Problem with query filter

[spectrolab]

Hi,

I am having a few issues with a query that is used to filter results in a form, the SQL follows

SELECT tblMoisture.SampleName
FROM tblSampleSubmission INNER JOIN tblMoisture ON tblSampleSubmission.SampleName = tblMoisture.SampleName
WHERE (((tblSampleSubmission.SubmissionNumber)=[Forms]![frmH2OSelect]![cboSelectH2O]));

It seems to work fine when I run as is, but when it is called from the form, frmH2OSelect (using a macro, open form then apply filter) it asks for tblSampleSubmission.SubmissionNumber, even though the form is still active.

I sthere something wrong with relationships between the 2 tables? It should a one-to-one relationship between SampleName. tblMoisture does not contain the field SubmissionNumber, but if I include it in this table (not very normalised) it works fine.

Any help would be appreciated.

 

[The_Doc_Man]

When a form or query asks you for something, it is spelled incorrectly vs. whatever you used in the form/query as a criterion. OR the item you used is spelled correctly but isn't visible at the time (a variable scoping issue). So Jet thinks it is a parameter query and asks for the missing parameter.

Can't tell which exact problem you have from here, but if you look at it carefully you might find it now that I've given you a clue.

 

[spectrolab]

Thanks for the reply, but since the post I realised I was going about it the wrong way. Instead I based the form on the query, not the table with a filter, it works fine now. Thanks for the guidance.

 

[petehilljnr]

So if you open the form frmH2OSelect and select a SubmissionNumber in the combo box cboSelectH2O then run the query from the database window, does it work fine there?

 

[spectrolab]

Yes, it works fine, I found some advice from www.fontstuff.com on how to do this. If the query has everything you need in the table and the relationships are correct, it works fine. The form frmMositureInput is now based on the query, not the table and using the query as a filter. There might be a better way to do it, but it works. To make it as simple as possible for other users, I put the following code in the AfterUpdate area of the combo box

Private Sub cboSelectH2O_AfterUpdate()
DoCmd.OpenForm "frmMoistureInput", acViewNormal, acEdit
 
 
   DoCmd.Close acForm, "frmH2OSelect"

End Sub