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
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)