Search results in no records. How to get back to my Switchboard when this happens? (1 Viewer)

Umpire

Member
Local time
Today, 06:56
Joined
Mar 24, 2020
Messages
120
I have a database that contains equipment serial numbers. I have a switchboard set up to run the database. One option is to search by serial
number. When I enter an existing number, everything works fine. I have a button on the form to return to the switchboard.

The problem is if I enter a serial number that is NOT in the database. I end up with a blank page that has no buttons.
I want to either have the buttons show up or, preferably, have a message pop up notifying the user that there is no records found and then return to the switchboard.

I searched the web and found some information about VBA programs to do this but they were so far over my head that I could not even figure out what to change to make it work.

I am thinking there is some type of macro I could use along with an form event (On Open?) to do this.

Any help appreciated.
 

Ranman256

Well-known member
Local time
Today, 09:56
Joined
Apr 9, 2015
Messages
4,339
I run the rpt thru a routine that checks if the query in the report (or form)
has records in it. But 1st it must get the query out of the report that was given.
If no records, return to the switchboard:

Code:
Sub OpenRpt(ByVal pvRpt)
Dim bHasRecs As Boolean
Dim vQry
vQry = getQryFromRpt(pvRpt)
bHasRecs = HasRecCount(vQry)
If bHasRecs Then
DoCmd.OpenReport pvRpt
Else
MsgBox "No records exist for this report."
OpenSwitchboard
End If
End Sub

Public Function getQryFromRpt(ByVal pvRpt)
Dim r 'As Report

on error resume next
Set db = currentdb
DoCmd.OpenReport pvRpt, acViewDesign
Set r = Reports(pvRpt)
getQryFromRpt = r.RecordSource
DoCmd.Close acReport, r.Name, acSaveNo
Set r = Nothing
End Function

Public Function HasRecCount(ByVal pvQry) As Boolean
On Error Resume Next
HasRecCount = DCount("*", pvQry) > 0
End Function
 

Umpire

Member
Local time
Today, 06:56
Joined
Mar 24, 2020
Messages
120
I run the rpt thru a routine that checks if the query in the report (or form)
has records in it. But 1st it must get the query out of the report that was given.
If no records, return to the switchboard:

Code:
Sub OpenRpt(ByVal pvRpt)
Dim bHasRecs As Boolean
Dim vQry
vQry = getQryFromRpt(pvRpt)
bHasRecs = HasRecCount(vQry)
If bHasRecs Then
DoCmd.OpenReport pvRpt
Else
MsgBox "No records exist for this report."
OpenSwitchboard
End If
End Sub

Public Function getQryFromRpt(ByVal pvRpt)
Dim r 'As Report

on error resume next
Set db = currentdb
DoCmd.OpenReport pvRpt, acViewDesign
Set r = Reports(pvRpt)
getQryFromRpt = r.RecordSource
DoCmd.Close acReport, r.Name, acSaveNo
Set r = Nothing
End Function

Public Function HasRecCount(ByVal pvQry) As Boolean
On Error Resume Next
HasRecCount = DCount("*", pvQry) > 0
End Function
Thanks for responding. I am so knowledge challenged in VB, that I do not even know what to change in your code to match my form.

Can you point out what I need to change?

Also just to verify, I am using a form and this code will allow the form to work like it does now if the searched data does return records?

And I would put this in as an OnEvent (On Open?) function?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:56
Joined
Feb 28, 2001
Messages
27,003
The problem is in whatever you use to actually perform the search. You need to tell us what steps you take because one of the steps needs an extra test for "0 records returned." Access can easily tell you that such a thing has happened, but - as you are probably realizing both in Access and on the forum - it is a matter of asking the right question at the right time. Since we don't know your method of doing this search, we cannot tell you what is either the right time or the right method.

Having said that, rest assured that it IS possible to know that you got back doodlum squat after a search. As my old aunt Hattie used to say, "Just got to hold your mouth right when you do it."
 

Cronk

Registered User.
Local time
Tomorrow, 00:56
Joined
Jul 4, 2013
Messages
2,770
This was the problem that you originally posted. I find there is not enough information in your questions. Where is your button placed on your form?

If it is placed in the detail of a continuous form, then of course it will not show if there is no record returned from the search. You could put the button on the form's header or footer. That might or not work depending on what the button click is supposed to do. More information please.
 

Cronk

Registered User.
Local time
Tomorrow, 00:56
Joined
Jul 4, 2013
Messages
2,770
I meant to post this in the subsequent new thread by the OP but plog's post supersedes it.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:56
Joined
Feb 19, 2002
Messages
42,982
Access is a RAD tool (Rapid Application Development). As such, it does a great deal for you and when you are not getting the results you want, it frequently comes down to tweaking properties. Code might be necessary but is is not usually the first option.

Reports have a no data event - third item on the Event tab of the properties sheet. When you click on the builder (....) to the right of the property, Access will prompt you for what you want to do. Choose code. If you have set the default to always write code, Access opens to the event procedure with the cursor placed where you need to start typing.

You're going to need two lines of code here.

msgbox "No data was found or whatever you want to say.", vbOKOnly
Cancel = True

The first line displays a message. The second line prevents the empty report from opening.

If you opened the report from code or a macro, the calling procedure will get an error because the report failed to open. You have to trap this error and suppress it.

The procedure originally probably contained only the one line of code to open the report. I had to add an error handler to trap the 2501 error and surpress it. Case Else is there to display any other error that might have occurred.
Code:
Private Sub OpenRpt_Click()

On Error GoTo ErrProc
    DoCmd.OpenReport "copy of frmreferences", acViewPreview
   
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 2501
            ' report cancelled
        Case Else
            MsgBox Err.Number & "--" & Err.Description, vbOKOnly
    End Select
End Sub
 

Umpire

Member
Local time
Today, 06:56
Joined
Mar 24, 2020
Messages
120
I have a database that is a record of equipment and software tested in my section. I have a switchboard that opens a Split Form that has fields from multiple tables that list things like Date Tested, Who tested it, What was the result etc. The lower half of the form is a Datasheet View showing all the applicable records that are returned by the Query built into the form. In the criteria for the Source Field I have Like "*" & [Enter Location: ] & "*"
This gives me a box to enter the location name (or part of the name). This part of the form works fine. When I enter a location that is in the database, the form opens fine. In the detail section of the form, I have a button that runs a macro that closes the window. this returns the user to the switchboard they ran the search from. (I am using a macro because all of my searches use the same basic form. Just change the criteria (part number instead of location for example.)

The problem is when I enter a location that is not in the results. The form opens with nothing displayed. No blank fields, no titles, no buttons. Just a header and a blank data sheet.

What, where, and how do I enter something to display a Message Box saying there are no records to display and then when the user clicks on OK, the window closes so they are back at the Switchboard they started from?

I hope this explains my issue.
 

Umpire

Member
Local time
Today, 06:56
Joined
Mar 24, 2020
Messages
120
Access is a RAD tool (Rapid Application Development). As such, it does a great deal for you and when you are not getting the results you want, it frequently comes down to tweaking properties. Code might be necessary but is is not usually the first option.

Reports have a no data event - third item on the Event tab of the properties sheet. When you click on the builder (....) to the right of the property, Access will prompt you for what you want to do. Choose code. If you have set the default to always write code, Access opens to the event procedure with the cursor placed where you need to start typing.

You're going to need two lines of code here.

msgbox "No data was found or whatever you want to say.", vbOKOnly
Cancel = True

The first line displays a message. The second line prevents the empty report from opening.

If you opened the report from code or a macro, the calling procedure will get an error because the report failed to open. You have to trap this error and suppress it.

The procedure originally probably contained only the one line of code to open the report. I had to add an error handler to trap the 2501 error and surpress it. Case Else is there to display any other error that might have occurred.
Code:
Private Sub OpenRpt_Click()

On Error GoTo ErrProc
    DoCmd.OpenReport "copy of frmreferences", acViewPreview
  
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 2501
            ' report cancelled
        Case Else
            MsgBox Err.Number & "--" & Err.Description, vbOKOnly
    End Select
End Sub
Thank you for your help. However I am using a form not a report. I do not see a "No Data" event for a form.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:56
Joined
Feb 28, 2001
Messages
27,003
Find the place in your code where you can successfully open the form when you have data. It has to be near the part that involves the [Enter Location:] element you just showed us. The point you want is probably just after that question is asked. Consider changing that section.

1. Just before you would normally open the form, instead of waiting for the query to ask the question, you create an Input Box where you can ask that question at a time you have control. Using an input box allows you to store the result in a variable because you will use it more than once.

Reference: https://support.microsoft.com/en-us...350-b7f1-4786575314d9?ui=en-us&rs=en-us&ad=us

2. Look hard at the query you are using to select and return data. Create a DCount based on that query that will count the records you would expect to see. In essence, you use DCount to count records like this: NumRec = DCount( "*", "your data source goes here", "your criteria clause goes here" ) where the criteria clause is just the WHERE clause without the word WHERE and the data source is either a table or a query name. The first argument, "*", just lets you count any records.You can build the criteria clause as a simple string including a substitution for the location field. Then, when the DCount runs, check the count of records that it returned. If it is zero, you can do something like pop up a message box with "No Data" or however elaborate a message you need. Then you can close the search form like you would if you had returned data.

Reference: https://support.microsoft.com/en-us...e42-be7a-11a64acbf3d3?ui=en-us&rs=en-us&ad=us

3. If step 2 returned a record count other than 0, you have something to display. When you are about to build the SQL that will become the form's recordsource, you can build the SQL in one step to include string concatenation that includes the answer of the Input Box. In other words, divide and conquer - first build the SQL and then use it once built. After the SQL string is ready, do whatever you were going to do with the form's recordsource.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:56
Joined
Feb 19, 2002
Messages
42,982
Sorry, I saw "report". Save the code for when you have to open a report that might have no data. Doc's suggestion takes care of forms.
 

Cronk

Registered User.
Local time
Tomorrow, 00:56
Joined
Jul 4, 2013
Messages
2,770
Umpire, rather than trying to much around with the switchboard if it's the inbuilt one, I suggest you use Doc's method in the Open event of your form and if there are no records set cancel = true which stops the form from continuing to open.
 

Umpire

Member
Local time
Today, 06:56
Joined
Mar 24, 2020
Messages
120
Umpire, rather than trying to much around with the switchboard if it's the inbuilt one, I suggest you use Doc's method in the Open event of your form and if there are no records set cancel = true which stops the form from continuing to open.
OK progress is being made.
Using the code builder for the event "OpenForm" I have ended up with this:
NumRec = DCount("*", "LocationSearchResults", "Source")

If NumRec > 0 Then

DoCmd.OpenForm "LocationSearchResults"

Else

MsgBox "There are no records to view."



End If

Private Sub Form_Load()

End Sub

Private Sub Form_Open(Cancel As Integer)

End Sub


Now when I open then form, It asks for the location I am looking for as I expect but it never opens the form or displays the message box. I get the same result if I enter a source that is in the database as or if I enter a source that is not in the database.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:56
Joined
Feb 28, 2001
Messages
27,003
To be more specific:

In the Form_Open routine, if you have set up the .RecordSource property of the form correctly, you have the perfect opportunity to implement the test that I suggested but (as Cronk reminded me) there is an easier way. For a bound form, you have a recordset that you can test.

Code:
If Me.Recordset.Count = 0 Then
    Msgbox "No records - closing form", vbOKOnly, "No records"
    Cancel = -1
End If

That will prevent the form from opening, but it ONLY works in the Form_Open routine, which has a Cancel argument. If you don't do it there, the form WILL open.


Since it is a recordset, it has the properties of a recordset. Including .Count.
 
Last edited:

Cronk

Registered User.
Local time
Tomorrow, 00:56
Joined
Jul 4, 2013
Messages
2,770
Doc, I believe the form's recordset object exists at form open. I'd use Dcount.

I never thought about it before but reports have a NoData property at open. Might be something like Dcount behind the scenes.
 

Umpire

Member
Local time
Today, 06:56
Joined
Mar 24, 2020
Messages
120
To all,
I found a workaround. If I allow editing of the form (in form properties) it will show a blank form including the command button to return to the menu.
For now, this will allow me to have a usable set up. As I gain experience and understanding of VBA and Access, I will implement the suggestions given here. Once I get it working on a backup copy of the database I will implement the change in the live database.

Locking down the database is not critical for now as there will only be myself and 2 others using it. And we are in the same location.

Thanks to everyone for your help.
 

Umpire

Member
Local time
Today, 06:56
Joined
Mar 24, 2020
Messages
120
One additional question:
While looking online for VBA courses, most (all?) refer to Excel VBA. Does that matter? or is Excel VBA applicable only to Excel and the information will not translate to Access?
 

Umpire

Member
Local time
Today, 06:56
Joined
Mar 24, 2020
Messages
120
Find the place in your code where you can successfully open the form when you have data. It has to be near the part that involves the [Enter Location:] element you just showed us. The point you want is probably just after that question is asked. Consider changing that section.

1. Just before you would normally open the form, instead of waiting for the query to ask the question, you create an Input Box where you can ask that question at a time you have control. Using an input box allows you to store the result in a variable because you will use it more than once.

Reference: https://support.microsoft.com/en-us...350-b7f1-4786575314d9?ui=en-us&rs=en-us&ad=us

2. Look hard at the query you are using to select and return data. Create a DCount based on that query that will count the records you would expect to see. In essence, you use DCount to count records like this: NumRec = DCount( "*", "your data source goes here", "your criteria clause goes here" ) where the criteria clause is just the WHERE clause without the word WHERE and the data source is either a table or a query name. The first argument, "*", just lets you count any records.You can build the criteria clause as a simple string including a substitution for the location field. Then, when the DCount runs, check the count of records that it returned. If it is zero, you can do something like pop up a message box with "No Data" or however elaborate a message you need. Then you can close the search form like you would if you had returned data.

Reference: https://support.microsoft.com/en-us...e42-be7a-11a64acbf3d3?ui=en-us&rs=en-us&ad=us

3. If step 2 returned a record count other than 0, you have something to display. When you are about to build the SQL that will become the form's recordsource, you can build the SQL in one step to include string concatenation that includes the answer of the Input Box. In other words, divide and conquer - first build the SQL and then use it once built. After the SQL string is ready, do whatever you were going to do with the form's recordsource.
OK, As I learn more about VBA I think I understand what you saying. Let me see if I have this "flow" correct:

Switchboard selection opens my form;
I have a OnOpen event set to run my VBA code
My Code opens a message box asking what I am searching for;
Then I transfer the message box data to the Query for the form;
The query runs and I count the records returned;
If the number of records is >0 the form opens, everything in my VBA code ends and all is well with the world
If the number of records is = 0, then a box pops up saying there are no records. when the box is closed, everything ends and the form never opens and the user is returned to the original switchboard that started everything.

Is that about right?
 
Last edited:

Users who are viewing this thread

Top Bottom