Dlookup column on a form from a Combo Box

[Loopyl00]

I am trying to lookup the second column in a combo box to auto fill a form using the AfterEvent Update

Me.VendeurID = Me.PickCombo.Column(0)
Me.FirstName = Me.PickCombo.Column(2)
Me.Emailtxt = DLookup("", "[Vendeurs]", "VendeurID = " & Me.VendeurID)
Me.Equipetxt = DLookup("[equipe]", "[Vendeurs]", "VendeurID = " & Me.VendeurID)
Me.Phonetxt = DLookup("[business phone]", "[Vendeurs]", "VendeurID = " & Me.VendeurID)
Me.mobtxt = DLookup("[mobile phone]", "[Vendeurs]", "VendeurID = " & Me.VendeurID)
Me.notestxt = DLookup("[notes]", "[Vendeurs]", "VendeurID = " & Me.VendeurID)

The Me.Equipetxt is the problem. The Equipe Field on the Vendurs form is a combo box (from another table)

Equipe Table
Primary Key - auto Number
Equipe - Txt

No problem linking this into the Vendeurs table

The form however for the Me.Equipetxt box is using hte primary key, how can I get it to select the column(2) From the Equipe Field on the Vendeurs tbl?

 

[dhookom]

Are you actually attempting to store values that can be looked up? For instance, is a vender email address stored in only one table and one record or are you storing it in multiple records and multiple tables?

Regarding the Equipetxt, you can use a query name rather than a table name in the domain of the DLookup().

Duane
Hook'D on Access
MS Access MVP

 

[Loopyl00]

All the lookups are fine apart from the Equipetxt

I think it has something to do with the fact that in the Vendeur Table the Equipe Field is Looking up from The Equipe Table,

SELECT Equipe.ID, Equipe.Equipe FROM Equipe;

With Column width 0" to hide the Primary key from the Equipe Table - so this is why I keep getting only the primary key showing when i try and call it elsewhere

I have created a query which shows successfully the VendeurID and the Equipe called [Vendeur_Equipe]

SELECT Vendeurs.VendeurID, Vendeurs.Equipe
FROM Vendeurs;

So the new lookup is

Me.Equipetxt = DLookup("equipe", "Vendeur_Equipe", "VendeurID = " & Me.VendeurID)

Still only Primary key from Equipe table showing

 

[dhookom]

Your experience is a good reason why lookup fields are considered evil.

You query would need to contain the Equipe in order to display the "Equipe - Txt" field.

You never answered my question about storing the values in another table.

Duane
Hook'D on Access
MS Access MVP

 

[Loopyl00]

Regarding my tables - no - I have no duplicate information in tables

The information is only stored once using FK's and in the case of the Vendeur Equipe a lookup in the from Equipetbl into Vendeurtbl

 

[Loopyl00]

Thank you btw- i needed to delete the relationship between the Vendeur tbl and the Equipe tbl then amended my query

it works

 

[dhookom]

Is your issue resolved? If your information is only stored once then I don't understand why you need any code. For instance, the control source of the first name text box should be:

  =PickCombo.Column(2)

Displaying the values requires no code.

Duane
Hook'D on Access
MS Access MVP

 

[Loopyl00]

Yes my issue is resolved thank you

Picking the Equipe from the Combo box didn't seem to work when i tried it - it was just displaying the Primary key again for the Equipe - I believe as the Lookkup in the Vendeurs table for this field is the two columns EquipeID, Equipe with Column width set to 0

SELECT Vendeurs.VendeurID, Vendeurs.[Last Name], Vendeurs.[First Name], Vendeurs.Equipe FROM Vendeurs ORDER BY Vendeurs.VendeurID;

 

[dhookom]

It's good to hear your initial issue is resolved. I still question your table structures and use of code but that's my issue.

Duane
Hook'D on Access
MS Access MVP

 

[Loopyl00]

You'll be pleased to hear that I've reworked it all so that I'm not using a lookup in my vendeurs table any more but just a relationship between the Vendeurs tbl and Equipe tbl - it is all as it should be, nice and tidy