469,338 Members | 8,181 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,338 developers. It's quick & easy.

How do you autofill a field from another table?

30
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
Jan 11 '11 #1

✓ answered by Lisa B

Solved

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

6 15231
beacon
579 512MB
Change your SELECT query to pull the teamid from the table and use a WHERE clause to identify the foreign key.

The psuedocode would be:

Expand|Select|Wrap|Line Numbers
  1. SELECT teamid FROM teamtable WHERE vendoridfk = 7
  2.  
Jan 11 '11 #2
Lisa B
30
Thank you but i think yo've miss understood, the select query is the successfull query for the combo box

It looks to me( and please correct me if 'm wrong) that your select statement will only return for vendor whose ID is 7 - not return the name of the team in a seperate txt box as per the results of the combox
Jan 11 '11 #3
beacon
579 512MB
It selects the teamid that is associated with a vendoridfk of 7.

If you're tables/relationships are setup the way you I'm imagining based on the info provided, if the vendorid in the first combobox is 7, then the vendoridfk of 7 in the teamtable should grab the teamid associated with vendoridfk 7 and populate the combobox with the team name.

What you're doing is creating cascading combo boxes. One of the other contributors to the forum, Rabbit, created a tutorial that you may find helpful. The link is http://bytes.com/topic/access/insigh...mbo-list-boxes.

Try this on for size and let me know if you get stuck.

beacon
Jan 11 '11 #4
Lisa B
30
Hi I looked at the tutorial you posted and tried to adapt your code - The Me.Equipetxt only updates for the first selection made in the combo table and the code for the Me.Emailtxt doesn't work

I've read that I may need a string, but honestly don't know where to start to input the info for this

All help very gratefully received

I have a combo box
SELECT [Vendeurs].[VendeurID], [Vendeurs].[Last Name], [Vendeurs].[First Name] FROM Vendeurs ORDER BY [VendeurID];

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

With Me.Emailtxt
.RowSource = "SELECT [email] " & _
"FROM TblVendeurs " & _
"WHERE [VendeurID]= Me.VendeurID"

End With

End Sub
Jan 14 '11 #5
Lisa B
30
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("[email]", "[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

http://cid-4490afdc094900ec.skydrive.live.com/redir.aspx?resid=4490AFDC094900EC!368
Jan 15 '11 #6
Lisa B
30
Solved

Me.Emailtxt = DLookup("[email]", "[Vendeurs]", "VendeurID = " & Me.VendeurID)
Jan 15 '11 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by Marco Simone | last post: by
2 posts views Thread by cazz | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.