Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2014
    Posts
    17

    Unanswered: Return message if no record is found.

    Hello,

    I have the following VB which is used to search for a record, basically you enter the surname into a text box on the home page and press enter and it will open up the Candidate form on the specific record,

    Private Sub cmd_Command4_Click()
    DoCmd.OpenForm "frm_Candidate", , , "surname = '" & Me.lbl_Surname & "'"
    Me.lbl_Surname.Value = ""
    End Sub


    However if you search for a surname that doesnt exist it brings up a blank form, Can someone help me with the code so it brings back a message saying 'User not found' or something similiar.

    Hope this make sense, thankyou for any help!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    either place soem code in frm_candidate's NODATA event
    or
    place some code in the command button that checks to see if the surname exists before opening the form

    Code:
    Private Sub cmd_Command4_Click()
    if dcount("Surname","mytable","surname = '" & Me.lbl_Surname & "'") >=1 then
      DoCmd.OpenForm "frm_Candidate", , , "surname = '" & Me.lbl_Surname & "'"
      Me.lbl_Surname.Value = ""
    else
      msgbox "Oi muppet, put in a surname that already exists",vbExclamation, "Search for:" &  Me.lbl_Surname
    endif
    End Sub
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can use:
    1. In the module of the form you open (frm_Candidate):
    Code:
    Private Sub Form_Load()
    
        If Me.Recordset.RecordCount = 0 Then 
            MsgBox "User not found", vbInformation, "frm_Candidate"
            DoCmd.Close acForm, Me.Name
        End If
       
    End Sub
    OR
    2. In the module that opens the form:
    Code:
    Private Sub cmd_Command4_Click()
    '
    ' Source is the name of the RecordSource object (table or query) used by frm_Candidate.
    '
        If DCount("*", "Source", "surname = '" & Me.lbl_Surname & "'") > 0 Then
            DoCmd.OpenForm "frm_Candidate", , , "surname = '" & Me.lbl_Surname & "'"
        Else
            MsgBox "User not found", vbInformation, "cmd_Command4_Click"
        End If
        Me.lbl_Surname.Value = ""
    
    End Sub
    Have a nice day!

  4. #4
    Join Date
    Mar 2014
    Posts
    17
    Thankyou! That worked perfectly.

  5. #5
    Join Date
    Mar 2014
    Posts
    17
    Thank you also Sinndho, I went for option 2

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •