How do I check or validate if there is a record in a SQL statement?

[wind20mph]

Payroll main form to post attendance record. Before I open the attendance form, I wanted to check if there is a record in the sql statement.

The code below produces error MS Acces object "Variable or With Block Variable Not Set"

Private Sub cmdPostAtt_Click()

Dim stSQL As String
Dim dbHBK As DAO.Database
Dim rsHBK As DAO.Recordset


    If IsLoaded("pmkpayrollp") Then
        stSQL = "SELECT * FROM attemain WHERE ((eid LIKE forms![pmpayentrya]![empno]) AND (pperiod LIKE forms![pmkpayrollp]![Text9]))"
        'MsgBox Forms![pmpayentrya]![empno] & " - " & Forms![pmkpayrollp]![Text9]
        Set dbHBK = CurrentDb
        Set rsHBK = dbHBK.OpenRecordset(stSQL)
        If rsHBK.RecordCount >= 1 Then
        DoCmd.OpenForm "attpop", , , , , acDialog
        Else
            MsgBox "No attendance recorded", vbInformation + vbOKOnly, "Nothing to Post"
        End If
        Set rsHBK = Nothing
    Else
        MsgBox "No payroll period"
    End If
        
End Sub

 

[pr2-eugin]

You need to concatenate Form control values, try this.

Private Sub cmdPostAtt_Click()
    Dim stSQL As String
    Dim dbHBK As DAO.Database
    Dim rsHBK As DAO.Recordset

    If IsLoaded("pmkpayrollp") Then
        stSQL = "SELECT * FROM attemain WHERE ((eid = [COLOR=Red][B]" &[/B][/COLOR] Forms![pmpayentrya]![empno] [COLOR=Red][B]& "[/B][/COLOR]) AND (pperiod =[COLOR=Red][B] '" &[/B][/COLOR] forms![pmkpayrollp]![Text9] [COLOR=Red][B]& "'[/B][/COLOR]))"
       [COLOR=Green] 'MsgBox Forms![pmpayentrya]![empno] & " - " & Forms![pmkpayrollp]![Text9][/COLOR]
        Set dbHBK = CurrentDb
        Set rsHBK = dbHBK.OpenRecordset(stSQL)
        If rsHBK.RecordCount >= 1 Then
            DoCmd.OpenForm "attpop", , , , , acDialog
        Else
            MsgBox "No attendance recorded", vbInformation + vbOKOnly, "Nothing to Post"
        End If
        Set rsHBK = Nothing
    Else
        MsgBox "No payroll period"
    End If
End Sub

I have taken eid to be Number and pperiod as String.

 

[wind20mph]

thank you very much! that was the root cause. that even when I call query still gives error. now all is well. again thank you very much. problem solved.

 

[pbaldy]

Regarding your reported post, you can mark this thread Solved by going into Thread Tools, Edit Thread. Change Question to Solved.

Typically threads are not locked when solved, in case somebody wants to post a follow up question.