Surely this is Simple? 1

[Dannybe2]

I'm having problems with something that I think is quite simple, I just can't get it to work, this is really frustrating me, and I would really appreciate some help.

I have two tables:

Company & Contact

I have two forms:

Company and Contact

The company form includes the company name and the telephone number.

The contact form also includes these fields and after selecting the company name, I want the database to look up the telephone number.

This is the code I am using:

Private Sub AfterUpdate_CompanyName()

CompanyTelephoneNumber = Forms![Company]!CompanyTelephoneNumber
Department.SetFocus

End Sub

 

[jebry]

Hi!

The code you want in there is:

DLookUp("CompanyTelephoneNumber", "Company", "CompanyName = " & Me.CompanyName.Value)

In general DLookUp goes like this:

DLookUp("YourFieldName", "YourTableOrQuery", "YourCriteria&quot

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Dannybe2]

Thanks Jeff, I was considering using a dlookup, but wasn't sure about the criteria section.

One thing I discovered just now is that under the options for designing a table is the lookup wizard - which allows me to select which field in another table I want to be the source, would this also work?

Dan

 

[Dannybe2]

I'm still having problems with that:

I'm using the following code:

Me.CompanyTelephoneNumber = DLookUp("CompanyTelephoneNumber", "Company Book", "Company = " & Me.Company.Value"

I get an error with the criteria which doesn't appear to be right. The helpful tooltip tells me the correct me.company.value as does the immediate window. Shouldn't it be "CompanyID = " & Me.Company.Value" ?

Thanks again, Dan

 

[jebry]

Hi Dan!

Your criteria should look like this:

"Company = '" & Me.Company.Value & "'"

That is assuming the Company is text.

hth

Jeff Bridgham
bridgham@purdue.edu