Referencing a form control from a Query.

[Tom55]

Hey all,

Have a very annoying issue that has had me stumped for the last two days; whenever I use a reference to a control in another form as a criteria for my query, it wont recognise it. The form is always open as you cannot access the query without it.

I am using;

[Forms]![Candidate]![WorkEmail] Or [Forms]![Candidate]![PrivateEmail]

as the criteria in the Query. When I run it the Query prompts the user to enter a value for the criteria instead of detecting the existing strings (just like a parameter Query). Can anyone explain why it isn't working? I run the Query from another control using a simple 'Requery'. Ideally I want it to run on load but I believe it is trying to pull information from a form not fully loaded and so wont work properly.

So my main issue is why my criteria isn't recognising the form control as a reference. The control is in a Tab, but searches assured me that is no different from being in Detail.

Anyone that can shed some light on my problem?

Thanks,
Tom

 

[vbaInet]

Welcome to AWF!

You've probably put it in the wrong place. Show us a print screen of the query in design view or paste the sql statement here.

 

[Tom55]

Hi, thanks for the welcome.

What do you mean by wrong place?

Here is the SQL script in my Query;

SELECT Email1.Subject, Email1.FromAddress
FROM Email1
WHERE (((Email1.FromAddress)=[Forms]![Candidate]![WorkEmail] Or (Email1.FromAddress)=[Forms]![Candidate]![PrivateEmail]));

 

[vbaInet]

It looks correct. Is the form open and with text before you open the query?

 

[Tom55]

It is, it's not possible to access the Query without the form open, since the button that runs it is on that form.

 

[vbaInet]

It would normally prompt if the form is not open or if it can't find the control based on your reference. Check the names again.

You can also post a stripped-down version of your db and I'll have a quick look.

 

[Tom55]

That's what has stumped me, the fact that the form is clearly open when asking for the references. I've checked and triple checked every name involved and all match perfectly.

My email controls are in the Detail section of a form, inside a 'Rectangle' that is also inside a Tab. Does that make any difference?

I would let you take a look but our database is quite massive, it would take quite a while to strip it and post it.

This is just one of those things that obviously should work, but don't, for no reason. But as soon as the reason is discovered, I'll kick myself, happens every time!

 

[vbaInet]

You can export the necessary bits, like the form, the query and the tables (with dummy data) to a blank new database shell and post here.

What do you get when you msgbox the value?

Msgbox nz(Forms![Candidate]![WorkEmail], "1. Is Null") & vbnewline & nz(Forms![Candidate]![PrivateEmail], "2. Is Null")

 

[Tom55]

When I run that msgbox I get the Work Email and Private Email addresses of the current candidate displayed. Works as it should, the Query just wont recognise it.

 

[vbaInet]

What about creating a new query with just those two fields and including the criteria in there?

 

[DCrake]

Take a look at this link it may point you in the right direction. See documentation for a clearer understanding of how it woks and how much more versatile you app can become.

 

[viennese_finger]

hi all - not sure if i should reply to this or start a fresh post...?

anyway, here goes:

I have a form (frmQuotesIssued) with a subform within (qryQuotedServices subform). It is the subform I'm interested in. The subform contains records that relate to the main form ID (one-to-many). the default view for the subform records is 'continuous forms'.

I have a combo box ("Services") on the subform that pulls values from a table (tblServices) and stores the foreign key from tblServices in tblQuotedServices.ServiceID_FK. I would like to limit or filter the rows or records available on the Services combo box based on the value of another combo box ("service type").

I have tried referencing the service type value as a criteria in the query for the combo box, but when i test the form it just asks for the parameter and doesn't look at the service type value. also it seems to use the same entered value for all the records in the subform regardless of the individual servicetype values for those - they are different for most records.

anyway hope this makes sense

Cheers!

Matt