Sending multiple items to a table

[illy2k]

Is there anyway to do this:

Let's say I have a combo Box, looking up values in a products table. Values like Product name and such. Is there anyway to send back into the table not only the choice you selected on from the combo box, but sending back 0 values of all the products you didn't select from that combo box?

 

[dcx693]

Since, by definition, you can only select one item from a combo box, then all other choices are not selected. Why would you need to store a 0 value for this?

 

[illy2k]

Totaling

I am working on a report that does totals of every order ID for s date range. The only problem is that on some orders, some items are not ordered. See, each item is attached to a code. 1 is for parts, 2 is for labor, 3 is for tires, ect all the way to 6. When you enter stuff in the form, there is a combo box to a lookup of the products table were all that stuff is stored. So you can pick any product, and on another lookup you can pick any type of labor. Here is where the problem arises. I need a total of labor, and also a separate total for maintanence for my report. If I do not enter both on the form, the query fails becasue if there is no info for both these fields it just doesn't display it. I need the query to understand that if there is nothing there, then it is zero. I hope this helps.

 

[Calvin]

I'm assuming that you want to update existing records and not create a new one with this update process.

If so try doing two things,

Private Sub cboProducts_AfterUpdate()
'first reset all records to zero
CurrentDB.Execute "Update tblProducts SET [Selected]=0;"
'second update only the record of the product that was selected
CurrentDB.Execute "Update tblProducts SET [Selected]=-1 WHERE [ID]=" & Me.cboProducts & ";"
End Sub

-assumes that a combo box is used, called cboProducts, with 2 columns, first column is the bound column and has a length of 0 so it does not show in the dropdownlist and will contain the unique id of the product record so when the combo box is referenced it's value will return the unique ID not the displayed product name.
-the combo box row source (cboProducts.RowSource) would contain a sql statement similar to:
"SELECT [ID], [ProductName] FROM tblProducts ORDER BY [ProductName];"

Hopefully this will get you started.

 

[Calvin]

Maybe I went a bit to far with my last post. After rereading your last post

I need the query to understand that if there is nothing there, then it is zero.

Try using an IIF statment in the query converting empty or null data to 0

[Selected]: IIF(isnull([Selected]) or [Selected]="",0,[Selected])

make sure to change field to Expression

again, hope this helps