Solved Reset combobox if user clicks another form (1 Viewer)

foshizzle

Registered User.
Local time
Today, 17:31
Joined
Nov 27, 2013
Messages
277
I have a form with two subforms; one in the header and another in the detail area of the main form.
The header is used for data entry and contains a bound combobox which allows other controls inside the header to be populated specific previous record information found in the detail form.

Problem:

When something is selected from the combobox and the user clicks off the header form, I receive the following message:
The Microsoft Access database engine cannot find a record in the table 'tblTrucks' with key matching field(s). TruckNumberLookup.

I imagine I need a way to reset this combobox to default if something outside the header form is selected. What is the proper way to resolve this problem?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:31
Joined
Feb 28, 2001
Messages
27,001
This message suggests that you have relational integrity enabled with a formal relationship between whatever is in the combobox and some other element, and that other element doesn't contain the value selected in the combo. I also suspect part of your problem, from the field name, in that error message, is that you have used a lookup field in a table. That error message is one of the reasons why we never recommend using such things. What is PROBABLY happening is that the combo's bound field and displayed field are not the same column, and only one column can be the "returned" value from a combo. You CAN display one column and use a different column, but I am thinking you did this wrong. If you really DO have a lookup field, my best advice is to get rid of it and have the lookup in a form, not in a table's field.
 

foshizzle

Registered User.
Local time
Today, 17:31
Joined
Nov 27, 2013
Messages
277
Hi Doc, thank you for your insight. I'm not 100 percent sure I follow however please allow me to provide some clarification as I've further found the error appears when a value is entered in ANY control inside the header, then clicking off of the header form. It also seems this action is creating a new record when this happens.

I'm attaching a photo of the query relationship for this form as well as a copy of the DB for reference. Thank you for any further insight you can provide.
 

Attachments

  • relationship.jpg
    relationship.jpg
    98.7 KB · Views: 98
  • myDB1.accdb
    768 KB · Views: 105

theDBguy

I’m here to help
Staff member
Local time
Today, 14:31
Joined
Oct 29, 2018
Messages
21,358
can anyone help?
Hi. This looks familiar. I downloaded your file but couldn't get an error. Actually, I can't even change the combobox selection (after the first time).
 

foshizzle

Registered User.
Local time
Today, 17:31
Joined
Nov 27, 2013
Messages
277
Hi. This looks familiar. I downloaded your file but couldn't get an error. Actually, I can't even change the combobox selection (after the first time).

Hi again! Yes, you got me this far. I believe I may have figured out what Doc was saying but I'm getting a new error 'You must enter a value in the 'tblMeterReadings2.MeterTruckNumber' field. I think this is because I need to find a way to clear the changed value of the combobox back to default.

In relation to your question, I locked the combox control after update as well in attempts to prevent Access from writing a new record if the user changes the value. Not sure why that was happening
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:31
Joined
Oct 29, 2018
Messages
21,358
Hi again! Yes, you got me this far. I believe I may have figured out what Doc was saying but I'm getting a new error 'You must enter a value in the 'tblMeterReadings2.MeterTruckNumber' field. I think this is because I need to find a way to clear the changed value of the combobox back to default.

In relation to your question, I locked the combox control after update as well in attempts to prevent Access from writing a new record if the user changes the value. Not sure why that was happening
Okay, so do you still need any help? If so, please provide a step-by-step instruction on how to duplicate the error. Thanks.
 

foshizzle

Registered User.
Local time
Today, 17:31
Joined
Nov 27, 2013
Messages
277
To duplicate the error:
Update any control on the header form. Then click on any part of the sub form. You will receive the original message
"The Microsoft Access database engine cannot find a record in the table 'tblTrucks' with key matching field(s). TruckNumberLookup."
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:31
Joined
Oct 29, 2018
Messages
21,358
To duplicate the error:
Update any control on the header form. Then click on any part of the sub form. You will receive the original message
"The Microsoft Access database engine cannot find a record in the table 'tblTrucks' with key matching field(s). TruckNumberLookup."
Okay, I did the following:
1. Opened the form
2. Selected Truck91 from the dropdown
3. Entered 123 in Current Meter 1
4. Clicked in the other subform

Was I supposed to see the error? I didn't.
 

foshizzle

Registered User.
Local time
Today, 17:31
Joined
Nov 27, 2013
Messages
277
Okay, I did the following:
1. Opened the form
2. Selected Truck91 from the dropdown
3. Entered 123 in Current Meter 1
4. Clicked in the other subform

Was I supposed to see the error? I didn't.

I'm losing my mind. Ok, so after entering the above, now click the Reset button and clicking into the subform.. Perhaps its an issue with the Reset code.
 

foshizzle

Registered User.
Local time
Today, 17:31
Joined
Nov 27, 2013
Messages
277
I finally got this working by removing all bound controls from the header. thanks for everyones insight.
 

Users who are viewing this thread

Top Bottom