Results 1 to 8 of 8
  1. #1
    OsborneBCKK is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    4

    New unwanted entries on lookup tables

    thank you for a quick explanation on my last question.
    This one may be even more simple, but again I can't find an answer...
    I have a form used to enter donations for a charity. Everything works to populate the table Donations, but it is creating a new record on each lookup table rather than looking for value in the field. It is using the value to populate the combobox, but once selected, it creates a new value in the lookup table.
    Click image for larger version. 

Name:	FormIssue.PNG 
Views:	20 
Size:	49.4 KB 
ID:	25260
    please help.

    update: I found this Field list. This may be what is causing my issue, but I'm not sure how to fix it. The fields under 'Fields available for this view:' duplicate entries on the lookup tables. The fields under 'Fields available in related tables:' does not duplicate. So how do I get these other lookup tables to appear in the 2nd list?


    Click image for larger version. 

Name:	Issue.PNG 
Views:	20 
Size:	51.2 KB 
ID:	25261

    thanks,
    Brian

  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
    The donors table/field should not be part of the form's record source. That combo should be bound to the donor ID field in the donations table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    OsborneBCKK is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    4
    ok. I've deleted all my forms, all my queries, and all my reports. Nothing was working the way it was supposed to...
    I'm back to just my tables and the relationships as below.
    I need some help creating a form that allows dropdown selection of all the foreign keys in the Donations table. But I don't want the IDs for each, I'd like to see the Name associated with them in the dropdown to populate the ID on the Donations table.
    Someone on here patient enough to do a step by step or point me to a directional video that does this?
    Click image for larger version. 

Name:	relationship.PNG 
Views:	16 
Size:	18.5 KB 
ID:	25265

  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
    The would be based only on the donations table. For the foreign key fields, try the combo box wizard. It will create a combo that gets its selections from the lookup table, saves the ID field to the donations table but display the name.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    OsborneBCKK is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    4
    I enabled the combo box wizard and placed a combo box on the form. When I get to the window where I select from the list of available fields, the only ones that show up are ones that are of a type number. But I want the name, not the ID.
    Click image for larger version. 

Name:	ComboBox Wizard.PNG 
Views:	17 
Size:	24.5 KB 
ID:	25266

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    First, do yourself a favor and don't use spaces in object names.

    I need some help creating a form that allows dropdown selection of all the foreign keys in the Donations table. But I don't want the IDs for each, I'd like to see the Name associated with them in the dropdown to populate the ID on the Donations table.
    Let's start with the "Donors" table. You want to store the donor name but the FK field in table "Donations" appears to be a Long Integer.
    You will have to change the field type to a text type.
    BTW, "Name" and "Description" are reserved words in Access and shouldn't be used for object names".

    So, you will have to change the other "linked" field types also if you want to save text instead of the ID numbers.

  7. #7
    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 don't think I've seen the wizard not list all fields. What did you select previously? Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    It happens to me, one of my SQL tables for Employee Names they made the datatype (nvarchar(MAX)) which in access makes it a memo field. I guess due to datatype or length, it will not show it as fields to select in the wizard, but when I go into the combo box query view the data, I can then see the field in the box and add it to the query. I then have to adjust the Column Count, Column Width and such manually.

    Basically to do what you want, in the combo box you will want DonorID and DonorName(change it from just Name as it is reserved as others said). Use the wizard and it should hide the ID and show the Name in the box when you open the form but save the DonorID in that field.

    Main field property values to consider are Bound Column from Data tab which should be "1" and from Format tab, Column Count which should be "2" and Column Widths which should be something like be 0";2" (the 0 width will hide the DonorID from the list)

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

Similar Threads

  1. Lookup returns unwanted HTML tags
    By George in forum Access
    Replies: 4
    Last Post: 07-22-2015, 07:17 PM
  2. Replies: 11
    Last Post: 03-13-2014, 09:54 AM
  3. Replies: 3
    Last Post: 09-27-2013, 11:50 AM
  4. Sum of entries from seperate tables/queries
    By krutoigoga in forum Queries
    Replies: 4
    Last Post: 07-07-2011, 11:54 AM
  5. Subform creating new unwanted entries
    By Rpatrick in forum Access
    Replies: 1
    Last Post: 08-14-2009, 03:08 PM

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