How to handle 0 lines found after a DoCmd.RunSQL(INSERT INTO...)

Question

For starters, I only started yesterday with the attempts of introducing SQL into my VBA code.

I'm trying to use VBA/SQL to Insert Data into a local table, made from a combination of a Database table and form input. I want to know how to trigger a "0 Lines retrieved".

I've already tried looking on several pages on how to handle "0 lines to Insert" when running a DoCmd.RunSQL("INSERT INTO ... SELECT ... FROM ... WHERE ...).

The code itself works when there is data present, so that's not the problem.

The problem itself is when I don't find data, I want to trigger a messagebox that gives instructions on how to handle the current situation.

Sadly, I have not found on how I can trigger this.

sqlTempInsert = "INSERT INTO tblScanInput (Support, EAN, Counted, Product, Description, Launched, Collected) " & _
        "SELECT " & lblSupportData.Caption & ", " & txtEANInput.Value & ", "

If txtAmountInput.Visible = True Then
    sqlTempInsert = sqlTempInsert & txtAmountInput.Value & ", "
ElseIf txtAmountInput.Visible = False Then
    sqlTempInsert = sqlTempInsert & "1, "
End If

sqlTempInsert = sqlTempInsert & "GEPRO.CODPRO, GEPRO.DS1PRO, GESUPDC.UVCSRV, GESUPDC.UVCLIV " & _
        "FROM [Database_Table] GESUPDC LEFT OUTER JOIN [Database_Table] GEPRO ON GESUPDC.CODPRO = GEPRO.CODPRO " & _
        "WHERE GESUPDC.NUMSUP = " & lblSupportData.Caption & " AND GESUPDC.EDIPRO = '" & txtEANInput.Value & "';"   

DoCmd.RunSQL(sqlTempInsert)

I would suggest to make a prior count select with the same query you are using for insert, to check the number of the rows... then if 0, do your stuff. This does mean you have to run 2 queries each time, and maybe there are better solutions... Access is not my thing. — FAB

Andre Andre · Accepted Answer · 2019-05-03T08:45:31

Use .Execute and .RecordsAffected.

Dim db As DAO.Database
Dim x As Long

Set db = CurrentDb
db.Execute sqlTempInsert, dbFailOnError
x = db.RecordsAffected
If x = 0 Then
    ' nothing was inserted
End If

Note: pay attention to Delete 5 Records but RecordsAffected Property is 0

How to handle 0 lines found after a DoCmd.RunSQL(INSERT INTO...)

1 Answers