Generating sequential numbers

[carvind]

I have four different forms which are linked to four different tables. Each form has a field called RECORD_ID and I would like to increment the RECORD_ID sequentially based on the last number that was assigned for anyone of them. Is there a simple method to accomplish this task?

 

[Jack Cowley]

If Me.NewRecord Then
On Error Resume Next
Me.Record_ID = Nz(DMax("[Record_ID]", "TableName"), 0) + 1
End If

 

[carvind]

Hi Jack,

I need to increment the RECORD_ID by 1 based on the max RECORD_ID in the four tables.

Is there a way to use the DMAX function to find the max for multiple tables.

Thanks for your response.

 

[Jack Cowley]

You can only look up one table at a time with DMax(). Just use multiple instances of the code....

 

[carvind]

Could you please elaborate on that giving an example if possible
Thanks Jack.

 

[Jack Cowley]

Dim A,B,C,D As Integer

A = Nz(DMax("[Record_ID]", "Table1"), 0) + 1
B = Nz(DMax("[Record_ID]", "Table2"), 0) + 1
C = Nz(DMax("[Record_ID]", "Table3"), 0) + 1
D = Nz(DMax("[Record_ID]", "Table4"), 0) + 1

This will tell you what the NEXT ID will be...

[This message has been edited by Jack Cowley (edited 03-08-2002).]

 

[carvind]

Thanks Jack.