Page 1 of 3 123 LastLast
Results 1 to 15 of 40
  1. #1
    mikel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    37

    Question Empty Lookup

    I have a main table and one of the fields is called "Procedure". This is a lookup field that looks up the data from another table called "Procedure". In the "Procedure" table my users can enter a name and details for different procedures. I created a form where the users can enter main information but when they go to the next entry, I get a error message that says "The Microsoft Office Access database engine cannot find a record in the table 'Procedure' with key matching field(s) 'Procedure'". How can I make this work so that an empty procedure is ok? Is it possible?

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You're not using Lookup Fields are you?

  3. #3
    mikel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    37
    Yes I am, I think. I recreated the field and selected "Lookup Wizard" for the data type. I ran through the wizard and when I'm looking at the table in datasheet view, it works fine.

    My problem must be on the form I created to enter the data into the tables. I tried creating a quick new form and it seems to work fine without that error. But as soon as I add the details field it only shows the records that have a procedure selected.

    When I enter information into the lookup wizard I use the ID and name. But on my form I need to see the name and details (which is another column in the Procedure table). When I make a new record it won't allow me to save it unless I have selected a procedure. The other combo boxes that were built with lookup wizard are working fine. Does it have to do with the number of columns that I select in the lookp wizard. I'm just not understanding what the difference is between the ones that work and this one.

    Mike

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    If you follow the link I provided in post #2 you will see that it is not recommended to use Lookup Fields. ComboBoxes on forms are just fine but not Lookup Fields in tables. If you change the Lookup to a Textbox, you will see what is really in the table.

  5. #5
    mikel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    37
    How do I link that field to another table without Lookup? The users will need to be able to add to the Procedure table.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Assuming your two tables have a defined relationship you should be able to bind your form to a query that joins the two tables on the related field. Then the fields of both tables are available to display/edit.

  7. #7
    mikel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    37
    I'm pretty new to Access so I'm not sure what you mean. I started a new file to try and test/learn. I have the tables and they have a one-to-many (Procedure table on the one side and Gage on the many side) relationship. I linked them in the Relationships window by dragging the primary key (procedure_ID) from the procedure table over the procedure field in the gage table. Is that the correct way of linking the tables?

    Then I highlighted the Gage table and created a form. It automatically entered all of the fields that are in the table. But since I didn't use lookup tables, when I view the form in form view, the boxes that should have drop down selection boxes, don't. How do I fix those so the users can pick a selection from a list?

    I don't know how to "bind your form to a query that joins the two tables".

    Sorry for all the questions.

    Mike

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    It sounds like you have created the relationship between the two tables correctly. Normally we would show a 1:m relationship with a Form/SubForm arrangement. The Form would be bound to a query of the One side table and the SubForm would be bound to a query of the Many side table. The LinkMaster/Child Fields of the SubForm control would be set to the field the two tables have in common.

  9. #9
    mikel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    37
    Ok, that all makes sense. Can I do it the other way and still have it work? Make the Many side the form and the one side the subform? What is the purpose of making the Query and then making the form from the query instead of just making the form from the table? What fields should be in the query, all of them?

    Mike

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    If I understand you correctly, each Gage only has one Procedure so reversing them does not make much sense to me at this point. Queries are *far* more powerful than tables directly and do not have the record locking problems that comes with opening tables directly. The ComboBox wizard knows how to look up values in other tables and put those values in the current table.

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Does that make any sense Mike? What field(s) are you trying to change with a ComboBox?

  12. #12
    mikel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    37
    The main reason for this little program is to have a database of the gages we use. So that's the main table, Gage. In that table there is a bunch of details that need to be stored with each gage. One of those details is the procedure that we used to certify it. Since the procedures will change over time and we may add or remove them, I figured I would make a new table called Procedures and link them. They are linked with the Gage table on the many side and the Procedure table on the one side.

    The form I would like to create is a form to enter Gage information. I had made some test forms just to try and figure out how some of this works, and found that I would like to have a form with tabs. That part I can figure out. The first tab would have general information about the gage (manufacturer, owner, type, size, accuracy, etc.) from the gage table that the users can enter. The second tab I would like the user to be able to select from a list the procedure that is used. Once the procedure is selected I was hoping that below it on the same tab it could display the actual procedure just for reference.

    There is also a Calibration table that records calibrations for all of the gages. It is linked to the Gage table with the Calibration on the many side and the Gage on the one side. On the next tab of the Gage form I was hoping to show some general information about the gage just for reference and then an area where the user can enter calibration information. finally, the last tab would show a history of the calibrations for the gage that is selected.

    That's the big picture, just having a hard time understanding it.

    Any thoughts? Can I use a form like Gage even with it being on the many side of the Procedure table? Am I going about this all wrong?

    Thanks for your help!

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I see what you need to accomplish now. Let me give it some thought and I'll get back to you.

  14. #14
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    OK, base your Gage form on a query of the Gage table and then join the Procedure table to the query. This allows you to pull the Procedure field for the current record in the Gage table and when you change the ProcedureID field in the Gage table, the ProcedureDescriprion will just magically change.

  15. #15
    mikel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    37
    Sorry that it's been so long but I'm finally getting back to this project and had some more questions that I was hoping you could help with.

    How do I "join the Procedure table to the query."? If I have the query open in Design View do I just add all of the fields from the procedure table? Or do you mean something else?

    If lookup fields are bad in tables, how do you let the user select from a list? On a test form I can add a list box and they can select it but that selection doesn't get "pushed" back to the table? Am I missing something?

    Do I need to make seperate tables for every field that I was using the lookup tables for? If I did then the relationships would look like the attachment, right?

    Mike

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

Similar Threads

  1. Copying into empty fields until...
    By cochi30 in forum Programming
    Replies: 8
    Last Post: 06-07-2009, 07:38 AM
  2. Hiding fields that contains empty records
    By sakthivels in forum Reports
    Replies: 4
    Last Post: 05-27-2009, 07:06 AM
  3. How to get rows with empty fields as well?
    By Orabidoo in forum Queries
    Replies: 10
    Last Post: 04-24-2009, 06:08 PM
  4. Access 2003 returns empty recordset
    By Leelers in forum Queries
    Replies: 0
    Last Post: 03-20-2009, 11:11 AM
  5. Replies: 1
    Last Post: 07-14-2008, 12:15 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