Combo box to set value in table

Posted on 2008-11-08
Last Modified: 2013-11-28
I have 2 tables Monitor and Mileage.  Created Form and subform to use combo box to show mileage for one particular monitor.  Works fine.  When I open the form the form shows the combobox, allows me to choose which monitor and then show only that monitors mileage.  
Problem comes when I attempt to add new mileage to that monitor I get following message "The Microsoft Jet database engine cannot find a record in the table 'Monitor' with key matching    field(s) 'Mileage.MonitorId" (Error 3101)"  How do I set the value for the "Mileage.MonitorId" to be the same as the combo box?

Question by:softsupport
    LVL 29

    Expert Comment

    How are the two tables related?
    LVL 10

    Expert Comment

    How did you set up your combo box? Try using the wizard, and select the 'Find a record on my form based on the value I selected in my combo box' option. Make sure you include the MonitorID field as well as the MonitorName field when asked which fields you want to include in your combo box. You'll be given the option to hide the key column (MonitorID) in the next step so it will still look like it does now.
    LVL 74

    Accepted Solution


    My guess is that something got crossed when you related the tables.

    The hint is that "MonitorType" does not belong in the Milage form. (it belongs on the Main (Monitor) form).
    If you select a monitor, they should always be the same type, so there is no need to see this repeating data in the subform.

    Always keep in simple at first.
      Make the tables
      Relate the tables
      Make the main form
      Make the subform
      Drop the subform on the main form, ...and TEST IT.

    After you are sure it is working, ...THEN create any source queries you might need.

    Here is a sample that approximates your setup.


    Author Closing Comment

    Thank you for your assistance.  I was so close.... I had the 2 tables (Monitor) and (Mileage) in the query which was causing the problem.  One the Monitor table was removed and I recreated the subform.... It worked like a charm.  Thank you for the example.  It showed me how to fix my error and your explanation reminded me of the KISS method... (Keep It Simple Stupid) (smile!)  Thanks again!!!!!!!!!!!!!!!!!!!1
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    Don't feel bad, I have to remind myself all the time to "keep it simple", as well!

    You can still create a query for the subform. You just have to be carefull of what fields you are bringing in , and why.


    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
    User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now