Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2011

    Unanswered: Sequential Number Based on Other Field in Table


    My table design is as follows:

    Product Type (3 digit numeric code i.e. 120)
    Sequence Number (4 or 5 digit numeric code, depending on the product)
    Description (text string)

    I want to have a form with Product Type, Description and Date. When you hit a button on the form, I want the next sequential number to be assigned to this specific product based on the product type that was chosen from a drop down. Then I would like to have the table where this information lies to be populated with the new information created. Is there a way to do this? I have looked through this forum, as well as other places in the internet but have not found an answer.

    I should probably also mention that I am using Access 97, which may be why a lot of what I'm finding on the internet is not working when I try to use the code.

    How can I do this? I'm a beginner at using VB so if someone could help me I would greatly appreciate it.


  2. #2
    Join Date
    Nov 2011
    I think I have it somewhat figured out, but I'm getting a ByRef Arg Type Mismatch error when I run it... my code is below

    Private Sub cmd_add_product_Click()

    On Error GoTo Err_Command39_Click

    Dim dbsImp As Database
    Dim rstProduct As Recordset

    'Variables for new sequence number creation
    Dim LMax As Long
    Dim LNew As Long

    'Variables for adding new record
    Dim intLoadCenter As Integer
    Dim strDate As String
    Dim intSequenceNmbr As Long
    Dim intProductNumber As Long
    Dim strDescription As String

    'Open recordset
    Set dbsImpellers = CurrentDb()
    Set rstProduct= dbsImp.OpenRecordset("ProductInfoTbl", dbOpenDynaset)

    'Set LMax = Highest number in SequenceNmbr field with criteria of Load Center = value chosen on form

    LMax = DMax("SequenceNmbr", "ProductInfoTbl", "LoadCenter=" & Me.cbo_LoadCenter & "")

    Debug.Print [LMax]

    LNew = LMax + 1
    Debug.Print [LNew]

    'Set variables to values in form text/combo boxes
    intLoadCenter = Me.cbo_LoadCenter
    strDate = Me.tbo_Date
    strDescription = Me.tbo_miscellaneous
    intSequenceNmbr = LNew
    intProductNumber = Me.cbo_LoadCenter & LNew
    Debug.Print [intProductNumber]

    'Call function Add Record
    AddRecord rstProduct, intLoadCenter, strDate, intSequenceNmbr, intProductNumber, strDescription


    Exit Sub

    MsgBox Err.Description
    Resume Exit_Command39_Click

    End Sub

    The function called is:

    Function AddRecord(rstTemp As Recordset, intLoadCenter As Integer,
    strDate As String, intSequenceNmbr As Long, _
    intProductNumber As Long, strDescription As String)

    With rstTemp
    !ProductNumber = intProductNumber
    !LoadCenter = intLoadCenter
    !SequenceNmbr = intSequenceNumber
    !Date = strDate
    !Description = strDescription
    End Function

    The error comes up and highlights the intSequenceNmbr that is used when calling the function AddRecords... any ideas why this is happening?
    Like I said before, SequenceNmbr is 4 or 5 digit number depending on the product. I declared it as a long though because this could eventually become 6 or more digits and I want it to work if this happens also.

    Can anyone see why I'm getting this error? My data types match, and I made sure to declare each variable on it's own line so I'm not really sure what else could be causing this... Thanks for the help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts