What am I missing???

[tyewonk]

What am I doing wrong?


If I change the combo box to read 126, it says it will append 0 rows.
However, if I remove the variable portion and replace it with 126, it wants to append 1 row. (i.e. WHERE tblItems.UPC = '126'; )

Private Sub cboUpcScan_AfterUpdate()
Dim strSQLUPCScan As String
strSQLUPCScan = "INSERT INTO tblItemsSold (UPCSold, DescSold, PriceSold) SELECT UPC, DESC , List FROM tblItems WHERE tblItems.UPC = '" & Me.cboUpcScan & "';" 
DoCmd.RunSQL (strSQLUPCScan)
End Sub

[tyewonk]

Came across another site that said lookup fields are evil and cause this issue. I switched combo box to a text box and works now. I just loose the dropdown feature...

[June7]

Lookups with alias are evil if set up in table, otherwise they have valid use on forms. Just have to get the referencing correct. Refer to the item ID, not the UPC code. Of course, if you are using the UPC as a primary key, would be different situation.

Why are you even trying to save the item UPC, description, cost values? Just save the item ID. Why do you need to use INSERT sql action instead of doing data entry directly into the destination table through a bound form?

[tyewonk]

Well, for one, because I am new to access and most of what you just said is jibberish to me. LOL. With my limited knowledge I need these to make it work the way I know how for the time being. As I progress I will rewrite this or start over, but for now i just need something that works, even if its ugly.

[June7]

You are taking what would normally be the harder approach and one that seems odd for a newbie to even be aware of.

Bind form to table, that means set the form RecordSource property. Bind controls (textbox for one) to fields of the form recordset, that means set the control ControlSource property. Anything entered into the control by whatever method passes directly into record of table. This is basic Access functionality.

If you have already set up bound form and controls, use of the INSERT sql action makes no sense.

If you really don't want to use bound form, then need to know more about your data and the combobox. Sounds like you are using an autonumber as the primary key for items and not the UPC. Also sounds like it is not a multi-column combobox and does not include the ID in a column. Review http://www.datapigtechnologies.com/f...combobox3.html