Adding to a table through a form (vb search)

[TimE]

Not sure if this is the right section (vb code?)

I am using a form to search for an item (using vb stSQL and stWHERE statement to pull from a table). If found, it will display certain fields. Once found and displayed, I would then like the option of adding this record to another table. If you can assist, I will provide the code I currently have.

 

[agehoops]

You could run an IF statement to check if there is a result and then tell it to display the fields if there are. Then have an append query which will copy the record to a table.

 

[TimE]

Thank you for your response.

I may not have provided enough detail, or I am going in the wrong direction. This is what I have so far:

Private Sub cmdFilter_Click()

If IsNull(Me.name.Value) And IsNull(Me.pay.Value) Then
MsgBox "Please enter your search criteria.", vbOKOnly + vbCritical, _
"Enter Search Criteria"

Exit Sub

End If

Dim stSQL As String
Dim stWhere As String

stSQL = "SELECT [qryTEST].[NAME], [qryTEST].[PAY] FROM qryTEST"

If Me.name <> "" And Not IsNull(Me.name) Then
stWhere = " WHERE [qryTEST].[NAME] LIKE '*" & Me.name & "*'"

End If

If Me.pay <> "" And Not IsNull(Me.pay) Then
stWhere = " WHERE [qryTEST].[PAY] LIKE '*" & Me.pay & "*'"

End If

stSQL = stSQL & stWhere & " ORDER BY [qryTEST].[NAME], [qryTEST].[PAY];"

Me.SelectionList.RowSource = stSQL
Me.SelectionList.Requery

If Me.SelectionList.ListCount = 0 Then
MsgBox "Not found in database. Would you like to ", vbOKOnly + vbCritical, _
"Not in database"
End If

End Sub


After the item is found and displayed, I would like to have it added to another table when double clicked. I need help with the SQL statment to add/append/insert.

 

[agehoops]

i'm not 100% certain on this so don't take my word for it, but i don't think you can use the SQL function of INSERT in VB so you'll have to create an actual append query in the normal query way, and then on double click, make it run the query and in the query set the criteria to the selection of the list box you double clicked, if any of this makes any sense?

However i may be completely wrong about the SQL part in VB. Do a search, you're basically looking for an INSERT statement i think