Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    LeonS is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2014
    Posts
    115

    Error Message from a Combo Box


    Hello! I have 3 Combo Boxes on one form. One does not work. The tables are linked, Master1 is linked to Industries by the IDIndustry. In both tables there is a Field called, Industry. I want the Combo Box to look into the Industries table to show all the available Industries. Then to save the selected Industry into the Industry field in Master1. I have copied other Combo Boxes and changed the data source etc etc. I have created new Combo Boxes. Neither system worked. Question 1. Is there a limit on the number of Combo Boxes you can have on 1 Form. Question 2. In the Combo Wizard the box starts as Unbound. At no time do I see it change to Bound (but I presume it is a Bound Combo Box??).

  2. #2
    LeonS is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2014
    Posts
    115
    Hello! It is Leon again! I forgot to include the Error Message - Error you cannot add or change a record because a related record is required in the table Industries.
    And the drop down shows an industry, which I select - so there IS a related record!

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The message means you are trying to create a child record when there is no parent for it and is likely a result of your relationships. Assuming the relationships are correct, then it's likely just a matter of procedure. In other words, don't try to create child records when there is no parent, or design so that it can't happen.

    A combo list does not mean there is a record with a value in a field that is bound to the control. Combos used for searches are typically left unbound because it's easy to alter values in the records that you've bound the control to. So the list values are one thing (they typically come from a table/query or value list) and the records where you're saving a selection is something else. The property for the former is known as the rowsource property; the latter is the controlsource property (field that the combo is bound to).
    HTH - if not, maybe post the db or refer to a prior post where it can be found.
    Last edited by Micron; 09-01-2021 at 10:14 AM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    LeonS is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2014
    Posts
    115
    Hello! Please find a copy of the relationship between the tables. Please tell me if this is correct, so I can achieve the ComboBox results that work on the other 2 ComboBoxes (namely Countries and Nationality). The ComboBox I am trying to create is Industries. Thank you.
    As I said earlier in my thread, I have copied the code from another, working, ComboBox. But I want to make sure that the tables are linked correctly. Thank you, Click image for larger version. 

Name:	ContactsScreen2.png 
Views:	20 
Size:	13.3 KB 
ID:	46127Leon.
    Click image for larger version. 

Name:	TableRelationships.jpg 
Views:	18 
Size:	104.8 KB 
ID:	46126

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I can only surmise that the form is bound to the Master1 table and you are trying to add an industry value there when it doesn't exist in the Industry table. By copying the combo, you might have brought over one or more properties that are not conducive to your form design. If those pics mean that you select "retail" in the combo then go to the next combo, the industry one might be bound and there is no "retail" value in the industry field, or it is a lookup field or who knows what. This might be easier/faster if you copied the db, removed what isn't necessary for this issue, compact/repair the copy then zip and post it here.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    LeonS is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2014
    Posts
    115
    Yes, the "Contacts" form is bound to the Master1 table. I am trying to designate the Industry (Law) to a record. As you can see from the table, Law exists. I have tried other industries - so it is not Law specifically. I will copy the db to a new blank db. I will add a form for this function. And try again. If that does not work, I will send you a zipped copy containing the 2 tables, the new form (which will include the Industry ComboBox and the County/Nationality ComboBox). I thank you for your patience and help. Leon

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Your combos here should be bound to the respective fields of Master 1.
    The sources should come from the respective tables?

    These are not cascading combos are they?

    With your experience, I would not copy combos etc? You might think you are saving time, but run into issues like this. It takes a few seconds to create a combo with the wizard and only a little more without, but you will learn more that way.?

    I hear it so many times 'I copied code that works, and his does not', only to find either the first code did not work for this situation, or an amendment broke that code.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    LeonS is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2014
    Posts
    115
    Hello! I want the Combo to get the data from the field "Industry" in the Table Industries and put it in the Industry field in the Master1 table.
    In a test db, I have created a new form. I have added a new Combo by using the Wizard. The Combo shows the the real Industry name, eg Media BUT saves the IDIndustry number to the Master1 table. This I do not want! I need the text in Industry to be added to the Master1 table. I show the table relationships.

    Relationships.pdfClick image for larger version. 

Name:	TableRelationships.jpg 
Views:	16 
Size:	104.8 KB 
ID:	46131

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    As you can see from the table, Law exists.
    Actually, no I can't. Something missing from your posts? Where can you see that?
    The Combo shows the the real Industry name, eg Media BUT saves the IDIndustry number to the Master1 table. This I do not want!
    Actually, yes you do - that is the way it is usually done. When the primary key value is an autonumber, you store that number everywhere, not the value associated with the number. In queries, you join those 2 fields and include the value field so that you see the value. That way, if you ever edit the value field (even just correct spelling) the new value propagates everywhere without you having to do anything else.

    You could do it this other way, but then you have to worry about relationships that have cascade updates set. Many, if not most, experienced developers don't.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    As mentioned, I would not do it that way, but if you want to shoot yourself in the foot, only bring in the Industry name into the combo.?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  11. #11
    LeonS is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2014
    Posts
    115
    Hello! What you haven't seen yet are the contents of the other tables - in this case Industries, Countries (which has Country and Nationality). I think I am now starting to see where this heading.
    I need the Country, Nationality and Industry to appear in the Master1 table - not their ID. The Country, Nationality and Industry are criteria in queries that form part of the backbone of the database. I now show the Countries and Industry tables. I hope this helps. Maybe I need to increase the number of relationships??? Leon


    Click image for larger version. 

Name:	IndustriesTable.jpg 
Views:	15 
Size:	117.5 KB 
ID:	46132Click image for larger version. 

Name:	IndustriesTable.jpg 
Views:	15 
Size:	117.5 KB 
ID:	46132

  12. #12
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Leon

    As everyone has said, when you use a Combobox to lookup a value from another table, the ID -Autonumber for the selected items is stored in the Master table.

    When you need to retrieve the actual Text Value using a Query or Report, then you would add the lookup table to the query and then add the Text value to the query grid.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  13. #13
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    No!, you need the ID. From that you can get the descriptive name of that ID, plus anything else you need.?
    However if you want to shoot yourself in the head, I have already told you what you need to do.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I need the Country, Nationality and Industry to appear in the Master1 table - not their ID
    Then I guess you bind that combo column to the field instead of the ID. Might as well remove all your autonumber id fields as you seem to be saying you're not going to use them anyway in spite of advice given.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    LeonS is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2014
    Posts
    115
    Hello! Perhaps I should explain a little about the db, so you can understand why I want the Industry value/ name in the box rather then the ID number. The user will be a secretary or assistant. They will remember the Industry name - not the ID number. There is a button/ query on the form which produces all the email addresses for a specific Industry as requested by the user. So, if the task is to send an email to everyone in the Construction industry, the user presses the button, a criteria box opens asking for the name of the Industry. Fill in the name of the Industry and the email addresses of all the contacts in that Industry are sent to an Excel file on the Desktop. There may be another way of achieving the end result. I hope this explains my situation. Leon

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

Similar Threads

  1. Adding a combo box gives me an error message
    By gutenberg in forum Forms
    Replies: 1
    Last Post: 07-26-2016, 12:00 PM
  2. Replies: 7
    Last Post: 03-17-2016, 05:53 PM
  3. Replies: 14
    Last Post: 03-31-2015, 05:20 PM
  4. Replies: 3
    Last Post: 10-30-2013, 05:59 AM
  5. Combo box error message
    By pbouk in forum Forms
    Replies: 5
    Last Post: 05-08-2013, 07:02 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