VBA Insert Into (1 Viewer)

[Oreynolds]

Hi,

I have the following insert into statement:

Dim RS As DAO.Recordset
      
        Set RS = CurrentDb.OpenRecordset("SELECT * FROM OrdersCheckBoxes")
        
        RS.MoveLast
        RS.MoveFirst
        Do Until RS.EOF
        
        If RS!FireCheckBox = True Then
        CurrentDb.Execute "INSERT INTO tblOrdersDisciplines (ChkSupply, DateX) VALUES (" _
        & RS!FireSupplyCheck & ", " _
        & "Date())"
        End If
                      
        RS.MoveNext
        Loop
        Set RS = Nothing

The problem I have is the original RS data are short text fields and all contain either a 0 or a -1 where as my new table data are Yes/No fields. So what I need to do is amend the statement above to check each of the above variables and convert them to a True/False.

As an example I have tried amending it to the following:

& If(RS!FireSupplyCheck)=""-1"",True,False) & ", " _

Or

& If(RS!FireSupplyCheck)=""-1"",-1,0) & ", " _

But I get errors - can anyone where I have gone wrong?

 

[arnelgp]

you can directly insert to a table without using recordset:

    CurrentDb.Execute "Insert Into tblOrdersDisciplines (ChkSupply, DateX) " & _
                    "Select -1, Date() From OrdersCheckBoxes " & _
                    "Where OrdersCheckBoxes.FireSupplyCheck = '-1';"

but i think the data is meaningless, when you only got True, and Current date?
you should also include any FK to include in the insert.

 

[Pat Hartman]

If the values are 0 and -1, then just use the Val() function or cInt()

Val(RS!FireSupplyCheck)
or
cInt(RS!FireSupplyCheck)

But the larger questions would be
1. why are you copying an entire table
2. why are you using embedded SQL
3. why are you using a VBA loop at all when you can run one query instead of potentially thousands to copy all the records at one go?