Dlookup sytax and punctuation

[The_Doc_Man]

If you return the same row every time, there is something wrong with the table OR there is something wrong with the criteria of the DLookup. You must pick a criteria field in the DLookup that will let you find the area code you want. The value you are feeding has to correspond to the correct field in the table. But this statement needs clarification:

"arcode is a control on the form,"

It is immaterial as to what is in "me.arcode" because that doesn't directly participate in the operation shown in post #15. You copy it but don't actually use it. What you really want is that [areacodetbl].[arcode] must exist as a valid field for the lookup. Then it must also occur that the record in the [areacodetbl] that has [arcode] of 512 has the correct value in [areacodetbl].[Region].

 

[June7]

No, the field in the table is Region, and I don't reference the form explicitly.

You said arcode is a textbox on form.

Region is the field you want to return a value from. You reference arcode in the search criteria. Is arcode a field in areacodetbl? If not, then what field holds the area code?

Did you read the rest of post 17?

Time to provide db for analysis? Follow instructions at bottom of my post.

 

[Gasman]

Any field that is included in criteria must also be a field in domain

That would be correct, you cannot search for a field that does not exist?

However you are returning Region. What is the name of the field that has the area code in it?

We are 24+ posts in here, for a simple DlookUp

Show the fields of the AreaCodetbl in a picture

 

[jpl458]

Late last nite I went back to the beginning, I reloaded the area code table and cleaned up the initial part of the code. The data used is as follow:

Table: AreaCodTable that has two 3 columns I use
AreaCode Short Text
Region Short Text examp.: IL, TX, MI
Description Short Text Contains cities in the associated area code

Form: Master Entry which contains the following text boxes:
callingnumber
arcode unbound
st unbound
callorigin unbound

Here is the code from the Lost Focus event of callingnumber textbox

 arcode = Mid(callingnumber, 1, 3) 'Extract the area code from the phone number and put in arcode txtbox on from
 Me.st = DLookup("Region", "AreaCodeTable", "arcode = '" & arcode & "'") 'Put Region(state) into textbox named st[
/CODE]

In the DLookup it's the criteria that I have no clue as to its construction.  I want to get the value in the region col of the AreaCodeTable that corresponds to the area code that is in callingnumber. the criteria "arcode = '" & arcode & "'" is wrong, and the last of my abortive attempts try and make it wotk. After a lot of reading and trying it still escapes me.

Thanks all for your time and responses,

 

[Gasman]

You still have not shown the fields in table AreaCodeTable?

 

[jpl458]

You still have not shown the fields in table AreaCodeTable?

Where have I not shown them? In the criteria? Can you be more specific, please.

 

[Gasman]

Show us the structure of the AreaCodeTable

Like this

 

[cheekybuddha]

Is the table called 'areacodetbl' or 'AreaCodeTable' ?

 

[jpl458]

Finally, I figured it out, and here is the code that works:

Private Sub callingnumber_LostFocus()
   arcode = Mid(callingnumber, 1, 3) 'Extract the area code from the phone number and put in arcode txtbox on form
   Me.st = DLookup("Region", "AreaCodeTable", "AreaCode = '" & arcode & "'") 'Put Region(state) into textbox named st
   Me.callorigin = DLookup("Description", "AreaCodeTable", "AreaCode = '" & arcode & "'") 'Put description into callorigin
 
End Sub

Your last reply pointed me to the solution, Thanks

 

[cheekybuddha]

You really need to add Option Explicit to the top of every code module.

And then go to the VBA options and tick 'Require Variable Declaration' checkbox.

 

[Gasman]

Finally, I figured it out, and here is the code that works:

Private Sub callingnumber_LostFocus()
   arcode = Mid(callingnumber, 1, 3) 'Extract the area code from the phone number and put in arcode txtbox on form
   Me.st = DLookup("Region", "AreaCodeTable", "AreaCode = '" & arcode & "'") 'Put Region(state) into textbox named st
   Me.callorigin = DLookup("Description", "AreaCodeTable", "AreaCode = '" & arcode & "'") 'Put description into callorigin

End Sub

Your last reply pointed me to the solution, Thanks

Yes, you have to use fields that exist It told you that in the DLookup() from MS, which I also quoted from your post?
I wanted to see a picture as proof, not just take what you typed as gospel.
So all along it has just been because you have not used the correct field name.

 

[jpl458]

You really need to add Option Explicit to the top of every code module.

And then go to the VBA options and tick 'Require Variable Declaration' checkbox.

Where are the VBA options located?

 

[cheekybuddha]

In the VBA editor menu:

Tools -> Options

 

[Gasman]

In the vba window. Tools/Options

 

[cheekybuddha]

Requiring variable declaration will automatically add Option Explicit to every newly created code module.

However, you must add it manually to any already existing code module.

Once you have done that, in the VBA editor menu choose:

Debug -> Compile

It will highlight the first syntax error it finds in your code.

Correct the error, and repeat the process until no more errors are displayed