INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Query criteria cannot find a subforms unbound field

Query criteria cannot find a subforms unbound field

(OP)
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.



RE: Query criteria cannot find a subforms unbound field

(OP)
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??

RE: Query criteria cannot find a subforms unbound field

"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:

CODE --> SQL

[Forms]![frmMoreLookups]![cboCusStatus2] 

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

Duane
Hook'D on Access
MS Access MVP

RE: Query criteria cannot find a subforms unbound field

(OP)
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.ac...

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

RE: Query criteria cannot find a subforms unbound field

(OP)
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.

RE: Query criteria cannot find a subforms unbound field

(OP)
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

RE: Query criteria cannot find a subforms unbound field

(OP)
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.

RE: Query criteria cannot find a subforms unbound field

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close