Windows 7 32bit 
Access 2010 32bit
I am in the process of creating a database that assigns an indexed equipment identification numbers, within a specified number range, based on type of equipment; Major (1000 - 1999), Electrical (2000 - 2999), Instrumentation (3000 - 3999), and Valves (4000 - 4999). I have a form which allows the user to select equipment type and then via a button opens the associated equipment type subform. The subform allows the user additional options and then via a button assigns a equipment id number based on the main form AutoNumber and the Format Fuction as shown below.
If [Forms]![TREATEquipmentID]![Major] = True Then
Me!ValveNo = (Format(Forms!TREATEquipmentID!AutoNumber, "1000"))
End If
If [Forms]![TREATEquipmentID]![Valves] = True Then
Me!ValveNo = (Format(Forms!TREATEquipmentID!AutoNumber, "2000"))
End If
In an attempt to make the database work, I created specific tables, which include AutoNumbers, for each specific equipment type. This was done since Access will only allow one AutoNumber per table, which I thought would allow me to assign an equipment id based on the specified range. My issue is, I do not know how to get the subform field to get the AutoNumber from the specified table. As long as I use the AutoNumber on the main form everything works perfect, but when I try adding additional AutoNumbers, by whatever means, to get the multiple equipent id ranges required, I fail.
Also, this method assigns the next sequenchial AutoNumber so you may have 3122 and then 4123, instead of keeping each range separate.
Any assistance in this matter would be greatly appreciated. As a Access amatuer, there probably is a way to populate a table with the required numbers and then have the database assign the next sequential number.
Thanks
Reply With Quote
