Inserting records one by one into a table -Object Required Error. HELP

[Maggie24]

Hi all,

I have a piece of code which i know works where by I insert a record into a new table.

I want to now insert a group of records (whicch are displayed in a list box - all are selected by default) into the table but i am getting a Debug Error - Object required when I try to do this.

This is my code:

Dim mydb As DAO.Database
Set mydb = CurrentDb()

ReDim sqlArray(List42.ItemsSelected.count)


count = 0
For Each varitm In List42.ItemsSelected

If varitm = 1 Then
If MsgBox("You are about to DELETE " & List42.ItemsSelected.count & " records", vbOKCancel) = vbCancel Then
X = 1
MsgBox ("You have chosen to cancel the delete")
End If
End If

If X = 0 Then

'AMG - Move Duplicate of ITEM NO + DESC MATCH to Duplication table


strSQL = "INSERT INTO DuplicationFinal ([Item No], [Product Description], Site, SearchType, [Product No Sys] )"
strSQL = strSQL + " SELECT tblProducts.[Item No], tblProducts.[Element Description], tblProducts.Site, 'ITEM_F' AS Search, tblProducts.[Product No]"
strSQL = strSQL + " FROM tblProducts WHERE tblProducts.[Product No]= " & List42.Column(0, varitm)

MsgBox (strSQL)
db.Execute (strSQL)

'AMG - Delete the Duplicated Record from the Products table
strSQL = "Delete tblProducts.[Element Description] from tblProducts WHERE tblProducts.[Product No]= " & List42.Column(0, varitm)

db.Execute (strSQL)

Set db = Nothing
Refresh
End If

count = count + 1

Next varitm



Any help would be so appreciated as I need this fixed ASAP!!

MAGGIE

"Work is the curse of the drinking classes

 

[bboffin]

You declared a variable called mydb and assigned CurrentDb to it.

You then use an undeclared variable called db to call the Execute.

You should always include Option Explicit at the start of a module so that this type of bug is picked up by the compiler.

Bob Boffin

 

[Maggie24]

I can't believe i missed that!

Thanks very much Bob!

Maggie

"Work is the curse of the drinking classes