Combo Box Overwriting Table

[Guest]

I have trawled through this site looking for the answer and I have had to
give up and ask even though I'm sure it's been dealt with before!

I have a form and a subform.
The form's record source is:
SELECT T_ORDER.T_ORDER, T_ORDER.ORD_DATE, T_ORDER.ORIGINATOR,
T_ORDER.SUP_ID, SUPPLIER.SUP_NAME
FROM SUPPLIER INNER JOIN T_ORDER ON SUPPLIER.SUP_ID = T_ORDER.SUP_ID
ORDER BY T_ORDER.ORD_DATE;

And it has a combo box on it with the following parameters:
Control Source: SUP_ID
Row Source: SELECT SUPPLIER.SUP_ID, SUPPLIER.SUP_NAME
FROM SUPPLIER
ORDER BY SUPPLIER.SUP_NAME;
Column Count: 2
Column Width: 0cm,2.54cm
Bound Column: 1
This allows me to select a supplier name and amend which supplier an order
was placed with. Works fine (just including for info).

The subform's record source is:
SELECT VENDOR.VENDOR_ID, VENDOR.VENDOR_NAME, PRODUCT.PROD_ID,
PRODUCT.PROD_NAME, ORDER_LINE.ORD_ID, ORDER_LINE.ORD_QTY, T_ORDER.T_ORDER
FROM VENDOR INNER JOIN (T_ORDER INNER JOIN (PRODUCT INNER JOIN ORDER_LINE ON
PRODUCT.PROD_ID = ORDER_LINE.PROD_ID) ON T_ORDER.T_ORDER =
ORDER_LINE.T_ORDER) ON VENDOR.VENDOR_ID = PRODUCT.VENDOR_ID;

It has two combo boxes on it, I'll only describe one as they both are
similarly coded and produce the same error. The combo box Vendor has
parameters:

Control Source: VENDOR_ID
Row Source: SELECT VENDOR.VENDOR_ID, VENDOR.VENDOR_NAME
FROM VENDOR
ORDER BY VENDOR.VENDOR_NAME;
Column Count: 2
Column Width: 0cm,2.54cm
Bound Column: 1

When I select a Vendor name from the list, with it bound to Col 1 I get the
error message:
Control can't be edited, it is bound to autonumber field VENDOR_ID.
If I change the bound column to 2, I can change the field name fine.
However, when I look at the underlying table, the VENDOR_NAME is also changed!

For example, I have three vendors: Microsoft, Adobe and WRQ. If I change the
vendor in the subform from Microsoft to WRQ and then look at the Vendor
table, I now have two WRQ's!

I just want to select a different name, and do not understand why this
overwrites the table when the combo box on the form performs perfectly with
very similar coding. I will be forever indebted to anyone who can shed some
light on what I've done wrong!

 

[Roger Carlson]

You are actually changing the value of the vendor name IN THE VENDOR TABLE,
instead of adding a vendor to the source of your subform. Although I don't
know your whole application, it is quite likely that you can take the Join
to the VENDOR table right out of your Record Source.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Hi Roger

When I did that, the Vendor column entries all turned to #Name? and although
the vendor names were visible in the drop down, they could not be selected.

Any further suggestions, gratefully received!

Louise

 

[Roger Carlson]

Make sure you have PRODUCT.VENDOR_ID in your query and bind the combo box to
that field.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L