Query criteria cannot find a subforms unbound field 1

[cimoli]

I am having trouble getting a subform to be recognized with a query and after update.
I want to stay with an Access criteria query and avoid vba and sql.
I will first show you what works and then the part that does not work.

1 - If I put my unbound lookup fields on the parent form and use the below criteria
in the underlying query, I CORRECTLY get my desired records.

[Forms]![frmCustomer]![cboCusStatus2]

The unbound field in the parent form has the following after update.

Private Sub cboMailRegion2_AfterUpdate()
On Error GoTo Err_cboMailRegion2_Click
Me.RecordSource = "Select * From [qryCustomerInfoMailerRegion2]"
Exit_cboMailRegion2_Click:
Exit Sub
Err_cboMailRegion2_Click:
MsgBox Err.Description
Resume Exit_cboMailRegion2_Click
End Sub

Works fine.

2 - However, to give me me more space on the parent form, i want to show the lookup field instead
by using a BUTTON on the parent form and have a subform popup called frmMoreLookups.

So I thought that I only needed to add the subform frmMoreLookups into query criteria. But it does not work. I get no records.

CusStatus query field criteria:
[Forms]![frmCustomer]![frmMoreLookups].[Form]![cboCusStatus2]

What am I doing wrong?

Thanks alot.

 

[cimoli]

In case you are wondering why I prefer to use ... Me.RecordSource = "Select * From [qryCustomerInfoMailerRegion2]" ....
I really have 3 criteria's in the query. I am just showing one here to make things simple.

I realize that I could have used some vba with this.

But I think it easier for us to use the Qry as a recordSource.
I can adjust the qry faster.

As summary, i have a parent form called frmCustomer with a Button on it that takes me to another form called frmMoreLookups.

On the 2nd form, i pick some criteria. Hit enter and the appropriate records appear in frmCustomer form.

This all works if i do not use the frmMoreLookups.
So maybe i need the word Parent or something??

 

[dhookom]

takes me to another form called frmMoreLookups" This suggests the frmMoreLookups is not a subform since it is not embedded in a subform control on the main form. It is its own form. Try simply:

[Forms]![frmMoreLookups]![cboCusStatus2]

All of this assumes frmMoreLookups is open and a value is in cboCusStatus2.

Duane
Hook'D on Access
MS Access MVP

 

[cimoli]

Duane - You are correct. I was not clear. My issue is about two forms.
Maybe I need a GotFocus or something? My method 1 definitely works using only an Access query with criteria which
will pull up matching records shown in frmCustomer. So my method works. using strict Access and no vba or sql code.

But when i use the method on another form (frmMoreLookups), i cannot get the frmCustomer records to collect.
I realize that i am using another form to make another form work. Sounds crazy, right?

But is there some kind of vba or sql short code to tell the frmMoreLookups to show the records in the frmCustomer ??
Just giving this a shot.


I made a very tiny example if you want to play around.
I show method 1 on frmCustomer and method 2 on frmMoreLookups.
The method 1 works just fine. It is method 2 that does not collect the records
to be shown on frmCustomer.

https://www.dropbox.com/s/3g1e0gg9tiqecp0/Hook1.accdb?dl=0

Thanks you, if you think this is a worthy request.

 

[dhookom]

Did you change the criteria in the query?

Duane
Hook'D on Access
MS Access MVP

 

[cimoli]

Yes, I changed my query criteria to match your method. It still works on the frmCustomer. I also changed my frmMoreLookups to your good method.

As a test, you could try on the frmCustomer lookup 4 fields .... Active, 0 , 2000, cle
You should get 8 people.

Then try ... prospect, 0, 2000, cle
You will get 41 people.

Thanks for looking at this. This will be a neat way to put the lesser used lookups onto another form, yet
retrieve records on the main form. If possible. Maybe some sort of GotFocus is needed or something along those lines
so that the frmCustomer gets the necessary records from the frmMoreLookup's underlying query.
Maybe this is a mission impossible.

 

[cimoli]

Duane - I just saw your preferred method to uploading a file. I just did so. Hope this helps explain my situation where I would like a 2nd form (frmMoreLookups) to house several lookups to be used for the main form (frmCustomer) records. thanks

 

[cimoli]

PS - I am trying mostly to utilize the Access criteria method and not Vba or SqL so that i can easily change my criteria whenever I want to in an Access query.

 

[dhookom]

I would remove all references to controls on forms from your query criteria. My preference is to use code to set the filter properties of forms and reports.

Duane
Hook'D on Access
MS Access MVP