Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2008

    Unanswered: Add number of rows/records

    This is first time posting for me. I want to automate serial number generation with something like an autoid number. I want to create a work order number and specify a quantity of individual devices on that work order. Based on the device quantity specifed, I need to generate that many serial numbers in a different table. The quantity of devices can be different from one work order to another. Each serial number should have the same product code and description as the work order. Any help on this will be greatly appreciated.

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    I would have the autonumber field in the table and leave that alone (no primary key). I would add a "SerialNumber" field and make this a primary key (No Duplicates - unless you want to allow duplicates then set the value to Yes - Duplicates OK.)
    To populate the SerialNumber field, you probably want to make a function (in a module) that returns the value you need. The function might be something like this....
    Function retSerialNumber() as integer
    (Put your logic here to compile the serial number...)
    Dim ListCForForm1 as integer
    Dim ListCForForm2 as integer
    Dim X as integer
    ListCForForm1 = If(Forms!MyForm1!ListboxA.Listcount - 1 = -1,-1,0,Forms!MyForm1!ListboxA.Listcount - 1)
    ListCForForm2 = IF(Forms!MyForm2!ListboxA.Listcount - 1 = -1,0,Forms!MyForm2!ListboxA.Listcount - 1)
    X = Forms!MyForms1!SomeValue (or SomeProductCode)
    retSerialNumber = 23 * date()-23 + 33 + ListCForForm1 - ListCForForm2 * X
    ..more code to manipulate retSerialNumber
    end function

    Then the function is called like this.....

    me!SomeFieldOntheForm = retSerialNumber()

    or in a query, or as the default value, or anywhere you need to use this function.

    Once you get the serialNumber populated in the first form, you can then populate that same serialNumber in the other forms.....

    Forms!SomeFormB!SerialNumber = Forms!MyMainForm!SerialNumber
    Last edited by pkstormy; 09-22-08 at 22:17.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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