Solved: combo box question doing a look up in a table

[jaureguima]

I am trying to create a code to where If I choose a name in "label0" it will automaticly look up this value from a table called store list (which I have done by making it a combo box) once this is done I want the value in "label1" to selfgenerate from the "store list" table under the column named "phone". In other words when I choose a store I automaticly want the number to show up with the corresponding store.

Store list contains two columns "store" and "phone". All of this information is not going to be stored in the "store list" table that is just were I choose the values from

Thanks :banghead:

[OBP]

Hello, the easiest way to do this is to recreate your Combo box and add the phone number as the second column, with the store name in the first column which is "bound" to the label0 (which I hope is a text box not a label)
Then in the Combo boxes "After Update" event procedure you place a simple bit of VBA which transfers the phone numer in to label1.
The VBA code would be like this
me.label1 = me.combo0.column(1)
Your combo may have a different number to 0 though.
If you want me to create it for you can you post a zipped copy of the database on here, it can have all the data removed if necessary.

[jaureguima]

:friends: Here is the DB. What I wanted to do is have Corp and Region Self automate Once I choose the store. Also if possible I wanted the Tracking number to self generate to the next number form the Master Table. Thanks I appreciate all of your help

[OBP]

I can't see any Master table Tracking number, so I will leave that part for now and get your "Store" selection working.
OK found the Master table, all the tables other that the store list were hiding LOL.

[jaureguima]

LOL if you look at the form you will see that there is a tracking number this will update the tracking number in the Master Table. All of the other list are just so that I can make it easy for the user to figure out who they want to use when creating a new entry in the master table...lol sound funny but it is better than the 15 different excel spreadsheets they are using...

[OBP]

Ok. here is phase 1, the Combo working. I have added a select Stores Query that the combo gets it's data from. It has the store names sorted in alphabetical order. The combo now puts the Corp and Region in as well as the Store Name.
I will have a look at the Master Tracking number.
It may have to be tomorrow as it is getting late here in the UK.

[OBP]

There isn't any data in the master table, what form does your tracking number take?
The table hasn't got a key field which it should have, is it going to be the tracking number and are you going to use an autonumber for it?
If so you don't need to get the value from the table as Access will do it for you.

[jaureguima]

it is going to be a number that I will input into the master talbe. So say if I use tracking number 4231 next time somebody creates a new record it wil be 4232. It does not look up a number in any paticular table. I am hoping that there is a way to scan the master table for the latest number. Sorry if I don't make sense:doh:

[OBP]

Why not use an autonumber, you do not have to do anything with that?
I can get the last number from the table with no problem, but is it worth it?

[jaureguima]

Sorry about the last I see the db now. the Number would be worth it because evently different users will have a different set of numbers I am just trying to create the basic to get them up and running.

Example Jon's will start with 1000
jim 2000
joe 3000
and so on but that is a little down the future.

DB is great!!!!!!!! thanks

[OBP]

If you want to use seperate ranges for different users you will have to have security set up with user log-on so that the VBA can know who to look for in the table to get the correct number, it can be done because I have already done it for Different Departments, although they all started at zero but were prefixed by a dept. code.

[OBP]

Here is phase 2, with the tracking number looked up and incremented.
It uses a query and form called Last Tracking Number.
The VBA code is in the Form's On Current event procedure, so it appears as soon as the form is opened.

[OBP]

Here is phase 3, it has a check that makes sure someone has entered some data in the Store field before closing the form. If this isn't done and they close the form you will get an empty record with just the Tracking number in it.

[jaureguima]

Thanks I really appreciate it!!!!!!!!!