Copy selected row from one data grid to another table

[DarrenUD]

Hi All,



I am new to your forum and I am a beginner programmer in VB and VBA. I am working on a small inventory control system and I and kind of a stuck on this particular problem, so here it is:

I have a search form and it is suppose to search and filter the criteria I enter from the text boxes and combo boxes and display the resulted query on the sub form (datasheet view). From the filtered results I want to select the product from the shortlist and add it as a new record in the "purchase order details" table with a click of a command button. moreover, I want to add an extra field of "quantity" to the "purchase order details" table with the selected data from the product search query.My knowledge of Access and VBA programming limited, Please Help.

[June7]

Maybe something like:

CurrentDb.Execute "INSERT INTO tablename (ProductID, Quantity) VALUES (" & Me.productID & ", " & Me.quantity & ")"

The syntax for referencing the variable ProductID and Quantity depends on where the the code is in relation to the variables. This might be easier if you use a listbox for the 'shortlist' instead of a subform. Do you allow data entry/edit in the subform?

[DarrenUD]

Hi June, Thanks for the reply, I use three combo boxes and one text box for user input and another one to enter the Qty and that suppose to go to the second table with the fields from the first table. all the editing and deleting records are disabled. I have attach a picture for your reference. what if I have spaces in table or query names, is that going to be a problem.

[June7]

If names include spaces, special characters, punctuation (underscore is exception) or are reserved words, enclose in [].

Did you consider suggestion for listbox instead of subform?

Did you attempt code?

[DarrenUD]

I haven't tried the list box, I got manged to get the code working for couple of fields except for the attachment. here it is:

Private Sub Command26_Click()
Dim strSQL As String

strSQL = "INSERT INTO xxx_Order_Details ([Model No],Attachment) VALUES ('" & Form![xxxOrderSearchQuery]![Model No] & "','" & Form![xxxOrderSearchQuery]![Attachment] & "')"


CurrentDb.Execute strSQL, dbFailOnError
Me.xxx_Order_Details_subform.Requery
End Sub

If I use this SQL for other fields it works, I am still working on it, Thanks again for the helping me, by the way how do I use the list box, do I have to use one list box for every field?

[DarrenUD]

Tried the list box and I guess it is better than the sub form..hope my SQL works with the list box the same way...

[June7]

Can't use SQL action to copy into or from attachment field. Review http://blogs.office.com/b/microsoft-...cess-2007.aspx

[DarrenUD]

Bummer ...I have to remove the Attachment field then...Thanks June7