Use ComboBox to append multiple values to a Single Record

[DCSGuru]

I have a ComboBox named PIDNO in a form named DWGNO. It lists all the drawing numbers in Table PID, under column named PID using RowSource:
SELECT DISTINCTROW [PID].[PID] FROM PID;
I need to create a command button to take the selected drawing number and place it in a record in a Table named MOC in a column named Drawings. Since multiple drawings will be listed in a single record, I need the buttton to keep the previous selected values and just add the new selection.
I have gotten this far...
DoCmd.RunSQL "UPDATE MOC SET MOC.Drawings = PIDNO.Value WHERE ([MOCNO] = Forms![MOCDWG]![MOCNO].Column(1));"
But it just replaces the existing value with the new value.

Thanks for a Great WebSite!

 

[BillPower]

This might help,

UPDATE MOC SET MOC.Drawings = [Drawings] & " " & [PIDNO].[Value]
WHERE (((MOC.MOCNO)=Forms![MOCDWG]![MOCNO].Column(1)));

Bill

 

[DCSGuru]

I receive a syntax error when I use the syntax & " " & to attempt to combine the existing data in the field with the new values. I tried &""& with the same result. Am I even using the best method to get multiple values in my database record using the value in my ComboBox?

Thanks for the QUICK response this morning!!

 

[BillPower]

Hi DCSGuru,

Try:

UPDATE MOC SET MOC.Drawings = [Drawings] & ", " & [Forms]![DWGNO]![PIDNO]
WHERE (((MOC.MOCNO)=[Forms]![MOCDWG]![MOCNO].Column(1)));

This assumes that both forms DWGNO and MOCDWG are open. Also that PIDNO is the Drawing, change PIDNO to PIDNO.Column(x) if necessary.

Bill

 

[DCSGuru]

Thanks! It worked Great! Sorry about the delay on the reply...Spint DSL in Texas
I have one more question about storing multiple values in one record. As you can see, I separated each value with a comma. (I don't know if that's a good thing or not) Since I associate everything to a single number field in my table, how is the best way to store these multiple values so they will display correctly on a report?

Dean

Catch Someone Doing Something Right...