How can I use a combo box entry to complete the name of a field?

[ChrisHaynes]

I am trying to use a combo box entry to complete the name of a field in a query. I have put this code behind a text box and I want it to look at the query and use the start of the field as 'SumOf' and then the combobox entry to be used as the text to add next to it. Then the data in this field is shown (there is only 1 record in each field). This is the code I have tried:

=qry_totalusers!["SumOf","Forms!frm_Software_Home!Combo160"]

It may be completely wrong or impossible to achieve, but can anyone give me some guidence? (or the answer!?)


Thanx in advance,
Chris.

 

[sxschech]

Try this:
=qry_totalusers!SumOf & Forms!frm_Software_Home!Combo160

Or this:
="qry_totaluser!SumOf" & Forms!frm_Software_Home!Combo160

 

[MajP]

I think.

=dlookup("SumOf " & Forms!frm_Software_Home!Combo160,"qry_totalUsers")

 

[ChrisHaynes]

Thanks for your quick reply. However, I am still getting a '#name' entry. I always seem to get this problem when linking a text box to something manually. I simply put this code into the control source. I this the correct way?


Thanx again,
Chris.

 

[MajP]

I never give guarantees on syntax, only concepts. So check all the names. I see I did "qry_totalUsers" not "qry_totalusers"

Go to the help file and look up "dlookup" so you understand the concept.

You can use the DLookup function to get the value of a particular field from a specified set of records (a domain). Use the DLookup function in Visual Basic, a macro, a query expression, or a calculated control on a form or report.

You can use the DLookup function to display the value of a field that isn't in the record source for your form or report. For example, suppose you have a form based on an Order Details table. The form displays the OrderID, ProductID, UnitPrice, Quantity, and Discount fields. However, the ProductName field is in another table, the Products table. You could use the DLookup function in a calculated control to display the ProductName on the same form.

DLookup(expr, domain, [criteria])
The DLookup function has the following arguments.

 

[ChrisHaynes]

Thanks for your help MajP. I will have a look at the DLookup function.


Thanks again.
Chris.

 

[ChrisHaynes]

Instead of trying to add the word SumOf in front of the field value in the code, I have just re-entered the values in the combo box with SumOf at the front of each word so it exactly matches the field name. It works for all the fields with no spaces between the words. How can I edit the code to work with fields with spaces? I have tried entering brackets but this hasn't worked. Can anyone help??


=DLookUp([Forms]![frm_Software_Home]![Combo160],"qry_totalUsers")


Thanks alot
Chris.

 

[PHV]

Perhaps this ?
=DLookUp("[" & [Forms]![frm_Software_Home]![Combo160] & "]","qry_totalUsers")

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