Query Criteria

[dylcon]

Hi, I am using a form to filter the records from a table based on a specific field. When I run the query, I set the form's specific field in the 'criteria' portion on the query. Here is my syntax:

[Forms]![MainMenu]![NavigationSubform]![PartRecordForm].[Form]![PartNamecbo]

but I keep getting prompted for this value each time it is executed. Does anyone have any suggestions?

[ItsMe]

How are you adjusting the form's filter? What is the prompt describing as missing ? Is it looking for the control PartNamecbo or a field within the recordset?

Can you post all of the code relevant to your Where criteria?

[rpeare]

Is your subform a continuous or single?

if it's continuous your query will really have no way of distinguishing which record is the correct one to look for.

I'm assuming this is for a query that is not driving the main form either, that you're trying to use an item selected in the subform as part of, say a query or report, that is unrelated to the form itself?

[ItsMe]

@ rpeare

Seems like a navigation form/control also

[June7]

Don't use navigation forms! (personal opinion - don't like them, find them harder to work with)

Is this a web database?

Review https://www.accessforums.net/forms/t...orm-32053.html

Maybe:

[Forms]![NavigationForm]![NavigationSubform]![PartRecordForm]![PartNamecbo]

[ItsMe]

Maybe:

[Forms]![NavigationForm]![NavigationSubform]![PartRecordForm]![PartNamecbo]

I just realized I am in the Queries forum.

[dylcon]

June7, I tried your suggestion, but am still having the same problem. My entire db is using a navigation form and is quite large so I am not sure if I want to change it now... any other suggestions?

I also believe that it is a single form.

[ItsMe]

You do not have to use parameterized queries to include dynamic criteria. You can adjust the recordsource of the form using the form's On Load event handler.

[dylcon]

Ok, so how exactly would I do this?

[June7]

Think will still be same issue of referencing the control on Navigation form in code (VBA or macro). Navigation form really complicates. Sorry, I have nothing else. The link I referenced is as far as I got with Navigation form.

[ItsMe]

I don't know where the control with the variable is located. Like June said, you may run into the same object reference issue. Can you explain where the subform is that needs the filtered recordset and where the combo is?

For instance, the combo is in a main form; the Navigation control is in the main form, the subform is in the nav control's container.

Or maybe you used the wizard to build a Navigation form. Then explain where the objects are for that scenario.

[ItsMe]

Another option may be to use the Navigation Button's "Navigation Where Clause" property. Maybe you could associate that property with an afterupdate event of a combo.