VBA Issue

[MikeDBMan]

What is wrong with the select statement below. I can't get this to work.


I have a table named "Billing People"
It has fields: BillingPhone and Billing People.
The field I just left is Billing Responsibility. I wish to lookup the data from the billing people table and populate the phone number in the current table. Thanks for any help...

Private Sub Billing_Responsibility_AfterUpdate()
Dim ThePerson As String
Dim Ans As Variant
Dim Txt As String
Dim Expr1 As String
Let ThePerson = Me.Billing_Responsibility
Dim Rec As Recordset
Let Txt = "SELECT [Billing_People.BillingPhone] as Expr1 FROM [Billing_People] where [Billing_People.Billing_Responsibility] =" & "'" & ThePerson & "'"
Let Ans = DLookup("[BillingPhone]", "Billing_People", Txt)
Me.BillingPhone = Ans

[pbaldy]

Is this what you want?

http://www.baldyweb.com/Autofill.htm

To answer your question, the criteria argument of the DLookup() just wants the WHERE clause, without the word WHERE. You don't want the Let keywords either. Just

Ans = DLookup(...)

[MikeDBMan]

The code you provided works in that I get no errors but I always get the first record's data regardless of what I select in the field Billing Responsibility. Suggestions why this would be?

[pbaldy]

What is the rowsource of the combo, and its bound column?

[MikeDBMan]

Row Source is Billing Responsibility and the bound Column is 1.

[pbaldy]

Can you post the db, or a representative sample?

[MikeDBMan]

I will have to strip some of the data from it. Let me see...

[MikeDBMan]

I have a file ready to upload. How do I go about doing that? I will see what I can find but I don't currently know how to do it.

[pbaldy]

There's a button to attach in the Post Reply or Go Advanced area.

[MikeDBMan]

I can't get the size less than 500K so I can't get it uploadable.