By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,389 Members | 1,674 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,389 IT Pros & Developers. It's quick & easy.

Determine largest value in table column

P: 62
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
Sep 9 '07 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,669
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
Expand|Select|Wrap|Line Numbers
  1. Dim strNextDashNum As String, strNextEntry As String, strLastEntry As String
  2.  
  3. strLastEntry = DLast("[DashNum]", "tblTest")     'e.g. 417A7360-009B
  4. strNextDashNum = Format$(Val(Mid$(strLastEntry, 10, 3)) + 1, "000")     '010
  5. strNextEntry = Left$(strLastEntry, 9) & strNextDashNum & Right$(strLastEntry, 1)     '417A7360-010B
Last Value Entered
Expand|Select|Wrap|Line Numbers
  1. 417A7360-009B
Next Value Produced
Expand|Select|Wrap|Line Numbers
  1. 417A7360-010B
Sep 9 '07 #2

P: 62
Expand|Select|Wrap|Line Numbers
  1. Dim strNextDashNum As String, strNextEntry As String, strLastEntry As String
  2.  
  3. strLastEntry = DLast("[DashNum]", "tblTest")     'e.g. 417A7360-009B
  4. strNextDashNum = Format$(Val(Mid$(strLastEntry, 10, 3)) + 1, "000")     '010
  5. strNextEntry = Left$(strLastEntry, 9) & strNextDashNum & Right$(strLastEntry, 1)     '417A7360-010B
Last Value Entered
Expand|Select|Wrap|Line Numbers
  1. 417A7360-009B
Next Value Produced
Expand|Select|Wrap|Line Numbers
  1. 417A7360-010B

Works like a charm! Thanks!
Sep 11 '07 #3

ADezii
Expert 5K+
P: 8,669
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.
Sep 11 '07 #4

P: 62
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.
Sep 11 '07 #5

Post your reply

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