Combo Box Overwriting Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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
 
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
 
Back
Top