Dlookup problem

H

Harry Bo

Hi all,

I have a table :
TblStoreCodes
Field Store--unique entries
Field Code--some duplicates

I have a form:
FrmTransactions
field Store--which has a cbo that that is the field Store in TblStoreCodes
Field Code -- which I would like to populate automatically when the user
picks a Store from the store field in Frmtransactions.

The user will alway know what the store is but not the code.

What is the simplest way to do this?

Thanks

Harry
 
D

Douglas J. Steele

If there are duplicates, then unless it doesn't matter which code is
selected, the most appropriate approach would be to have a second combo box
that's populated with the codes associated with the store and let the user
select from that list.
 
H

Harry Bo

Thanks Doug,

The user doesn't know what the actual store code is, and I need to isolate
the code for query purposes.

Harry
 
T

tina

in tblStoreCodes, each store is listed only once? so each store has only one
code? but more than one store may have the same code? as in

Store Code
A 1
B 2
C 2
D 3
E 1

does a store's code change from time to time? if it does not, then you
probably shouldn't save duplicate data (the code) in two places -
tblStoreCodes and tblTransactions. if the code does change, and you need to
record the code in use at the time of the transaction, then suggest you do
the following:

in the combo box on frmTransactions, add Code as an additional column. you
can hide the column from your user by setting it's width to 0 (zero), but
make sure you change the ColumnCount to reflect the increase in total
columns.
then set the combo box's AfterUpdate event to

Me!NameOfCodeControl = Me!CboStore.Column(x)

substitute the correct control names, of course. and replace the "x" with
the column number of the code column. remember that combo box columns are
zero-based, so column one would be (0), column two would be (1), etc.

hth
 
H

Harry Bo

Thanks Tina,

TblStoreCodes
Fields
Store
tina said:
in tblStoreCodes, each store is listed only once? so each store has only one
code? but more than one store may have the same code? as in

Store Code
A 1
B 2
C 2
D 3
E 1

does a store's code change from time to time? if it does not, then you
probably shouldn't save duplicate data (the code) in two places -
tblStoreCodes and tblTransactions. if the code does change, and you need to
record the code in use at the time of the transaction, then suggest you do
the following:

in the combo box on frmTransactions, add Code as an additional column. you
can hide the column from your user by setting it's width to 0 (zero), but
make sure you change the ColumnCount to reflect the increase in total
columns.
then set the combo box's AfterUpdate event to

Me!NameOfCodeControl = Me!CboStore.Column(x)

substitute the correct control names, of course. and replace the "x" with
the column number of the code column. remember that combo box columns are
zero-based, so column one would be (0), column two would be (1), etc.

hth
Click to expand...
 
H

Harry Bo

sorry hit the enter button too soon

Field

Store Code
FWG Dist
DRT DC
GRT DC
HRT Industrial
JHL Dist

Eachstore is only listed once but the code is like a cost centre and would
have multipies. There are 268 stores and 36 codes.

Harry
 
T

tina

okay, your setup is the same as what i guessed at. so do you have a question
re my suggestion? or did you try it and it didn't work for you?
 
Top