Programmatic sequential incrementation and data storage - access 2003

**youmike** · Oct 19 '08, 06:19 AM

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.

**Axxe** · Oct 19 '08, 02:49 PM

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?

Code:

Function NewItemCode(pComID) As String

    Dim db As Database
    Dim LSQL As String
    Dim LUpdate As String
    Dim LInsert As String
    Dim Lrs As DAO.Recordset
    Dim LNewItemCode As String

    On Error GoTo Err_Execute

    Set db = CurrentDb()

    'Retrieve last number assigned for Commodity Type
    LSQL = "Select Last_Nbr_Assigned from Codes"
    LSQL = LSQL & " where Code_Desc = '" & pComID & "'"

    Set Lrs = db.OpenRecordset(LSQL)

    'If no records were found, create a new Commodity Type in the Codes table
    'and set initial value to 1
    If Lrs.EOF = True Then

        LInsert = "Insert into Codes (Code_Desc, Last_Nbr_Assigned)"
        LInsert = LInsert & " values "
        LInsert = LInsert & "('" & pComID & "', 1)"

        db.Execute LInsert, dbFailOnError

        'New Item Code is formatted as "AGR-0001", for example
        LNewItemCode = pComID & "-" & Format(1, "0000")

    Else
        'Determine new ItemCode
        'New ItemCode is formatted as "AGR-0001", for example
        LNewItemCode = pComID & "-" & Format(Lrs("Last_Nbr_Assigned") + 1, "0000")

        'Increment counter in Codes table by 1
        LUpdate = "Update Codes"
        LUpdate = LUpdate & " set Last_Nbr_Assigned = " & Lrs("Last_Nbr_Assigned") + 1
        LUpdate = LUpdate & " where Code_Desc = '" & pComID & "'"

        db.Execute LUpdate, dbFailOnError

    End If

    Lrs.Close
    Set Lrs = Nothing
    Set db = Nothing

    NewItemCode = LNewItemCode

    Exit Function

Err_Execute:
    'An error occurred, return blank string
    NewItemCode = ""
    MsgBox "An error occurred while trying to determine the next ItemCode to assign."

End Function

**ADezii** · Oct 20 '08, 12:07 AM

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:

Code:

Dim varLastRecNum As Variant
Dim strState As String
Dim lngStartRange As Long
Dim lngEndRange As Long
Dim strMsg As String

'Create tblStateRange and poppulate it as such
'[State]      [Start]       [Stop]        [ID] - Primary Key
' Texas         5400         5499
' Alabama        300          399
'...

'Oversimplified tblRecords ([RecNum] is Indexed/No Duplicates)
'[State]      [RecNum]       [ID] - Primary Key
'Alabama       08-0323
'Texas         08-5422
'Alabama       08-0324
'...

'cboState is a Combo Box on a Form in Add Mode with all States listed in ALPHA Sort
If IsNull(Me![cboState]) Then Exit Sub      'need a State, if not outta here!

strState = Me![cboState]        'Valid State selected from Combo

'Retrieve the Start & Stop Ranges for the State
lngStartRange = DLookup("[Start]", "tblStateRange", "[State] = '" & strState & "'")
lngEndRange = DLookup("[Stop]", "tblStateRange", "[State] = '" & strState & "'")

'Retrieve the Last Record Number for the State chosen
varLastRecNum = DLast("[RecNum]", "tblRecords", "[State] ='" & strState & "'")

If IsNull(varLastRecNum) Then       'No Record Number for the State, create one
  Me![txtRecNum] = "08-" & Format$(lngStartRange, "0000")
Else
  'The first think to check is to see if Incrementinig the Range Number will
  'go above the Upper Limit of the Range Values for the State
  If Val(Mid$(varLastRecNum, 4, 4)) + 1 > lngEndRange Then
    strMsg = "The Record Number for the State [" & strState & "] cannot be incremented " & _
          "since it will place you above the Upper Range for [" & strState & "]!"
            MsgBox strMsg, vbCritical, "Cannot Increment Record Number"
  Else      'We are OK, just increment the Record Number, will ya
    Me![txtRecNum] = "08-" & Format$(Val(Mid$(varLastRecNum, 4, 4)) + 1, "0000")
  End If
End If

**Axxe** · Oct 21 '08, 05:21 PM

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.

**ADezii** · Oct 21 '08, 06:22 PM

Originally posted by Axxe

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.

**Axxe** · Oct 22 '08, 01:42 AM

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.

**ADezii** · Oct 22 '08, 02:09 AM

Originally posted by Axxe

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:

The Name of the Table to write to.
Field Names and their Data Types whch exist in this Table,
If Fields other than [RecNum] are being writen to, where are they deriving their values from?
etc...

**Axxe** · Oct 25 '08, 04:42 PM

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.

**ADezii** · Oct 25 '08, 05:24 PM

Originally posted by Axxe

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.