Prevent form from opening if row source is empty

SteveL13
SteveL13 used Ask the Experts™
on
I'm trying to prevent a form from being opened if the row source is empty.  Here is my code which isn't working:

Dim qrySearchForEarlyExitCandidateResult As Object

    DoCmd.OpenForm "frmParticipantInformation", acNormal, , , acFormEdit, acWindowNormal
    Forms!frmParticipantInformation.Form.RecordSource = "qrySearchForEarlyExitCandidateResult"
    
    If qrySearchForEarlyExitCandidateResult = "" Then
        MsgBox "This candidate does not exist"
        DoCmd.Close acForm, "Forms!frmParticipantInformation", acSaveNo
    End If

Open in new window


Can someone suggest how I can do this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
I'd use a recordset to do this:

dim rst As DAO.Recordset
rst.Open "SELECT * FROM qrySearchForEarlyExitCandidateResult"

If rst.EOF and rst.BOF Then
  '/ no records
   MsgBox "This candidate does not exist"
Else
   DoCmd.OpenForm "frmParticipantInformation", acNormal, , , acFormEdit, acWindowNormal
    Forms!frmParticipantInformation.Form.RecordSource = "qrySearchForEarlyExitCandidateResult"
End If

Note too that acSaveNo with OpenForm tells Access to not save Form modifications. It has nothing to do with data.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
If dcount("*", qrySearchForEarlyExitCandidateResult) = 0  Then
    MsgBox "This candidate does not exist"
else 
    DoCmd.openform "Forms!frmParticipantInformation"
End If

Open in new window

Author

Commented:
Dale, I get "Object variable or With block variable not set" using this:

Dim qrySearchForEarlyExitCandidateResult As Object

    If DCount("*", qrySearchForEarlyExitCandidateResult) = 0 Then
        MsgBox "This candidate does not exist"
    Else
        DoCmd.OpenForm "Forms!frmParticipantInformation"
    End If

Open in new window



Scott:  I get the same error using this:

    Dim qrySearchForEarlyExitCandidateResult As Object

    DoCmd.OpenForm "frmParticipantInformation", acNormal, , , acFormEdit, acWindowNormal
    Forms!frmParticipantInformation.Form.RecordSource = "qrySearchForEarlyExitCandidateResult"

    If qrySearchForEarlyExitCandidateResult = "" Then
        MsgBox "This candidate does not exist"
        DoCmd.Close acForm, "Forms!frmParticipantInformation", acSaveNo
    End If

Open in new window

Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
get rid of the Dim statement, then use

    If DCount("*", "qrySearchForEarlyExitCandidateResult") = 0 Then
        MsgBox "This candidate does not exist"
    Else
        DoCmd.OpenForm "Forms!frmParticipantInformation"
        Forms!frmParticipantInformation.Form.RecordSource = "qrySearchForEarlyExitCandidateResult"
    End If

Open in new window

I'm not sure why you think you need line 5 in that code, you should be able to simply set the recordsource of the form to that query name in the form design.

Author

Commented:
Perfect!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial