Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

Combo box to set value in table

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?

1 Solution
How are the two tables related?
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.
Jeffrey CoachmanMIS LiasonCommented:

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.

softsupportAuthor Commented:
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
Jeffrey CoachmanMIS LiasonCommented:
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now