Need to update incremental field, NOT autonumber

[Cory McP]

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!!

[FishVal]

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.

[code=sql]
SELECT t1.*, AutoNum(t1.f1,1 ) AS AN
FROM t1 WHERE AutoNum(t1.f1,1 )<>0
UNION SELECT t1.*, AutoNum(t1.f1) AS AN
FROM t1;
[/code]


[code=vb]
Public Function AutoNum(varDumm y As Variant, Optional lngSeed As Variant) As Long

Static lngNum As Long
If Not IsMissing(lngSe ed) Then
lngNum = lngSeed
AutoNum = 0
Exit Function
End If
AutoNum = lngNum
lngNum = lngNum + 1

End Function
[/code]

[Cory McP]

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

[FishVal]

Hi, Cory.

To sort the query on AN simply add ORDER BY clause to the end of SQL expression.
[code=sql]
SELECT t1.*, AutoNum(t1.f1,1 ) AS AN
FROM t1 WHERE AutoNum(t1.f1,1 )<>0
UNION SELECT t1.*, AutoNum(t1.f1) AS AN
FROM t1 ORDER BY AN;
[/code]

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

[Cory McP]

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

[FishVal]

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.

Public Sub MakeTable()

    AutoNum Null, 1
    DoCmd.RunSQL "SELECT Autonum(t1.f1) AS AN, t1.f1, t1.f2 INTO tblNewTab" & _
                 " FROM t1;"
                 
End Sub

Optionally you can sort the records added to [tblNewTab] using ORDER BY clause. The following example will create table enumerated by [t1].[f1].

Public Sub MakeTable()

    AutoNum Null, 1
    DoCmd.RunSQL "SELECT Autonum(t1.f1) AS AN, t1.f1, t1.f2 INTO tblNewTab" & _
                 " FROM t1 ORDER BY t1.f1;"
                 
End Sub

[Cory McP]

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!!

[FishVal]

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.