VBA Determine a 0 matches in found in a record Search (1 Viewer)

skoolz

Registered User.
Local time
Today, 07:23
Joined
Jun 26, 2015
Messages
32
Hi all,

i need you help with a record search code of mine which was and works on an earlier version of my database but not on this latest version I have had to redevelop and re-assign tables etc because I had made 2 initial fundamental design errors of saving text data to tables instead of binary and I also I wasn't using bound forms to insert/update records which I got stick for on here when I asked if I would have problems deploying for multi-users over a network.

Anyhow...issue is my search code on new version works and shows my records in the frmOrgDetailsP when matching records are found. However in the new version if no records are found I just get a completely black screen whereas in my earlier working version if no records were found I would still get the frmOrgDetailsP load up but with no records showing which was ok because the form has buttons to return to main search page and start/try again. New version it like it's frozen out.

nothing has really changed from the two form versions...

any ideas of code I can add to determine is the search hactually contains records and if not display a message "No matching records found, try again" and not load the frmOrgDetailsP form.

Code:
Private Sub search_record()

Dim recfield As String
      
      Me.cmbchooseDept = Nz(Me.cmbchooseDept, "")
      If Me.cmbchooseDept = "" Then
      MsgBox ("Please choose a Department first from the Top Left of the page!"), , "Blank field Error!"
      Me.cmbchooseDept.SetFocus
      Exit Sub
      End If
      
      Me.txtsearch = Nz(Me.txtsearch, "")
      If Me.txtsearch = "" Then
      MsgBox ("No search details have been entered!"), , "Blank field Error!"
      Me.txtsearch.SetFocus
      Exit Sub
      End If

recfield = Me.txtsearch.Value

If Me.cmbchooseDept = "Plowing" Then

    If Me.sfilter.Value = "Pref No." Then
    
    'example DoCmd.OpenForm "Employees", , , "LastName = 'King'"
    DoCmd.OpenForm "frmOrgDetailsP", , , "appNo = '" & recfield & "'"
    
    ElseIf Me.sfilter.Value = "Application No." Then
    
    DoCmd.OpenForm "frmOrgDetailsP", , , "appNo = '" & recfield & "'"
    
    ElseIf Me.sfilter.Value = "Org. Name" Then
    
    DoCmd.OpenForm "frmOrgDetailsP", , , "organisationName Like '*" & recfield & "*'"
    
    Else
    
    DoCmd.OpenForm "frmOrgDetailsP", , , "organisationName Like '*" & recfield & "*'"
    
    End If
Endif
End sub

Much appreciated
 

Minty

AWF VIP
Local time
Today, 14:23
Joined
Jul 26, 2013
Messages
10,354
If you create a SQL query based on your search criteria and open it as a recordset, you can check for records in the the temporary recordset and then either not open the form or display a message to try different criteria or display all records?
 

skoolz

Registered User.
Local time
Today, 07:23
Joined
Jun 26, 2015
Messages
32
Hi Minty but I'm new to access and don't know how to create a recordset as you suggested. And exemplary code would be appreciated.

Meanwhile i have tried my revised code below however when I try search now I'm getting the no records fine but even for valid search details i know records exist. My test of the Dcount using a msgbox shows the result as -1 for everything. Any idea why I'm getting this?

Code:
recfield = Me.txtsearch.Value

If Me.cmbchooseDept = "Plowing" Then

    If Me.sfilter.Value = "Pref No." Then
    
            If DCount("orgID", "tblOrgDetailsP", "appNo = '" & recfield & "'") < 1 Then
                
            MsgBox "No records matching criteria found!"
                
            Else
            DoCmd.OpenForm "frmOrgDetailsP", , , "appNo = '" & recfield & "'"
            End If

    
    ElseIf Me.sfilter.Value = "Application No." Then
    
    DoCmd.OpenForm "frmOrgDetailsP", , , "appNo = '" & recfield & "'"
    
    ElseIf Me.sfilter.Value = "Org. Name" Then
    
    DoCmd.OpenForm "frmOrgDetailsP", , , "organisationName Like '*" & recfield & "*'"
    
    Else
    
    DoCmd.OpenForm "frmOrgDetailsP", , , "organisationName Like '*" & recfield & "*'"
    
    End If
Endif
End sub
 

Users who are viewing this thread

Top Bottom