Page 1 of 3 123 LastLast
Results 1 to 15 of 44
  1. #1
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282

    Autonumber not populating in Form

    I am attempting to create a form (shown below) which will enable you to enter details about a new assessment, however the (Autonumber) ID for this is not populating. I have seen in some threads it should do this when data is entered into other fields, however when I attempt this, nothing happens - except for a message in the bottom left-hand corner which says "Cannot Add record(s); join key of table 'Assessment Details is not in recordset' (Assessment Details is the table I am trying to input information into, an image of the error is shown below the form) this appears to be a paradox and I can't work out how to fix it, your help is much appreciated!

    Click image for larger version. 

Name:	Assessment Form.PNG 
Views:	72 
Size:	14.8 KB 
ID:	23209


    Click image for larger version. 

Name:	Assessment Form Error.PNG 
Views:	71 
Size:	2.1 KB 
ID:	23210

    Unfortunately I am unable to post the database on here due to the nature of the information held, Also, I would appreciate it if any advice you give described how to complete the task in Macro form since I do not currently understand either of the other options.

    Thanks in advance!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What is the record source of the form? It sounds like it's a query that is missing the field. Try with just the table name.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Yes, the source is a query, however the Assessment ID is part of the query (if that is what you meant)
    I am unsure what you mean about the table name?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I mean to take the query out of the record source and simply have

    Assessment Details

    as a test. By the way, spaces in your names will be more trouble than they're worth in the long run.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Excellent, thank you.

    Another thing, in the example above, I had to keep the previous form open to automatically populate Add Assessment Record Form's Person ID, Forename and Surname (As shown above) - am I missing something?

    Thank you for the tip but if I go back and change it now I'll probably miss something and then it'll all break...

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm not clear what you're asking. You could pass the values and then close the previous form.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Apologies, I asked without fully explaining, the page before the Add New Assessment Form (above) is a current assessment form which has been created (shown below)
    Click image for larger version. 

Name:	Assessment.PNG 
Views:	64 
Size:	14.5 KB 
ID:	23215
    This form has the Person ID, Forename and Surname already populated, however for some reason, when I add a new assessment (opening the first page) I cannot populate these cells unless I keep the previous form open and reference it (shown below)
    Click image for larger version. 

Name:	Name Error.PNG 
Views:	64 
Size:	6.3 KB 
ID:	23216
    - To be clear, this is a screenshot of the design view of the form shown in the first post.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That method would require the first form stay open. You could pass them then close the first form (the textboxes couldn't have that formula):

    DoCmd.OpenForm "SecondForm"
    Forms!SecondForm.PersonID = Me.PersonID
    Forms!SecondForm.Forename= Me.Forename
    Forms!SecondForm.Surname = Me.Surname
    DoCmd.Close...
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    How would I reference the forms and fields if they have spaces?
    Alternatively, is there a way to do this in Macro builder?

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    With inadvisable spaces the name has to be surrounded in square brackets...as you have them above.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Unfortunately I am unable to post the database on here due to the nature of the information held,
    You can still post the dB - delete the data, compact and repair, then zip. Say what the problem form name is.


    You are displaying the autonumber on the form. Not a good idea. See the following sites:

    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/index.php/Autonumbers

    Under the heading "What they are NOT", note #6.


    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I personally don't have a problem with an autonumber being exposed to the user, though i know some do. As long as you're aware of the quirks like potential gaps and they don't affect you, who cares? I use one for a limousine reservation number. Nobody cares if there's a missing number or whatever, they just want a number they can give the customer as a reference. It is certainly not appropriate in many if not most cases, but I disagree with a blanket "never".
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Just one of the conventions I have adopted. The only thing I use an autonumber field for is for table relationships. I also don't use two or more fields for a PK.
    I don't display an autonumber field, just as I don't use spaces, punctuation or special characters (except underscore) in object names.

    What happens if the autonumber is negative? Certainly nothing catastrophic. Can you live with a negative limousine reservation number? Sure... but it would bother me that the number was negative.

    If I needed a reservation number or an invoice number, I would create a "custom autonumber" using a technique like the one from, say.... BaldyWeb.com!



    I bring it up so at least they have some knowledge about autonumbers and can decide if it is acceptable for them.

  14. #14
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Thank you guys,
    The reason I need my Autonumber ID's visible is they need to be searchable - in the image below for example:
    Click image for larger version. 

Name:	Assessor Error.PNG 
Views:	53 
Size:	19.7 KB 
ID:	23260
    This is getting complicated now...
    I need to have the first fields (above the first line) populated from the previous page - done
    The second lot of fields (between the two lines) are populated depending on the Assessor ID inputted in the search box - my problem
    The final set of fields will begin with no data, but will be populated to add a record.

    The Assessor ID is the only field from the second group which will be used, however we need to check the assessor is correct.

    If you need to know more information about my database feel free to ask.

    I know I've deviated from the original question quite a lot now, your help is much appreciated!

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    One option would be changing the textbox to a combo and filling the filling the fields like this:

    BaldyWeb - Autofill

    Another would be opening a recordset on the input value and getting the related values from that.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Autonumber field not populating
    By Pete-RM in forum Forms
    Replies: 5
    Last Post: 09-24-2015, 08:05 AM
  2. Replies: 5
    Last Post: 10-20-2014, 10:32 AM
  3. Replies: 4
    Last Post: 11-21-2013, 05:06 PM
  4. Autonumber in multitable form
    By Fairlane in forum Forms
    Replies: 5
    Last Post: 08-29-2013, 06:15 PM
  5. Autonumber through a form, not a table
    By Kat-ness in forum Forms
    Replies: 2
    Last Post: 05-02-2013, 04:18 PM

Tags for this Thread

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