Problem with combo box in a select query with a join

[cravincreeks]

Problem with combo box in a select query with a join

Hello all. I have a table with a text field that is restricted to a value list via a combo box (I specified the “look up wizard”, chose value list, and entered two the two options that the user can choose from). When I open the table in the table view, I can choose a value from the combo box and the value get stored in the table. When I create a select query without joining this table to another, I can choose a value combo box without any complaint. But when I join another table and attempt to edit the combo box field in the ‘table view’ of the select query, it won’t allow me to do so – I get the error ‘ding’ sound when I attempt to select a value. I am trying to do this join so the user can see the corresponding values in another table – values that would direct him/her in deciding which combo box value to select.

The relationship between the two tables is a one-to-one relationship and the join in the query designer is an inner join. So why can’t I assign a value from a combo box when I display a joined field in the select query? Without the joined field the combo box approach works as expected.

Thanks!

AZ

 

[dhookom]

Consider reading about the evils of lookup fields at

http://www.mvps.org/access/lookupfields.htm.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cravincreeks]

Thanks for your reply.

I had suspected that the relationship caused by the use of a look up field may have caused the problems. So I deleted the field and used a value list (e.g., "Status - Open"; "Status - Closed") to populate the combo box instead of looking up a value in another table or query. But!?! Does this create a msys table that's hidden from me? And therefore, is it having the same effect on the use of the combobox in queries?

Most of the points in the article (see link above) seem to refer to the use of a look up field , rather than a value list. A co-worker of mine says that he's done this successfully in the past, but he's unable to diagnose the problem in this particular case.

Thanks again for your response!

AZ

 

[PHV]

In your Original Post, what was the BoundColumn of the ComboBox ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

Don't define any field as a lookup field. Not value list, not table/query, not nothing....

Keep your lookup tables and use combo or list boxes on forms to select values from your lookup tables.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cravincreeks]

The BoundColumn property was set to 1.

AZ

 

[PHV]

I rephrase:
The boundcolumn of the combo should be the PK of the looked table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cravincreeks]

dhookom,

Thanks for your reply. Ideally, I would be able to use the combobox in a tabular format (table view of the select query), as this would make it much easier for the data entry folks working on this project.

Thanks again!

AZ

 

[cravincreeks]

I just tried using a combobox on a form, but ran into the same problem! First, I deleted the problematic field from the table completely. Then I readded the field as a simple text field, length 50. Then I created a new query with the join.

I then created the form using the wizard, but left out the text column that I want to update using a restricted list in a combobox. I then added the combo box control and using the wizard, specified the field in which the values should be stored. When I tried to use the form, it still wouldn't let me pick from the combo box.

It seems that the join is causing the problem....

AZ