Dlookup

[fenhow]

Hi,

I have a form that has a ddl which the user can select a name from another table called Vendors. The vendors table has Name, Tel, Address.

On my form I have an area for Vendor (which is the ddl), Name, Tel and Address.

When I select the Vendor from the DDL I am trying to populate the other fields automaticlly using Dlookup.

Two things are happening, first I get an Error "Syntax Error" missing operator and sometimes if it works the record will not save.

Here is a line of the Dlookup code I am using on the "On Change Event" of the ddl.

Me.VendorPhone = DLookup("vPhone", "Vendors", "VendorPhone = '" & Me.VendorPhone & "'")

Any help would be greatly appreciated.

Fen How

 

[pbaldy]

Well, you're trying to set the same field you're looking up, which I assume isn't what you want. That said, I wouldn't use DLookup at all. Make sure all 3 fields are in the rowsource of the combo, then in its after update event:

Me.AddressField = Me.ComboName.Column(x)

where x is the column containing the value (it is zero based).

 

[fenhow]

Thanks, that looks like a far better way to do it, i tried it but the field does not pouplate, do i need the () around the Column?

Vendor is the name of the ddl and VendorContact is the name of the text box on the form.

Private Sub Vendor_AfterUpdate()
Me.VendorContact = Me.Vendor.Column(10)
End Sub

 

[fenhow]

Sorry, i got it, had my col numbers wrong.. cant add thanks for taking the time. I really apprecaite it.

Fen

 

[pbaldy]

No problemo, Fen.