Object Required

[Guest]

I am an Access novice that is trying to populate fields in a form with data
from two other tables within the same database. The code I have written
keeps generating an "object required" error message. Below is the code,
followed by table/form info. Can anyone let me know what I am doing
incorrectly?

Private Sub ProjectNum_AfterUpdate()
Dim varPM As Variant
Dim varCustomer As Variant
Dim varIndication As Variant
Dim varSubject As Variant

varPM = DLookup("[PM]", "Project_Information", "[ProjectNum] = '" &
Tables![Project_Information].[Project#] & "'")
varCustomer = DLookup("[Customer]", "Project_Information", "[ProjectNum]
= '" & Tables![Project_Information].[Project#] & "'")
varIndication = DLookup("[Indication]", "Project_Information",
"[ProjectNum] = '" & Tables![Project_Information].[Project#] & "'")
varSubject = DLookup("[Subject/Teaser]", "CreativeInfo", "[ProjectNum] =
'" & Tables![CreativeInfo].[Project#] & "'")

If (Not IsNull(varPM)) Then Me![ProjectMgr] = varPM
If (Not IsNull(varCustomer)) Then Me![Client] = varCustomer
If (Not IsNull(varIndication)) Then Me![Study] = varIndication
If (Not IsNull(varSubject)) Then Me![SubjectLine] = varSubject
End Sub

Trying to populate a form named "Projects2" by typing a code into the
"ProjectNum" field and having the "ProjectMgr", "Client", "Study", and
"SubjectLine" fields update with info from two tables:

1) Table named "Project_Info"
where "ProjectNum" from "Projects2" form = "Project#"
using fields "Indication" = "Study" in "Projects2"
"Customer" = "Client" in "Projects2"
"PM" = "ProjectMgr" in "Projects2"
2) Table named "CreativeInfo"
where "ProjectNum from "Projects2" form = "Project#"
using field "Subject/Teaser" = "SubjectLine" in "Projects2"

Thanks!

 

[Guest]

This line is incorrect.
varPM = DLookup("[PM]", "Project_Information", "[ProjectNum] = '" &
Tables![Project_Information].[Project#] & "'")

The reference to Tables![Project_Information].[Project#] & "'") will not
work. You need to have the value you want to look up in a variable or a form
control that can provide a specific value.

Are you trying to use an unbound form? Why? There are occasions where it
is appropriate, but in most cases a bound form based on a table or query will
work much better.

To be able to address fields within a table or query, you have to use a
Recordset, you cannot address them directly.