Problems with Structuring a Combo Box Row Source

[Dalan]

Sometime ago I had inserted two Combo Boxes on a form (cboCustomers)
and (cboVendors) to lookup values in separate tables. They have worked
fine except now I want to increase their usage by changing the Row
Source to include any changes made in the Customers and Vendors tables
to be reflected in the associated fields on the form (frmAccounts). An
example would be changing or correcting the spelling of a Customer or
Vendor name. Currently they have to be change manually on the
frmAccounts.

The Customer and Vendor tables only include an ID autonumber and the
name. I have done just about everything to get it to work correctly,
but with no success. Of course, when I have made the changes to the
Row Source it has blanked out all of the existing names in the fields
on the form. So I have reselected them several times and this is
becoming quite old fast. Further compunding the problem is that the
Vendors have a separate combo box on a Dialog form for selecting
individual ones for report output.

I have tried recreating the Combo Boxes with different selection
criteria, but the results were the same. The original Row Source for
the Vendors was:

SELECT DISTINCTROW tblVendors.Vendors FROM tblVendors. ORDER BY
tblVendors.Vendors

Row Source Type - Table/Query
Column Count - 1
Column Widths - 1.5"
Bound Column - 1

So I added the VenID to Select DistinctRow and added a WHERE clause
(that I must have changed a dozen times), and changed the Column Count
to 2, and 2 Column widths and Bound Column to 2 and then changed them
back to 1 and other combinations. Doesn't matter what I tried as the
resulting values in the control fields were always the Vendor ID
numbers which were of course populated in the once Vendor name fields
in the Table. The same result occurred when using the Vendor Dialog
Box as the Vendor name was no more, but instead the VenID numbers.
Worse, I could not change any of it back to its original state.

Any advice or suggestions to resolve this problem will be appreciated.
Thanks, Dalan

[Mark van Bree]

When you save the VendID in the table you will see the VendID on the
form when you place a textbox bound to the field. What you can do is
hide this textbox and create a new unbound textbox and fill it with
the the name of the vendor. Make sure to refresh the unbound field
when the vendor changes.

If you want to save the name of the vendor in the table you can try to
create a relation between the tables with "Cascade update related
records" set. This way, whan the name of a vendor changes it will also
change in the corresponding records. This is a problem when you
currently have inconsistent names in the related tables.