General question about form usage

[Tommygun3]

Hello,



It has been a long time since I've used Access so i apologize if the answer is obvious...

Is it possible to use a form to generate a table's records?

What I have in mind is to have a form generate a primary key from concatenating a autonumber and a foreign key from a list box, the other fields would be direct data records into the table. Also, would the autonumber allow me to keep in sequence new records which are linked to the foreign key in this manner; FK + 001 = FK001, FL +001 = FL001, etc. I'm uncertain if the autonumber field can be used as I have in mind, resetting according to a new foreign key.

Feel free to provide any input.

Thanks

[pbaldy]

Typically done with a DMax() like the one near the bottom here:

http://www.baldyweb.com/CustomAutonumber.htm

[Tommygun3]

Hello Paul,

Thanks for your posting, it is very useful. I have a question on its contents;

"The proper way, then, is to create your own function that creates the number sequence you need.
Have the last¹ number stored in a table. It would probably be a good idea to make this a one record table, with this value the only value in the record. A one-value table."

Therefore to maintain proper sequence control for my situation, I would need to create separate tables for each of my foreign keys? The reason I bring this up is that I'd like to be able to automatically generate a unique sequential primary key that is the foreign key (a customer id to be specific) with a number that is unique and stays in sequence with the next pairing that is specific for that particular foreign key. Example: ForeignKey1 + 001, ForeignKey1 + 002, ForeignKey2 + 001, ForeignKey2 + 002, etc. Or is the DMax function if properly coded able to accomplish what I need with just a single table?

[pbaldy]

I was suggesting the DMax() method at the bottom (I just implemented the same type of thing for a client). With your text key like "FL" it would look like:

Nz(DMax("NumberField", "TableName", "KeyField = '" & Me.KeyField & "'"), 0) + 1

Where you stored the key value of "FL" in a field named KeyField and the "001" part in a field named NumberField (obviously you'd use your field names). I would store the number field as a number, and get the "001" with the Format function or property. In other words, you can store FL and 1 but display "FL001" to the user.