Populate form from a query

[kernowboy]

I'm trying to populate a field in my form from a query. Can someone please give me some help. I've been trying to figure it out from the web but can't get it sorted. I've got a combo box that selects a member of staff from a table, and then a query runs which takes the value from that combo to retrieve the staffs current rate of pay.

I've added this code to the on change event on the combo.


Private Sub cboStaffID_Change()
Me.txtunit.Value = [qrycurrentpay]![Unit]
End Sub

But it doesn't seem to work,

txtunit is the field I want to fill on my form, and Unit is he field name of the value I want from query qrycurrentpay.

Many thanks

Ali

 

[boblarson]

You can't refer to a query in this manner. If the field is in the form's record source you can just bind it to the field. If not, you need to use a DLOOKUP.

Me.txtunit = DLookup("Unit", "qrycurrentpay", "[EmployeeIDFieldInTableHere]=" & Me.cboStaffID

And it should be in the combo's AFTER UPDATE event NOT the CHANGE event.

 

[Bassbase]

Hi Bob,

I realise this thread is old but it is very similiar to a problem i am having and didn't want to double post!

I need to do a dlookup on a query that will copy all data from the query to the subform.

I'm stuck as to which way to go about it. i could either:
A) Set query as record source then save each field to the table in VBA or
B) Set table as record source and Dlookup each field in query (there are only 5 fields).

however this is being done in a tab and either of these options needs to be done on tab click.

Can this be done?

Should i start a new thread?

Many thanks

James