473,327 Members | 2,069 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Need to update incremental field, NOT autonumber

Hi there,

I'm creating an employee table that tracks employees, and also is used in automerge to print off bi-monthly timesheets for those employees.

As employees come and go, the field ID is set as the primary key, autonumber. A year from now, employee 3 may leave, and I don't want #3 assigned again so autonumber works great.

My problem arises in trying to get an incremented field (called PageCount) numbered 1 up to the max number of records. This number is printed off on each employees timesheet. Their ID is printed on the form as well to identify them and tie them to their employee record, but the PageCount value is used as an index exactly like sports cards or comic books on the paper timesheets themselves. The shift supervisor collects the paper timesheets at the end of the pay period and counts the PageCount from ie. 1-45. This way they know they have all the timesheets in, with no missing index numbers in between. Employee ID #3 has left, and shouldn't have a timesheet printed or leave holes in the Pagecount procedure. Still, ID 4 must now have a PageCount of 3.

After the employees have been sorted alphabetically, how can I update the PageCount starting at 1 in row 1 up to the # of records in the final record? This will be done repeatedly as employees are added and removed, and cannot affect their autonumber primary key ID.

THANKS!!
Sep 19 '07 #1
7 7608
FishVal
2,653 Expert 2GB
Hi, Cory.

I have somewhat wet solution.
Counting number is being generated within a query using VBA function which holds Static variable. A tricky point here that Access refresh query multiple times, e.g. when query view needs to be repaint etc. So a somewhat ugly solution here is to make union query which resets counting number each time Access refresh the query.

Table t1 has two fields f1 and f2.

Expand|Select|Wrap|Line Numbers
  1. SELECT t1.*, AutoNum(t1.f1,1) AS AN
  2. FROM t1 WHERE AutoNum(t1.f1,1)<>0
  3. UNION SELECT t1.*, AutoNum(t1.f1) AS AN
  4. FROM t1;
  5.  

Expand|Select|Wrap|Line Numbers
  1. Public Function AutoNum(varDummy As Variant, Optional lngSeed As Variant) As Long
  2.  
  3.     Static lngNum As Long
  4.     If Not IsMissing(lngSeed) Then
  5.         lngNum = lngSeed
  6.         AutoNum = 0
  7.         Exit Function
  8.     End If
  9.     AutoNum = lngNum
  10.     lngNum = lngNum + 1
  11.  
  12. End Function
  13.  
Sep 19 '07 #2
Thanks FishVal!

I got it working - almost!

Your query worked - it numbered the people appropriately. There are two issues with this solution however. Any further tips would be great!!

1. The solution is in a union query. I won't have the query open when printing, and need to have automerge link to a table. Is there a way to change the union query you've created (which has the info the way I want) to a make-table query? My SQL, and especially VB are extremely limited.

2. After numbering them, it reorders the query by the primary key ID. The people are numbered appropriately in the AN field, but the order is askew again. Once I have the above union query saved as a table somehow, I suppose I could simply do another make-table query sorting by AN (or L_name, F_name again). This hinges on sorting out a solution to #1. Is there a way to have it sort by AN instead of the primary key in the first query? As we convert it to make-table?

Thanks again FishVal, and anyone else that can help!
Cory
Sep 20 '07 #3
FishVal
2,653 Expert 2GB
Hi, Cory.

To sort the query on AN simply add ORDER BY clause to the end of SQL expression.
Expand|Select|Wrap|Line Numbers
  1. SELECT t1.*, AutoNum(t1.f1,1) AS AN
  2. FROM t1 WHERE AutoNum(t1.f1,1)<>0
  3. UNION SELECT t1.*, AutoNum(t1.f1) AS AN
  4. FROM t1 ORDER BY AN;
  5.  
Didn't understand why do you need to make a table from it.
Union query is not updatable, but table made will not support numbering mentioned.
You may use the query as recordsource for reports, read-only forms etc, or rowsource for comboboxes, listboxes etc.

BTW, if you need this feature for report only, then take a look at this thread
Dynamic autonumber on an MS Access Report
Sep 20 '07 #4
Thanks for the Order command.

The issue is that I am using a different program, Teleform Automerge. Teleform allows digital scanning of paper forms for data retrieval. I'm trying to combine employee info from the closed Access table (hopefully our query result) to the teleformed timesheet.

In teleform, it allows you to select a table from Access to merge with a teleform, but not a query.(I'll check again though)

It's frustrating because your query presented the info exactly as I wanted it.
If I'm not able to update that info from your query into a table, the print won't reflect our AN. This whole step is to avoid someone having to manually update that integer column every two weeks each time we print up our timesheets.

I'll keep plugging away. So close!

Your comments have been extremely helpful! Thanks again FishVal!
Cory
Sep 20 '07 #5
FishVal
2,653 Expert 2GB
Thanks for the Order command.

The issue is that I am using a different program, Teleform Automerge. Teleform allows digital scanning of paper forms for data retrieval. I'm trying to combine employee info from the closed Access table (hopefully our query result) to the teleformed timesheet.

In teleform, it allows you to select a table from Access to merge with a teleform, but not a query.(I'll check again though)

It's frustrating because your query presented the info exactly as I wanted it.
If I'm not able to update that info from your query into a table, the print won't reflect our AN. This whole step is to avoid someone having to manually update that integer column every two weeks each time we print up our timesheets.

I'll keep plugging away. So close!

Your comments have been extremely helpful! Thanks again FishVal!
Cory
Well.

Actually this makes things even easier.

The following procedure will do the trick.

Expand|Select|Wrap|Line Numbers
  1. Public Sub MakeTable()
  2.  
  3.     AutoNum Null, 1
  4.     DoCmd.RunSQL "SELECT Autonum(t1.f1) AS AN, t1.f1, t1.f2 INTO tblNewTab" & _
  5.                  " FROM t1;"
  6.  
  7. End Sub
  8.  
Optionally you can sort the records added to [tblNewTab] using ORDER BY clause. The following example will create table enumerated by [t1].[f1].

Expand|Select|Wrap|Line Numbers
  1. Public Sub MakeTable()
  2.  
  3.     AutoNum Null, 1
  4.     DoCmd.RunSQL "SELECT Autonum(t1.f1) AS AN, t1.f1, t1.f2 INTO tblNewTab" & _
  5.                  " FROM t1 ORDER BY t1.f1;"
  6.  
  7. End Sub
  8.  
Sep 20 '07 #6
Thanks so much! That solution worked perfectly. I really appreciate all the thought you put into my problem!

The final thing I needed to do is to run your Module in a Macro after a few queries. I realized that by replacing "sub" with "function" in your syntax, I could call it with RunCode MakeTable().

The front end of my project is complete - weekend!!
Sep 21 '07 #7
FishVal
2,653 Expert 2GB
Thanks so much! That solution worked perfectly. I really appreciate all the thought you put into my problem!
Glad you've got it work.

The final thing I needed to do is to run your Module in a Macro after a few queries. I realized that by replacing "sub" with "function" in your syntax, I could call it with RunCode MakeTable().

The front end of my project is complete - weekend!!
Sorry, I never dealt with macros and have no ideas of how the code may be triggered from macro script. This stuff is all upon you.

Good luck.
Sep 21 '07 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Dejan Pujic | last post by:
Hello, I'm having difficulty figuring out how to update values in a certain column, where the new information depends on the previous. Here's a further description: The whole database is for...
4
by: ormor | last post by:
Hi friends, I am new to MS Access. I have desiged a Form wherein I would like to update the field based on some calculation derived from the previous fields. How this can be done. I want...
7
by: Colleen | last post by:
Hello All, I have a table for my employee data. I have a field that generates date before update of latest changes made. I also have two fields within called CreatedBy and UpdatedBy. I would...
1
by: Michael McGrew | last post by:
I have a asp form that has a dynamic drop-down box that a user selects a value from. Once the user makes a selection I use the onChange event to capture the selection and pass this value to a...
0
kaptaineaux
by: kaptaineaux | last post by:
Hi, What I am trying to do is update a field in list programmatically using a webpart. What I do is I get the context of the site, then all lists of type Events. For each End field that is...
1
by: Proaccesspro | last post by:
Trying to update a field in a table named CASE. How do I reference the particular field name?? The code is not working!! Dim SSNreplace As String SSNreplace = InputBox("Please enter the...
6
by: wellsfargouser | last post by:
Hey everyone, this is driving me crazy. I have a table which lists all the files users have on the server. This table is called risklist. It lists the pathname and size. The goal is to make a form...
1
by: Splattman | last post by:
How do I use VBA Code to update a field in a table in Access? I am trying select case queries and do while loop queries. I don't know how to open the source and then update the field I need to.
1
by: saagardn | last post by:
I am using Access 2007 on an XP machine. Is it possible to update one field with multiple values using a single SQL statement? I have tired to do this with the query builder, but keep getting a...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.