subform requery macro doesn't work in main form 1

[pl626]

I have and Access 2007 app with a subform that has 3 controls that pass filter criteria to from one control to the next. When only the subform is open, it works fine. However, when I run the subform from my main form, I get a prompt to enter the value from the first control. How do I get the subform to work from the main form?

 

[alvechurchdata]

It's got to be something to do with the way you're referring to the value you're using inside the filter. Perhaps you've got to give the full name of that value - something like mainform.subform.control.value.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[pl626]

I've tried referencing the subform control in the mainform using 'Forms!<Main Form>!<Sub-Form Name>.Form!<Combo Box name>.Requery' with events:After Update, On Lost Focus, On Open, etc. No luck.

The main form does not pass any data to the 3 subform controls. It only collects information for a header table. The subform is used to collect records for a child table, which may have many records for each header record.

I even tried changing the code on the subform to use VBA instead of the macro:

Sub ComboBox1_AfterUpdate()
ComboBox2.requery
End sub

Sub ComboBox2_AfterUpdate()
ComboBox3.requery
End sub

Like the macro method, this worked fine in the subform alone, but from the main form, I got a "Run-time error '2450' " stating it can't find my subform. How do I reference the subform from the mainform? To which event should the requery be bound or do I need to DIM something in the main form?

 

[dhookom]

Do you understand there might be a difference between the subform name and the subform control name? A subform control on a main form has a Source Document property that contains the name of the subform. The subform control also has a Name property that can be anything.

If you can't figure this out, can you explain

3 controls that pass filter criteria to from one control to the next

.

Duane
Hook'D on Access
MS Access MVP

 

[pl626]

Duane,

I thought the main form subform control name might be the problem, but it's not. I'm assuming by 'Source Document' you mean 'Source Object'? In my case, the SO is 'sf_AIRCRAFT_FLT_LOG' while the 'Name' is 'AIRCRAFT_FLT_LOG Subform'.

On the subform, I have 3 Combo Boxes bound to queries. These are to facilitate data entry on the child table. The first CB value is passed to the query that filters the selection for the second CB. Both of these values filter the third CB.

 

[dhookom]

Yes, I did mean "Source Object". You haven't yet provided the SQL views of your combo boxes. I believe if your form works as a stand alone but fails when used as a subform, the criteria in the SQL row source is wrong.

Duane
Hook'D on Access
MS Access MVP

 

[pl626]

the rowsouce in the sf is a query called 'qry_cnx_primary' and the actual SQL is:

SELECT [CNX Codes].[MSN Abort: Primary]
FROM [CNX Codes]
GROUP BY [CNX Codes].[MSN Abort: Primary];

 

[dhookom]

3 controls that pass filter criteria to from one control to the next

You haven't yet provided the SQL views of your combo boxes

rowsouce in the sf is a query

I think there is a huge divide between what I am asking and what you are providing. I don't care what the Record Source of your subform is since it doesn't have any apparent criteria. I expect your "3 controls" (can we assume one or more of these are combo boxes) have a Row Source that we can't see. Help us out?


Duane
Hook'D on Access
MS Access MVP

 

[pl626]

Sorry, I meant to say the Row Source for the first CB is a query called 'qry_cnx_primary', the second CB has a Row Source from a query called 'qry_CNX_secondary' and the third CB has a Row Source from a query called 'qry_CNX_tertiary'

The secondary SQL is:

SELECT [CNX Codes].[MSN Abort: Secondary]
FROM [CNX Codes]
GROUP BY [CNX Codes].[MSN Abort: Secondary], [CNX Codes].[MSN Abort: Primary]
HAVING ((([CNX Codes].[MSN Abort: Primary])=[Forms]![sf_AIRCRAFT_FLT_LOG]![MSN Abortrimary]));

and the tertiary SQL is:

SELECT [CNX Codes].[MSN Abort: Tertiary]
FROM [CNX Codes]
WHERE ((([CNX Codes].[MSN Abort: Primary])=[Forms]![sf_AIRCRAFT_FLT_LOG]![CNX Primary]) AND (([CNX Codes].[MSN Abort: Secondary])=[Forms]![sf_AIRCRAFT_FLT_LOG]![CNX Secondary]));

 

[dhookom]

You must start your expression with the name of the main form. If the name of your subform control is "AIRCRAFT_FLT_LOG Subform" then use it rather than the name of the source object "sf_AIRCRAFT_FLT_LOG".

[Forms]![frmMainName]![AIRCRAFT_FLT_LOG Subform].Form![MSN Abort:Primary]

I think you already understand you will need to requery the dependent combo boxes in the after update of leading combo boxes.

Duane
Hook'D on Access
MS Access MVP

 

[pl626]

Duane, thx so much. You were absolutely right. Once I qualified the SQL in the bound queries to use the mainform.subform, it works like a champ!