DLookup within a sub-form (1 Viewer)

[wllsth]

I have the following piece of VBA code which is called AfterUpdate

Me.Text62 = DLookup("[Job Desc]", "Projects Extended", "[ProjectID]=" & Nz(Me![Project], 0))

This retrieves Job Desc and puts it in field Text62 on my sub-form. However, when I enter another record with a different ProjectID it displays the new Job Desc for every line of the sub-form.

What am I doing wrong ??

 

[JANR]

If text62 is an unbound control in your subform this is what happens if your subform is a continous form or datasheet. You have to re-think what your goal is with this VBA sub.


JR

 

[wllsth]

You're right I think I've sorted it but I am getting a strange error now
'The object doesnt contain the Automation Object "Header" '
Not sure where this is coming from

 

[wllsth]

I am using the following bit of VBA code now to retrieve 'Job Desc' onto a line within my sub-form. It retreives it ok but I get the following error message ;-
The object doesnt contain the Automation object 'Header'

Private Sub Project_AfterUpdate()
Me.Job_Desc = DLookup("[Job Desc]", "Projects Extended", "[ProjectID]=" & Nz([Project], 0))
End Sub

Any thoughts !!

 

[JANR]

Looks like there could be someting wrong with a field-reference or maybe the link properties for your subform. google : object doesnt contain the Automation object for some possible answers.

JR

 

[Pat Hartman]

Get rid of the DLookup() and replace the form's RecordSource with a query that joins the main table to the lookup table. That way you will be able to create a bound field with the lookup value. That will allow it to be different for each row.