Hey all,
First time posting here, so please forgive me.
Working for our local government, I'm tasked with building a database to keep track of all of our deer hunting permits and members. Easy enough, right?
So I've got the database complete, with about 40 records in my main table (name, address, license #, etc etc). I incorporated a search form where the user can search for a name and it returns their the results (since name only has one field in the table, the user can search for the first or last name). Next to the name I have a button for edit record, which opens up the form of the hunter, showing all of the same information from the table. In that form, there is a button to print the record, which simply opens a nicely designed report with the generated members name.
My problem is as follows:
EACH member in the table has a permit number which is printed on the report to have hung on the car. Being that permits are re-issued with a new number each year, we need a way to have a field generate an auto-number for "Permit Number". So when you would click on "Add Hunter" from our search form, the "Hunter Form" will open with the only information already entered being the auto-generated Permit Number. I've experienced a couple issues with this:
1.) I cannot have more than one autonumber field in a table (ID is our first)
2.) I cannot have an autonumber field with data already in a table (we have 40 members and all of their data accounted for within this Hunter table)
3.) I cannot have two tables in one form (my idea with this was to have that one field in my "Hunter Form" reference a separate table called "Permit Number", but realize that no way would this keep record for each respective hunter).
I do not want to have to re-work the entire database. Is there a way. whether it be VBA code or whatnot, to have an autonumber field start whenever I click on "Add" or "Edit Hunter", which would pull that hunters record onto the form and display an auto-generated number for the ONE field. (granted, there is name, address, vehicle description, license number, etc etc, with only the ONE field of Permit Number that I need to have auto generated?
Sorry if this did not make sense, I've been scratching my head for quite some time now.
Thanks and sorry for the confusion!
Zix