Msg box if no result by action query

[htadis]

good day !

I have an action query. When runs it and no result, need to give a msg "There is no data". How can i do this ? below is the code which i have used presently.

Private Sub Command0_Click()
If MsgBox("You are about to asign Vessel/Voyage for imported data.", vbQuestion + vbYesNo + vbDefaultButton1) = vbYes Then
DoCmd.OpenQuery "data_Appendto_ImptsMain", acViewNormal
DoCmd.Close acForm, "Admin_Frm_dataActive"
Else
Me.Command0.SetFocus
End If

 

[CJ_London]

you need to use the .execute method and the recordsaffected property

Private Sub Command0_Click()
dim db as dao.database
  
If MsgBox("You are about to asign Vessel/Voyage for imported data.", vbQuestion + vbYesNo + vbDefaultButton1) = vbYes Then
    set db=currentdb
    db.execute "data_Appendto_ImptsMain", dbfailonerror
     if db.recordsaffected=0 then msgbox "No records added"   
    set db=nothing 
    DoCmd.Close acForm, "Admin_Frm_dataActive"
Else
    Me.Command0.SetFocus
End If
 ...
 ...

Notes:

1. not sure, but you may need to replace your queryname with the sql of your query
2. currentdb.execute will not return recordsaffected which is why you need to assign currentdb to another db object
3. dbfailonerror means the query will report any errors in execution (the same as running the query directly)

 

[htadis]

Many thanks CJ London for yr swift respond. let me try and confirm u.

 

[htadis]

CJ London,

rcvd following error.

run time error 3061

"too few parameters. expected 2 "

 

[vbaInet]

Looks like CJ isn't here at the moment.

You have parameters in the data_Appendto_ImptsMain query that's causing the problem. The Execute method can't handle your parameters that way.

You will need to create a form and use that form for passing the parameters to your query. Remember to add the parameter to the Parameters list in the query. Here's the code after you've done the above:

    Dim db  As DAO.Database
    Dim qdf As DAO.QueryDef
    
    If MsgBox("You are about to asign Vessel/Voyage for imported data.", _
              vbQuestion + vbYesNo + vbDefaultButton1) = vbYes _
    Then
        Set db = CurrentDb
        Set qdf = db.QueryDefs("qryTestUpdate")
        
        With qdf
            .Parameters("[COLOR="Blue"]Forms!FormName!txtBoxName[/COLOR]") = [COLOR="blue"]Forms!FormName!txtBoxName[/COLOR]
            .Execute dbFailOnError
            If .RecordsAffected = 0 Then
                MsgBox "No records added"
            End If
        End With
                
        Set qdf = Nothing
        Set db = Nothing
        
        DoCmd.Close acForm, "Admin_Frm_dataActive"
    Else
        Me.Command0.SetFocus
    End If

 

[CJ_London]

If your query has links back to the form, you need to replace the query name with the sql and build as a string

e.g. query says

... where somefield=forms!myform!myfield...

will become

"...where somefield=" & myfield & "...

 

[htadis]

Many many thanks CJ London & vbaInet. It works fine now.

appreciated !

Have a great week end !