Combo box in table via query - not sure how to!

[Fleur]

Hi
I am a complete beginner to Access and have muddled my way through fairly well so far! I have a table which I want to use to store data separate to other tables. I am pulling the names and addresses of the clients from a separate query which is fine and have done. The problem I have however is that the combo box data does not all populate the table field. For example, the clients name field is showing the drop down list of all clients from the query including address and postcode (total 8 columns). However, when I have found the right client and clicked on it, only column one data (title) shows in the table. From looking around the net, I am guessing I need to alter the row source by adding something along the lines of SELECT DISTINCTROW but do not have the foggiest with this! Can anyone help please? Be most grateful. Thanks!

 

[HiTechCoach]

It is working correctly for tables. I find the lookup field in tables to be very evel and only cause grief. I really think this feature should be removed ... well maybe not... I get a lot of work from this feature causing people so much grief.

See: The Evils of Lookup Fields in Tables


IMHO, you need to create a form for data entry. I never use tables or queries directly for data entry.


Have you looked at the sample Northwind database?

 

[Fleur]

Hi, not sure what you mean in that it's evil - how else can I filter through the data? I most certainly don't want to reinput it all! Wouldn't I still need to build the same sort of thing if I were using a form anyway?

 

[Insane_ai]

1. The rowsource property on the lookup tab can invoke a query builder environment for you. Right click the space where your table / query is shown in this environment, select properties and change the Unique values property to Yes. (Alternate: add DISTINCT after SELECT in teh SQL statement)

 

[WIS]

Hi
I am a complete beginner to Access and have muddled my way through fairly well so far! I have a table which I want to use to store data separate to other tables. I am pulling the names and addresses of the clients from a separate query which is fine and have done. The problem I have however is that the combo box data does not all populate the table field. For example, the clients name field is showing the drop down list of all clients from the query including address and postcode (total 8 columns). However, when I have found the right client and clicked on it, only column one data (title) shows in the table. From looking around the net, I am guessing I need to alter the row source by adding something along the lines of SELECT DISTINCTROW but do not have the foggiest with this! Can anyone help please? Be most grateful. Thanks!

I think you need something like the following:

AfterUpdate event:
Fld1 = me.cboYourName.YourControlname.Column(0)
Fld2 = me.cboYourName.YourControlname.Column(1)
etc. for the 8 columns(flds) you wish to add.