Results 1 to 7 of 7
  1. #1
    Zixxer is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2016
    Posts
    1

    First Time Poster - Need Help with Database and AutoNumbering for One Field

    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
    Attached Thumbnails Attached Thumbnails Search Form.png   Hunter Form.png   Print.png  

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,615
    First, Autonumbers are not to be used in this manner - not even made visible to the user. Suggest you read this for more guidance on why and how they're used.
    http://www.utteraccess.com/wiki/index.php/Autonumbers
    Yes, you can have more than one table involved in a form - simplest involves a single query that brings info together from two or more tables. Second method involves a form/sub form design. This can involve 2 or more tables with or without queries involved.

    I think what you need is a method of generating a unique license number, based on parameters that you need. That could involve the date, a telephone number, even a randomly generated number (which would not be sequential if that's what you're after) and/or any alphanumeric pattern you can come up with. A temp table might be advisable for this, so that concurrent users don't try to create the same system generated number at the same time. However, I profess that the unique identifier should be created at the last possible moment, when the record is finalized and committed.

    Before going further, I'm wondering if you need any reading material on other matters, such as a naming convention for Access objects, reserved words, and things to avoid which the MS people in their questionable wisdom, have provided - such as multi value fields. If I understand, you have only one field for names (you have strung together first and last) and you simply use ID as a field name. Neither of these would be what many would consider good design. I don't mean to pick apart your db; just that I detect things that will probably cause you grief later on.

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,368
    It looks like the Permit number is the year, followed by a sequential number.
    So, when adding a permit (disregarding the year for a moment) , the code needs to know the highest permit number so far, add one to it and there you have it.
    Keep the year and the sequential number in a separate fields in the table. You can append them for any display needed.
    You can find the highest sequential number in the table with a Dmax function.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,615
    That would be the logical approach for sure. However, I could be wrong, but I think Zixxer needs some background help (my apologies if not).
    If the permit will only be issued in the year it was created, no need to store the year. If not, and the user can edit the permit field, still no need to store the year. What we should emphasize in such solutions, is to determine that number at the last possible moment lest concurrent users be working on the same permit number at the same time. Bad things can happen in that case. If it cannot be this way, something has to be done to prevent concurrent use of the last (Max) number.

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,368
    As far as storing the year, if historical records are needed (keeping track of previous year permits), a second table with the hunter_FK, PermitYear and PermitNO would be a fine addition as a subform.

    Addendum: Thinking about this, if a tblPermits is added, determining the highest permit number would now require a Dmax search on the tblPermits table to find the next sequential number.
    If starting the sequential number at 1 for each year, the Dmax would have to take that into consideration in the search criteria.
    Last edited by davegri; 11-15-2016 at 05:00 PM. Reason: Added thoughts

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    One thing you might do is enter 5 to 10 SAMPLE records of made up data (ex Mickey Mouse, Daffy Duck, Donald Duck) and post your dB so we can see your table structure and overall dB design.

    Do a "Compact & Repair", then Zip it before posting it.


    Is this a multi user dB?

  7. #7
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,615
    Historical records can come from whatever provides a unique hunter identifier and permit number(s) for any year as long as they're not over-written from year to year. To overwrite them wouldn't make sense to me. The ID field would be of no help there if, as it appears, it is just an autonumber field.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. SOS.... AutoNumbering Issue
    By Tammy in forum Access
    Replies: 6
    Last Post: 03-04-2015, 01:13 PM
  2. Custom Autonumbering
    By mguardab in forum Access
    Replies: 1
    Last Post: 05-22-2014, 12:02 PM
  3. Replies: 2
    Last Post: 06-28-2013, 01:27 PM
  4. autonumbering.
    By puzzlednewbie in forum Access
    Replies: 3
    Last Post: 09-24-2012, 11:33 AM
  5. Autonumbering when a checkbox is clicked?
    By kutehart in forum Forms
    Replies: 1
    Last Post: 08-02-2010, 09:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums