Pass query calculated value to form

[Acipenser]

I have a form (Form1) that gets a calculated value from a query. Form1 is not based on the this query though (based on another query), so I am currently opening another form (Form2)to store the value of the query temporarily and then use the value of the field in Form2 as the control source for the field in Form1. This works, but I am sure it is not the best way to do it.

I could not get it to work by opening the query and then making the control source for the field in Form1 be the value of the field in the query. All I got back was ?Name.

Got to be a better way. Any suggestions are appreciated.

 

[Remou]

Have you looked at DLookUp, depending on your query, it may suit. Alternatively, you could use a recordset. Without a little more information, it is not possible to say exactly.

 

[randy700]

How about...

=DLookup("FieldName", "QueryName", [i]"Where condition"[/i])

Randy

 

[Acipenser]

I don't think DLookup will work because the query uses 2 parameters. Can you use DLookup with 2 parameters? I did not see any in the help section.

 

[Remou]

You can.
=Dlookup("Fieldname","QueryOrTable","Field1=2 And Field2='" & Me.txtTextbox & "'")

 

[Acipenser]

I tried the following code in the control source for the text box, but no luck (#Name? is returned).

=DLookUp ("[CountOfStrawID]","qryNumberofStrawsAvailable","[Shipped] = No" And "[SampleID] =" & Me.SampleID)

The query should count the number of straws that do not have a check in the field shipped and have the SampleID specified on the form.

Again the query itself works fine when I open it, I just can't get the value into the text box. Any more suggestions?

 

[Remou]

I think in this case you would be better with Dcount.
[tt]=DCount("*","tblStrawsAvailable",","[Shipped] = No And [SampleID] =" & Me.SampleID)[/tt]

You should not have the quote after No and Before [SampleID].
Is Shipped a YesNo datatype?
Text fields need single quotes.

 

[PHV]

=DCount("*","tblStrawsAvailable","Shipped=False And SampleID=" & [SampleID])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Acipenser]

Remou and PHV,

Thanks for the help. It is working perfectly now.
Much nicer than I had it before!!