Adding a field that autoupdates from another table

[Loopyl00]

I have created a combo box

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

This successfuly returns the first name and last name, and i'm able to autofill a text box with the first name pulled from the combo box

I want to add another txt box on the form that gives me their team name. I have a FK in the Vendeurs table linked to the [teamid] in the [team table]. When I select the name in the combobox I want to autofill the team name

 

[traingamer]

There are a number of ways to this (it is Access, after all)

One way:

SELECT Vendeurs.VendeurID, Vendeurs.[Last Name], Vendeurs.[First Name], [blue]Team.TeamName[/blue] FROM Vendeurs 
[blue]Inner Join [Team Table] Team on
Vendeurs.teamid = Team.teamid[/blue]
GROUP BY Vendeurs.VendeurID, Vendeurs.[Last Name], Vendeurs.[First Name], [blue]Team.TeamName[/blue] ;

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[traingamer]

Not sure why you're using Group By (assuming that VendeurID is unique).

My solution assumes that all vendors are on teams - if that is not true use Left Join rather than Inner Join.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[Loopyl00]

really hoping this will work, with the code as you've put it
amd getting an error that the sytax of the subquery in the experssion is incorrect. check the subquery's syntax and enclose the subquery in parentheses

 

[Loopyl00]

yes vendeurID is unique and each vendor has only one team assigned

 

[SkipVought]

check the subquery's syntax and enclose the subquery in parentheses

SubQuery???

please post ALL you SQL code in the query.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Loopyl00]

Since posting yesterday afternoon, i've been working on it some more and

Have created a query [Vendeur Equipe] which has 3 fields [VendeurID] - [Equipe] - [Last Name]

in the AfterEvent update of the combobox

Private Sub LastNameC_AfterUpdate()
Me.FirstName = Me.LastNameC.Column(2)
Me.VendeurIDtxt = Me.LastNameC.Column(0)
Me.Equipetxt = DLookup("equipe", "Vendeur Equipe", Me.VendeurIDtxt)

End Sub

 

[SkipVought]

...and no unanswered questions?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Loopyl00]

If Only!


Have now put the teams into the vendeurs table to try and simplyfy things

ComboBox, no problem, pulls Last name, and have got it to afterupdate fill first name and vendeurID - HOWEVER the Equipe (team) only updates the first time i use the combo box and no amount of clicking on the refrest button is making a difference

Private Sub PickCombo_AfterUpdate()
Me.VendeurID = Me.PickCombo.Column(0)
Me.FirstName = Me.PickCombo.Column(2)
Me.Equipetxt = DLookup("Equipe", "Vendeurs", Me.VendeurID)

End Sub

 

[TheAceMan1]

How are ya Loopyl00 . . .

[blue]Have now put the teams into the vendeurs table to try and simplyfy things ...[/blue]

Its not a good idea to just move things around in your tables just to get something to work. What would be a big help here is some assemblance of your table structure. Take a [blue]PrtScr[/blue] of your relationships window, paste it into Paint, save and upload the graphic to 4Shared. 4Shared will provide you with a [blue]link[/blue] you can paste in your next post where we can all go for viewing (4Shared is Free).

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Loopyl00]

Thanks So much -

Here is the Relationships image you requested

any other screen grab you think will help is but a click away

The Equipes are in a ComboBox (Paris, Beneluxe, Marseille, Lyon,, Callcentre) so i think this is why it may be returning an error

So I tried with just a txt box - Email

Me.Emailtxt = DLookup("", "[Vendeurs]", "VendeurID =Forms![Vendeurs]![VendurID]"

this only works (no errors) if the Vendeurs tbl is open - and is not returnign the Email address i Me.Email.txt

and

Me.Emailtxt = DLookup("[Email]", "[Vendeurs]", "VendeurID" = "Me.VendeurID")

Also no errors, but returns no value

The tbl btw is [Vendeurs] not equipe and the ID is VendeurID

I have uploaded to Skydrive a zip with the dbase in it

[URL unfurl="true"]http://cid-4490afdc094900ec.skydrive.live.com/redir.aspx?resid=4490AFDC094900EC!368[/URL]

All help gratefully received

[attach_link]http://i305.photobucket.com/albums/nn235/lisa2802/Relationships.jpg[/attach_link]

 

[Loopyl00]

solved

Me.Emailtxt = DLookup("", "[Vendeurs]", "VendeurID = " & Me.VendeurID)