Simple design problem (i hope)

[Eric6]

hello

i'm building a form to add budget entries in a table

my problem is that i need to figure out a way to
make a textfield display results from a query based on the employee that is selected in the same form...
(this query cannot be the form's control source)

i think i can best "explain" this with an SQL statement

Select query.totalpay from query where query.employee = form.employee

this field is calculated on the spot in a manner of speaking
(maybe there is a better way of doing this without the query?)

but i can't figure out how to make the textfields display values that come from a source other than the form's control source
i tried to copy and paste the SQL code from the query builder but it didn't work
i always get a #Name? "error" in the textfield...

also
i'm having a similar problem when, in the same form, i want to display the employee's information which is located in a different table than the forms control source...
(by the way, the form's control source has a "relationship" with the employee info table. The form is the child (contains the FK) of the employee details (which contains the PK).. not sure if this is relavent)

to re-itterate,
i mainly need to know how to make a textfield that has a different source than the form's control source

i would greatly appreciate any help you could give me
the help files are of very little assitance
thank you
Eric

 

[Dor100]

Sounds like you may need DLookup for both problems. I just read Help info on it, including Examples, and it's great. Always avoided it like the plague, although I've written thousands of lines of code.

If it's a go, you want to make sure the query or DLookup criteria is based on current updated employee data, i.e., employee field changes have been saved.

 

[Eric6]

i tried dlookup
in the access design view (placing the code in the textbox)
and i tried in VBA

but either way, it still doesn't work
in access, i get a #Name? error
and in vba
i get 3 "Object Required" messages

here is the code i have in the "Form_Current" sub

Me.ACost.Value = DLookup(queries![accumulatedcosts]![SumOfCnslt_month_cost], queries![accumulatedcosts], "queries![accumulatedcosts]![Cnslt_name] = Me.Cnslt_name&quot

i dont really know how to access a query in vba so that might be it
or it might be something about recordsets...
but i dont really know, i never used them...

in short
i'm stumped once more,
please help if you can
thank you

 

[Maquis]

Did you look at the Access help entry for Dlookup? I don't think you have the syntax correct.

Shorten it to:

Me.ACost = DLookup("SumOfCnslt_month_cost","accumulatedcosts" "Cnslt_name = '" & Me.Cnslt_name & "'&quot


(Also, if all you really want to do is sum a few records then you may be able to use a DSum instead)
Maq
<insert witty signature here>

 

[Eric6]

awesome!
thanks!
i always forget those single quotes for some reason
great job!
i've got my sum and my employee info working now!!

thank you
Eric