Determine largest value in table column

[nickvans]

Hello. I'm using Access 2003 on an XP machine. I have a table in which the primary key column is called "DashNum" which represents a portion of a longer number called "ModNum." A typical example is "417A7360-###B" where the "417A7360" is the first part of the module number (which _almost_ always remains the same), and the "###" is the Dash number. I have a form setup to guide users through the creation of additional records for multiple tables, which includes among other things a text box for inputing the module number.

For ease of data entry, I would like the default value of the text box to put in "417A7360-" & (1+the largest number in the DashNum table).

I'm pretty new to access, and have pretty limited VBA knowledge. Any help would be great!

Thanks!
nickvans

[ADezii]

Hello. I'm using Access 2003 on an XP machine. I have a table in which the primary key column is called "DashNum" which represents a portion of a longer number called "ModNum." A typical example is "417A7360-###B" where the "417A7360" is the first part of the module number (which _almost_ always remains the same), and the "###" is the Dash number. I have a form setup to guide users through the creation of additional records for multiple tables, which includes among other things a text box for inputing the module number.

For ease of data entry, I would like the default value of the text box to put in "417A7360-" & (1+the largest number in the DashNum table).

I'm pretty new to access, and have pretty limited VBA knowledge. Any help would be great!

Thanks!
nickvans

[CODE=vb]Dim strNextDashNum As String, strNextEntry As String, strLastEntry As String

strLastEntry = DLast("[DashNum]", "tblTest") 'e.g. 417A7360-009B
strNextDashNum = Format$(Val(Mid $(strLastEntry, 10, 3)) + 1, "000") '010
strNextEntry = Left$(strLastEn try, 9) & strNextDashNum & Right$(strLastE ntry, 1) '417A7360-010B[/CODE]
Last Value Entered
[CODE=text]417A7360-009B[/CODE]
Next Value Produced
[CODE=text]417A7360-010B[/CODE]

[nickvans]

[CODE=vb]Dim strNextDashNum As String, strNextEntry As String, strLastEntry As String

strLastEntry = DLast("[DashNum]", "tblTest") 'e.g. 417A7360-009B
strNextDashNum = Format$(Val(Mid $(strLastEntry, 10, 3)) + 1, "000") '010
strNextEntry = Left$(strLastEn try, 9) & strNextDashNum & Right$(strLastE ntry, 1) '417A7360-010B[/CODE]
Last Value Entered
[CODE=text]417A7360-009B[/CODE]
Next Value Produced
[CODE=text]417A7360-010B[/CODE]

Works like a charm! Thanks!

[ADezii]

Works like a charm! Thanks!

You are quite welcome. Be advised that the code will function normally as long as:

1. You stick with the exact 13-Character Format for the String containing the Dash Number.
2. The code will accommodate only Dash Numbers from 001 to 999. If you ever have Dash Numbers > 999, the code will have to be adjusted.

[nickvans]

You are quite welcome. Be advised that the code will function normally as long as:

1. You stick with the exact 13-Character Format for the String containing the Dash Number.
2. The code will accommodate only Dash Numbers from 001 to 999. If you ever have Dash Numbers > 999, the code will have to be adjusted.

Yeah I noticed that. Over the last 20 years or so we have yet to break 300, so I'm not worried about exceeding 999. Thanks again.