How to automatically fill-in a field based off of a previous field in Access 2007

[jond]

Database Details:
I have 2 tables; "tblContracts" and "tblVendorInfo" and a form ("frmVendorInfo) created off of the table "tblVendorInfo". I have fields including: "PO Number" (Primary Key), "Vendor", "Street", and "Zip" in the table "tblContracts". All the PO Numbers, Vendors, etc. are stored in the table "tblContracts".

My Question:
I need to know how to automatically fill-in the "Vendor", "Street", and "Zip" fields once a "PO Number" is selected from a combo box on the form. Does this have to be done by a dlookup? Or can it be easier than that?

What I've been trying:
I have attempted to research this and I came up with some code such as:

Private Sub PONumber_AfterUpdate()
 
    Vendor = DLookup("Vendor", "tblContracts", "PO Number =" & PONumber)
 
End Sub

I'm getting Run-time error '3075'
Syntax error (missing operator) in
Query expression 'PO Number=WWV11858'

I am a beginner and any very simplified help would be much appreciated. Let me know if I'm on the right track or I should be doing something totally different. Thanks!!

Jon

[VijaySofist]

Hi John,

Please Dont use Spaces while creating Fields. Any way Try Giving Field names in between Square Brackets.
Please Try the Following Code

Private Sub PONumber_AfterUpdate()
Vendor = DLookup("[Vendor]", "tblContracts", "[PO Number] =" & PONumber)
End Sub
 

You Can Find the Syntax & Example in below

Syntax
DLookup(expr, domain [, criteria] )

Argument Description
expr Required. An expression that identifies the field whose value you want to return. It can be a string expression (string expression: An expression that evaluates to a sequence of contiguous characters. Elements of the expression can be: functions that return a string or a string Variant (VarType 8); a string literal, constant, variable, or Variant.) identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.
domain Required. A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter.
criteria Optional. A string expression used to restrict the range of data on which the DLookup function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DLookup function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise, the DLookup function returns a Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain a Null value.).

Example

Dim intSearch As Integer
Dim varX As Variant
 
intSearch = 1
varX = DLookup("[CompanyName]", "Shippers", _
    "[ShipperID] = " & intSearch)
 

All the Best
Regards
Vijay.R

[NeoPa]

Jon, your syntax is wrong because, although you didn't mention it in the question, it seems that [PO Number] is a text field. For text literals you need quotes (See Quotes (') and Double-Quotes (") - Where and When to use them). Your code would need to look more like :

Vendor = DLookup("[Vendor]", _
                 "[tblContracts]", _
                 "[PO Number] ='" & Me.PONumber & "'")

All that being said, I suspect you're going about this the wrong way anyway. Check out Cascading Combo/List Boxes for a more straightforward approach.