Copy records from table1 to table2 based on inputbox value

[yschuerm]

Hi,

I want to be able to copy a selection of table1 to table2 where a specific field in table1 equals value of inputbox.

I have got the following code, but get an error on [Competitors].MoveFirst

Tables are "Competitors" and "Result Search"

Private Sub cmdBernArt_Click()

Dim Message As String
Dim tab1 As Recordset
Dim db As Database
Set db = CurrentDb
Dim sql As String
Message = InputBox("Enter BernerArticle")

sql = "INSERT INTO [Result Search] * " & _
              "SELECT * " & _
              "FROM [Competitors] " & _
              "WHERE [Competitors].[BernerArtNr] = '" & Message & "';"

[Competitors].MoveFirst

Do While Not [Competitors].EOF


DoCmd.RunSQL sql
[Competitors].MoveNext

Loop

End Sub

 

[ranman256]

no code is needed for this.
It is a single append query. you are reinventing the wheel.
Take your sql shown, put it in a query, and append to the table.

 

[Micron]

You may have a reason for using code when you don't have to, so my comment is, I don't believe you can use the first wildcard. Try
sql = "INSERT INTO [Result Search] SELECT * FROM [Competitors] "
sql = sql & "WHERE [Competitors].[BernerArtNr] = '" & Message & "';"
I have too much trouble with line continuation characters, so I don't use them.