0

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)
Tim Stack
  • 3,209
  • 3
  • 18
  • 39
SierraA
  • 5
  • 2
  • 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 May 03 '19 at 08:30

1 Answers1

1

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

Andre
  • 26,751
  • 7
  • 36
  • 80
  • I can assume that this is specifically for a the query I'm trying to run? as in, the combination between form and direct sql, and not for a query I already created with the querydesigner. – SierraA May 03 '19 at 08:57
  • I have already tested it and it works perfectly and does exactly what I wanted it to do. Thank you very much for the help. :) – SierraA May 03 '19 at 09:09
  • You can also run `db.Execute "MySavedQuery", dbFailOnError` for an existing query. @SierraA – Andre May 03 '19 at 09:11