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

Programmatic sequential incrementation and data storage - access 2003

P: 5
I have searched high and low for cogent, well-explained coding to complete a project on which I have spent six months of work.

I stumbled across something on this site that is close to what I seek, but, as is rather typical, I could understand neither the question nor the answer.

I am hopeful that someone in this forum can provide me with suitable guidance.

THE ISSUE
I am working in Access 2003. The database I am preparing collects and organizes information about records for different states of the United States.

Each state has been assigned an independent range of sequential numbers, in order to distinctly identify records entered for a particular state.

For example, the range of numeric values for Texas is 5400 through 5499. The range of numeric values for Alabama is 0300 through 0399.

Each numeric value must include a prefix of "08-" to identify the year of the record.

I want to automate the assignment of these numbers.

For example, if a user desires to create a new record for Texas, I desire a module or macro that (a) programmatically assigns the next number in the Texas sequence of assigned numbers; (b) records that number to appropriate tables; and (c) retrieves the number and increments it by one on the next occasion when a user desires to enter a further record for Texas.

I need this exact same programmatic format for each of the fifty states, so the program must be able to differentiate between the correct sequential record identifier for Texas versus Kentucky versus New York versus Alabama, etc.

WHAT I HAVE
I have a rudimentary understanding of Visual Basic, but I cannot find good, solid guidance or explanation concerning the properties I should use to cover this issue, or the appropriate programming logic.

Using If-Then statements, I developed a program that does ratchet a sequence upward (and do it correctly) by one number, but no more.

I believe I need to know how to code the output so it is written to a table, and how to code the program to go fetch the number from that table when the next record is being created. I do not believe DMax is suitable for my needs because there may be multiple simultaneous users of the database, and I understand that DMax would permit undesirable duplication in that context.

Apart from my simple "If-Then" coding, I have nothing more to place the specifics in context. I simply want to be pointed in a good direction to work out the rest myself.

Your kind input is welcome. Thanks.
Oct 18 '08 #1
Share this Question
Share on Google+
9 Replies


P: 69
A first reaction. By allocating blocks of numbers to different states, I'd say that you are plannning to attach significance to what should be a pprimary key. A better strategy is to add a field called StateID to the table. You could use the pretty standard two letter code for each state. To do this, I'd suggest a second table called tblState with two fields, StateID and StateName. If you want to allocate specific numbers, then add fields for MinNumber, MaxNumber and CurrentNumber and write a VBA module to retrieve current number and increment it each time it is retrieved. But I have to say that none of this seems a god plan

Similarly, the year should also be in a separate field. When you need to report, you should manipulate the year and concatenate it with the record number.

I say again that I think you need to examine your design strategy.
Oct 19 '08 #2

P: 5
What I described above is the data pattern that has been in use for a very long time. I did not create the pattern. I just have to "make it work."

In addition to the state-specific identifier that I described, every individual record has a separate, unique identifier that, this year, began at 08-1001 and now is at 08-1699. That would be my candidate for a primary key, if I were to use it for that purpose. I prefer not to use a data element for a primary key.

These records are sorted in a couple of different ways to a number of different reports. There is a master report of all records, and state-by-state reports according to the state-specific identifiers.

I found this web link -- http://www.techonthenet.com/access/m...ntial_nbr2.php
-- and downloaded the program at that website.

It is very close to what I need.

The only problem is this: With this counter, users appear to have "just one shot" to get it right. That won't work. I need a feature that will permit a user to check their work -- and to go back and "do it over" if they made a mistake -- before committing the new record to a table.

How would I do that?

Below is the coding for the sequential counter function. I understand this coding will be executed whenever a label called "ItemCode" loses focus. Perhaps I need to substitute a Yes Do It/No Please Go Back button in place of the lose focus event, a button command which also deletes the work that has been done if the user decides to modify the entry.

What do you think?

Expand|Select|Wrap|Line Numbers
  1. Function NewItemCode(pComID) As String
  2.  
  3.     Dim db As Database
  4.     Dim LSQL As String
  5.     Dim LUpdate As String
  6.     Dim LInsert As String
  7.     Dim Lrs As DAO.Recordset
  8.     Dim LNewItemCode As String
  9.  
  10.     On Error GoTo Err_Execute
  11.  
  12.     Set db = CurrentDb()
  13.  
  14.     'Retrieve last number assigned for Commodity Type
  15.     LSQL = "Select Last_Nbr_Assigned from Codes"
  16.     LSQL = LSQL & " where Code_Desc = '" & pComID & "'"
  17.  
  18.     Set Lrs = db.OpenRecordset(LSQL)
  19.  
  20.     'If no records were found, create a new Commodity Type in the Codes table
  21.     'and set initial value to 1
  22.     If Lrs.EOF = True Then
  23.  
  24.         LInsert = "Insert into Codes (Code_Desc, Last_Nbr_Assigned)"
  25.         LInsert = LInsert & " values "
  26.         LInsert = LInsert & "('" & pComID & "', 1)"
  27.  
  28.         db.Execute LInsert, dbFailOnError
  29.  
  30.         'New Item Code is formatted as "AGR-0001", for example
  31.         LNewItemCode = pComID & "-" & Format(1, "0000")
  32.  
  33.     Else
  34.         'Determine new ItemCode
  35.         'New ItemCode is formatted as "AGR-0001", for example
  36.         LNewItemCode = pComID & "-" & Format(Lrs("Last_Nbr_Assigned") + 1, "0000")
  37.  
  38.         'Increment counter in Codes table by 1
  39.         LUpdate = "Update Codes"
  40.         LUpdate = LUpdate & " set Last_Nbr_Assigned = " & Lrs("Last_Nbr_Assigned") + 1
  41.         LUpdate = LUpdate & " where Code_Desc = '" & pComID & "'"
  42.  
  43.         db.Execute LUpdate, dbFailOnError
  44.  
  45.     End If
  46.  
  47.     Lrs.Close
  48.     Set Lrs = Nothing
  49.     Set db = Nothing
  50.  
  51.     NewItemCode = LNewItemCode
  52.  
  53.     Exit Function
  54.  
  55. Err_Execute:
  56.     'An error occurred, return blank string
  57.     NewItemCode = ""
  58.     MsgBox "An error occurred while trying to determine the next ItemCode to assign."
  59.  
  60. End Function
Oct 19 '08 #3

ADezii
Expert 5K+
P: 8,608
Here is some logic that I developed that has been tested and works. The only problem is that I did it in a hurray, and do not have the time to explain it in detail, so I added additional Commenting. See if you like the concept, and if you do, look at it closely then let me know what you can, or cannot, decipher. Will check in when I can:
Expand|Select|Wrap|Line Numbers
  1. Dim varLastRecNum As Variant
  2. Dim strState As String
  3. Dim lngStartRange As Long
  4. Dim lngEndRange As Long
  5. Dim strMsg As String
  6.  
  7. 'Create tblStateRange and poppulate it as such
  8. '[State]      [Start]       [Stop]        [ID] - Primary Key
  9. ' Texas         5400         5499
  10. ' Alabama        300          399
  11. '...
  12.  
  13. 'Oversimplified tblRecords ([RecNum] is Indexed/No Duplicates)
  14. '[State]      [RecNum]       [ID] - Primary Key
  15. 'Alabama       08-0323
  16. 'Texas         08-5422
  17. 'Alabama       08-0324
  18. '...
  19.  
  20. 'cboState is a Combo Box on a Form in Add Mode with all States listed in ALPHA Sort
  21. If IsNull(Me![cboState]) Then Exit Sub      'need a State, if not outta here!
  22.  
  23. strState = Me![cboState]        'Valid State selected from Combo
  24.  
  25. 'Retrieve the Start & Stop Ranges for the State
  26. lngStartRange = DLookup("[Start]", "tblStateRange", "[State] = '" & strState & "'")
  27. lngEndRange = DLookup("[Stop]", "tblStateRange", "[State] = '" & strState & "'")
  28.  
  29. 'Retrieve the Last Record Number for the State chosen
  30. varLastRecNum = DLast("[RecNum]", "tblRecords", "[State] ='" & strState & "'")
  31.  
  32. If IsNull(varLastRecNum) Then       'No Record Number for the State, create one
  33.   Me![txtRecNum] = "08-" & Format$(lngStartRange, "0000")
  34. Else
  35.   'The first think to check is to see if Incrementinig the Range Number will
  36.   'go above the Upper Limit of the Range Values for the State
  37.   If Val(Mid$(varLastRecNum, 4, 4)) + 1 > lngEndRange Then
  38.     strMsg = "The Record Number for the State [" & strState & "] cannot be incremented " & _
  39.           "since it will place you above the Upper Range for [" & strState & "]!"
  40.             MsgBox strMsg, vbCritical, "Cannot Increment Record Number"
  41.   Else      'We are OK, just increment the Record Number, will ya
  42.     Me![txtRecNum] = "08-" & Format$(Val(Mid$(varLastRecNum, 4, 4)) + 1, "0000")
  43.   End If
  44. End If
Oct 20 '08 #4

P: 5
ADezii,

Thanks for helping me out. A quick review suggests to me that this code may be just what I need.

I'll study it closely and then see if I can make it work.

Thanks again.

Axxe.
Oct 21 '08 #5

ADezii
Expert 5K+
P: 8,608
ADezii,

Thanks for helping me out. A quick review suggests to me that this code may be just what I need.

I'll study it closely and then see if I can make it work.

Thanks again.

Axxe.
You are quite welcome. good luck.
Oct 21 '08 #6

P: 5
This code works perfectly, except I can only cause it to increment a state by one digit.

Correct me if I just missed something, but I think this is because the output is not overwriting to an appropriate table.

It seems to me that I need to write or overwrite numeric output to the "[Start]" field of tblStateRange.

The text string that results from the operation -- Me!["txtRecNum"] -- would also need to be written or saved to a field of a table, call it "tblMaster". The text string Me!["txtRecNum"] would not be incremented or overwritten; it merely identifies a specific document and it should be unique to that document, therefore, I would not want to overwrite it.

What is the proper way to write this output? In my past efforts to take a whack at this problem I've tried Put and Write and Print in endless combinations; I always come up with mismatch errors.

It seems to me that this, like the overall problem, should be a no-brainer. It's frustrating to find how very little (i.e., nothing) there is in VBA Access resources on simple things like how to code output so that it writes to a table. I guess it's something that writers assume (?) people already know . . . .

Thanks for your help. I confess that in my own blind groping I accomplished a one-digit "bump" by state (using endless coding).

Yours is magnificently elegant.
Oct 22 '08 #7

ADezii
Expert 5K+
P: 8,608
This code works perfectly, except I can only cause it to increment a state by one digit.

Correct me if I just missed something, but I think this is because the output is not overwriting to an appropriate table.

It seems to me that I need to write or overwrite numeric output to the "[Start]" field of tblStateRange.

The text string that results from the operation -- Me!["txtRecNum"] -- would also need to be written or saved to a field of a table, call it "tblMaster". The text string Me!["txtRecNum"] would not be incremented or overwritten; it merely identifies a specific document and it should be unique to that document, therefore, I would not want to overwrite it.

What is the proper way to write this output? In my past efforts to take a whack at this problem I've tried Put and Write and Print in endless combinations; I always come up with mismatch errors.

It seems to me that this, like the overall problem, should be a no-brainer. It's frustrating to find how very little (i.e., nothing) there is in VBA Access resources on simple things like how to code output so that it writes to a table. I guess it's something that writers assume (?) people already know . . . .

Thanks for your help. I confess that in my own blind groping I accomplished a one-digit "bump" by state (using endless coding).

Yours is magnificently elegant.
Correct me if I just missed something, but I think this is because the output is not overwriting to an appropriate table.
Correct, the only thing that the code is currently doing is writing the newly incremented RecNum to a Text Box on a Form, namely, Me![txtRecNum]. I was under the assumption that your Form consisted of other Fields and was Bound to a Main Table/Query. If you wish to directly write to a Table, I will need much more information such as:
  1. The Name of the Table to write to.
  2. Field Names and their Data Types whch exist in this Table,
  3. If Fields other than [RecNum] are being writen to, where are they deriving their values from?
  4. etc...
Oct 22 '08 #8

P: 5
In thinking I needed to write the result directly to a table, I'm sure I'm making this much harder than it needs to be.

I will work with my controls and tables to see if I can achieve the same result without taking any more of your time.

Your help has been awesome. Thanks.
Oct 25 '08 #9

ADezii
Expert 5K+
P: 8,608
In thinking I needed to write the result directly to a table, I'm sure I'm making this much harder than it needs to be.

I will work with my controls and tables to see if I can achieve the same result without taking any more of your time.

Your help has been awesome. Thanks.
If you cannot come up with the solution, I'll be happy to show youo how to write the results to a Table, as long as you are not in a rush. Let me know.
Oct 25 '08 #10

Post your reply

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