Dlookup returns AutoNo

[Frustrated..!]

Hihi, I have been trying for days.
I'm using dlookup but is returns the autonumber of combo box.

I have simplify it and here is how it goes,

I have 2 table name [tbl_Process] & [tbl_Stations]

[tbl_Stations]
Auto= AutoNumber(primary)
Stations=Text

[tbl_Process]
Auto=AutoNumber(primary)
Model=Text
Stations=ComboBox (tbl_Stations,BoundCol=1,ColCount=2,ColWidth=0,1)
Seq=Integer

In my form,

Private Sub Command0_Click()
MsgBox DLookup("[Stations]", "[tbl_Process]", "[Model]=""A"" AND Seq=3")
End Sub

it keeps returning the value of [tbl_Stations]'s Autonumber..?

pls help..

 

[John W. Vinson]

Hihi, I have been trying for days.
I'm using dlookup but is returns the autonumber of combo box.

I have simplify it and here is how it goes,

I have 2 table name [tbl_Process] & [tbl_Stations]

[tbl_Stations]
Auto= AutoNumber(primary)
Stations=Text

[tbl_Process]
Auto=AutoNumber(primary)
Model=Text
Stations=ComboBox (tbl_Stations,BoundCol=1,ColCount=2,ColWidth=0,1)
Seq=Integer

In my form,

Private Sub Command0_Click()
MsgBox DLookup("[Stations]", "[tbl_Process]", "[Model]=""A"" AND Seq=3")
End Sub

it keeps returning the value of [tbl_Stations]'s Autonumber..?

pls help..

Exactly. That's what the field contains.

You're yet another victim of the misdesigned, misleading, infuriating Lookup
Wizard misfeature. tblProcess *APPEARS* to contain a station name. It doesn't.
It contains a numeric ID, a foreign key to tblStations; this fact is concealed
from your view by the combo box. When you use DLookUp it's looking up what's
actually there (the ID number that you cannot see).

Create a Query joining tbl_Process to tbl_Stations by Station_ID, and look up
tbl_Stations.Stations from it, using criteria on the tbl_Process fields.

 

[tina]

it keeps returning the value of [tbl_Stations]'s Autonumber..?

that's correct. the primary key from tbl_Stations is the value that is being
stored in the Stations field in tbl_Process. by using a Lookup field in
tbl_Process, that fact is being hidden from you; that's one of several
reasons why many experienced Access developers never, ever, ever use Lookup
fields in tables. for more information, see
http://www.mvps.org/access/lookupfields.htm.

to see the station name rather than the primary key, from your code, you'll
need to get the primary key value first - which you're already doing - and
then use that to look up the name in tbl_Stations, as

Private Sub Command0_Click()

Dim lng as Long

lng = DLookup("[Stations]", "[tbl_Process]", "[Model]='A' AND Seq=3")
MsgBox DLookup("Stations", "tbl_Stations","Auto = " & lng)

End Sub

hth

 

[Wilfred]

thanks..!
and thanks again for the prompt reply
I will go and try

 

[M Skabialka]

Whenever I am asked to modify an older database that has lookup fields the
first thing I do is add a new column and put the actual information into it,
change all the forms, queries and reports and banish the lookup field from
the database. I'm glad the experts feel the same way about them - I just
found them annoying and frustrating! Even reports created by the reports
wizard showed combo boxes in design mode!
Mich

Hihi, I have been trying for days.
I'm using dlookup but is returns the autonumber of combo box.

I have simplify it and here is how it goes,

I have 2 table name [tbl_Process] & [tbl_Stations]

[tbl_Stations]
Auto= AutoNumber(primary)
Stations=Text

[tbl_Process]
Auto=AutoNumber(primary)
Model=Text
Stations=ComboBox (tbl_Stations,BoundCol=1,ColCount=2,ColWidth=0,1)
Seq=Integer

In my form,

Private Sub Command0_Click()
MsgBox DLookup("[Stations]", "[tbl_Process]", "[Model]=""A"" AND Seq=3")
End Sub

it keeps returning the value of [tbl_Stations]'s Autonumber..?

pls help..

Exactly. That's what the field contains.

You're yet another victim of the misdesigned, misleading, infuriating
Lookup
Wizard misfeature. tblProcess *APPEARS* to contain a station name. It
doesn't.
It contains a numeric ID, a foreign key to tblStations; this fact is
concealed
from your view by the combo box. When you use DLookUp it's looking up
what's
actually there (the ID number that you cannot see).

Create a Query joining tbl_Process to tbl_Stations by Station_ID, and look
up
tbl_Stations.Stations from it, using criteria on the tbl_Process fields.