Pulling one value from a query

[Adien]

Is there a way using a form to pull one value from a query. Basically
I have a table that has my pricing and I want to run a select query to
grab all the values and populate about 12 unbound txt boxes. I could
make 12 queries, one for each value, but theres gotta be a better
method.

Thanks,
Adien

 

[Douglas J. Steele]

You can use DLookup (i.e. Set the ControlSource property of Text1 to
=DLookup("Field1", "QueryName"), for Text2 to =DLookup("Field2",
"QueryName") and so on), or you can use a recordset:

Dim rsCurr As DAO.Recordset

Set rsCurr = CurrentDb.QueryDefs("QueryName").OpenRecordset
Me.Text1 = rsCurr!Field1
Me.Text2 = rsCurr!Field2
....
Me.Text12 = rsCurr!Field12
rsCurr.Close
Set rsCurr = Nothing

 

[Adien]

I'm sorry, I wasn't clear in my initial question. The setup of the
table has three fields: Name, Type, and CPO (Cost Per Ounce). All the
values I need to pull are in the CPO field. So I'd need something like
txtCPOChicken = DLookup("CPO" where Name = "Chicken", "Qry_FoodCost").

Thanks,
Adien

 

[Douglas J. Steele]

In that case, you'd have something like

txtCPOChicken = DLookup("CPO", "Qry_FoodCost", "[Name] = 'Chicken'")
txtCPOBeef = DLookup("CPO", "Qry_FoodCost", "[Name] = 'Beef'")

and so on. Note a couple of things. To make it simpler, I'm using single
quotes around the name of the product. Exagerated for clarity, that 3rd
parameter in the DLookup is " [Name] = ' Chicken ' ". I've also put square
brackets around the field name Name. That's because Name is a reserved word,
and should not be used for your own purposes. You really should rename that
field. For a good discussion on what names to avoid in Access, see what
Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

The other alternative would be:

Dim rsCurr As DAO.Recordset

Set rsCurr = CurrentDb.QueryDefs("Qry_FoodCost").OpenRecordset
Do Until rsCurr.EOF = True
Me.Controls("txtCPO" & rsCurr![Name]) = rsCurr!CPO
rsCurr.MoveNext
Loop
rsCurr.Close
Set rsCurr = Nothing

 

[Adien]

Perfect. Thanks for the help and the pointers Doug. I took your advice
and switched it to FName.