Inner Join Query and Combo Box

[Stubbie]

Hello!

I have a query that joins two tables in my Access 97 database:

SELECT Table1.A, Table1.B, Table1.C, Table2.E FROM Table2 INNER JOIN Table1 on Table2.D = Table1.C;

The query works as expected, associating the correct Table2.E with the rows in Table 1.

I used to the Access form wizard to create a form around this query, and that works correctly, if I manually enter Table2.E into the genereated textbox everything appears to be updated correctly. If I convert the text box into a comb box however, things stop working.

I can select the correct Table2.E text from the combo box and it updates the Table1.C value as expected. If I view the next record, and then view the previous record, the first text item in the combo box is overwritten with whatever I had selected previously. This change also appears in the database table.

For example, in Table2:

   D               E
   1             Something
   2             Else
   3             Other

If I select Other from the combo box, view the next record and comeback, the combo box will display Other, but if I view the drop down my choices will by Other, Else, Other, and Table 2 will look like:

   D               E
   1             Other
   2             Else
   3             Other

Any idea how to fix this?

Thanks

[Stewart Ross]

Hi Stubbie. It's hard to tell from what you have provided - it would be more helpful to see the full SQL for the recordsource of your form (assuming that you have not really used A, C etc as field names - which are not meaningful).

It sounds like the query on which the form is based has included the 'wrong' E-side value, the one from the one-side part of the relationship instead of from the many-side part.

As I say, it would be a lot easier to diagnose if you posted the proper SQL for the recordsource query, but anyway please check out which side of the relationship you are actually updating. Please also advise what the relationship is between the tables - presumably 1-Many?

-Stewart

ps the more I think about it the less I understand what you are trying to do. Your tables are linked by fields C and D (please provide real names!); your combo updates field C, but you show a value of 1, 2, 3 for this field and not Other, Else etc - unfathomable, without much more explanation I'm sorry to say.

[Stubbie]

Allow me to clarify:

I have two tables, Clients and Reps with the following fields:

Clients
----------
ID (AutoNumber)
Name
Company
AccountHandler (Number)

Reps
--------
ID (AutoNumber)
Name

There is a 1 Reps.ID to Many Clients.Account Handler relationship (Reps.ID is 1:many on Clients.Account Handler).

I would like to create a form that allows me to enter a client name, company, and select a Rep from a combo box. Then I save the form or move beyond it, I woud like the correct Reps.ID to be stored in Clients.Account Handler.

For example, if I have two Reps:
1 Bob Smith
2 Mary Joe

and I create a new client, I would like to select Mary Joe from the list and have it store a 2 in Clients.Account Handler.

Does this clarify?

Thanks

[Stewart Ross]

Hi Stubbie. Ahh, if you had just written up the real requirements in your first post instead of dummy ones it would have been so much easier to answer!!

Access's form wizards will create a suitable user form for you, and the Combo Box Wizard will also create the properties of the combo box bound to the Accounthandler field for you.

It is good practice to use a query as the recordsource for a form, instead of relying on the base table itself. Create a new query and add all fields from the Clients table to that query. Sort the query on whatever field is appropriate. Store the query under an appropriate name.

Use the form wizard to create a new form based on your query. Don't include the Accounthandler field, which would otherwise be shown as a simple textbox. If you do, delete it from the form when you open it in design view.

The simplest way to create the combo is to use the Access toolbox combo wizard to guide you through how to create a combo on your form, using table Reps as the source for the combo, storing the value in the field Accounthandler. You can also set this up manually with very little effort, as listed below.

Manually creating the combo, drag a combo control onto the form. The combo's recordsource is just a simple query which the Wizard would create:
[code=sql]SELECT ID, Name from Reps;[/code]
In the Column Count property set the value 2, and for the Bound Column property set the value 1 (representing the column with the ID number).

The Column Widths property defines the widths of all columns in the combo. So that users of the combo see only the name and not the ID number the width of the first column can set to 0, in which case you would have the widths set to 0cm; 5cm say. Set the List Width property to 5cm in this instance.

Set the Control Source property of the combo to the name of the field to which the combo is bound - Accounthandler in your case, and for clarity set this same value as the Name property of the combo box control itself.

That's the combo box creation done.

A step which you will have to do whether or not you use the Access Wizard to create the combo, is to set the tab order for the form so that the combo is selected in the correct sequence if a user tabs from one field to another.

Finally, size the fields on the form and align them appropriately. Save the form, and test that it operates as expected.

I attach a screenshot showing as an example the properties of one of the combo boxes on a form of mine. It is an unbound combo (no control source).

-Stewart

[Stubbie]

Fantastic, works perfectly, thank you! Also, the other information will be helpful as I finish this (hopefully) simple application.

A point to anybody else who reads this message, be sure to include AccountHandler (the linked ID field) in your query you base the form off, otherwise it doesn't seem to completely update correctly.