Combo Box on Form Causing Problems

[manybeats]

I have a query that pulls information from three tables:

tblEquipConsCode (table basically lists types of equipment - e.g. sampling supplies)
ECPK
ECCodeDescription
ECDefaultUnit

tblProjectEquipCons (table captures the project specific equipment and rates)
PECPK
ProjectFK (ties into my Project table)
ECFK (Foreign Key from above table)
PECRate
PECUnit

tblEquipConsReport (table captures details of each use of equipment)
ECRPK
ECDate
PECFK (Foreign Key from above table)
ECQuantity
ECComment
CELFK (ties into another table, basically a sub-project of the Project table)

I am using this query as the row source of a combo box on my form and the control source is PECFK.

In another field, I have ProjectFK. When I try to filter my combo box to only show the equipment from a certain project (using [Forms]![FormName]![FieldName]), I run into problems. My combo box list comes up blank and when I try to exit or delete the record, I get the message "The Microsoft Access database engine cannot find a record in the table 'tblProjectEquipCons' with key matching field(s) 'PECFK'. It can't, of course, find this record because there is no such field in that table. I can't figure out why it keeps wanting to look in that table.

I'm hoping someone can help me figure this out. I imagine it is an obvious solution, but I've been working on it all afternoon and evening and just don't get it.

Thanks,
Jennifer

 

[John Big Booty]

Are you able to post a copy of your DB? ('03 version for preference certainly no newer than '07)

 

[manybeats]

Thanks for replying. Here is the database. I converted it from Access 2010. It's the form frmTimesheet_EquipConsSubform that I'm specifically having issues with.

 

[John Big Booty]

OK. I've had a chance to have a play around with your DB.

The problem is your Combo is on a Sub Form, and the Syntax for referring to objects and control on Subforms is a little different, you had;

[Forms]![frmTimesheet_EquipConsSubform]![txtProjectFK]

but it should be;

[Forms]![frmtimesheet]![frmTimesheet_EquipConsSubform].[form]![txtProjectFK]

That overcomes part of the problem. The next part is that your Combo is on a Datasheet form, so as your focus shifts to a new record you Dependant Comb (which this is) will change to reflect the value held by txtProjectFK in the current record, this change will be reflected in the combo on all records irrespective of the value held in each record.

Given that this is a dependant Combo you will also need to requery it on the Main form's On Current event;

[Forms]![frmtimesheet]![frmTimesheet_EquipConsSubform].[Form]![cboEquipCons].Requery

There also seems to be a problem with the linking of this subform and it's parent, which you will need to resolve.

 

[John Big Booty]

Here's your DB with those adjustments.

I've not looked at the linking problem

 

[manybeats]

My syntax error makes sense, but I'm still getting the error message: "The Microsoft Access database engine cannot find a record in the table 'tblProjectEquipCons' with key matching field(s) 'ProjectEquipConsFK'" when I try to enter a new record on the subform. I think the link I have between parent and child is correct, but will look at it more closely.

From what I've read, there is no real solution to the dependent combo box on datasheets. I've read about covering the combo box (except the arrow) with a text box that shows the real value selected in the combo box. That seems to be the best option, but I'm not sure if it will work perfectly on various computer screens (i.e. if the size will change and then show some of the field below).

Anyways, I really appreciate your help and will keep plugging away.

 

[John Big Booty]

I believe that the Jet error is being caused because the Parent ID is not being passed through to the Subform to be saved as the Child ID, and the link between the two records can not be established as is required by the Form/Subform criteria.

The only way around the Dependant Combo box issue is to to populate your sub form with a query (and do away with the combo box) and use a pop-up from to add new records to that form. You can probably also use this to get around your current problem linking the parent child records, as the Parent ID would need to be passed to the Pop-up form which can then be save all in the one hit.