No Records Found Popup

[jesusoneez]

I have a database that holds hardware data. It's working fine, but there's a slight itch I'd like to scratch and I'm not sure how.

Very simply, I have a table listing printers. I have a search form with combo boxes called 'cboRoom' and 'cboDepartment'. The room and department fields in the printers table are lookups to a room table and a departments table respectivley.

The search form works fine by using the combo boxes to select a room and/or department, click search, and a query is run using the combo box selections as parameters. The query is also made to show all records if the combo boxes contain null. A form is then displayed with the query results.

Say for example, we have a room called B24. If B24 is selected in the combo box and the query run, I want a popup to appear that says 'No data with these search parameters' if there are no records containing 'B24'.

The search button (which is actually a label for design reasons) on the search form currently does nothing more than this;

Private Sub lblSearch_Click()
DoCmd.OpenForm "frmPrinters", acNormal
DoCmd.Close acForm, "frmPrinterSearch"
End Sub

'frmPrinters' is obviously using the query (qryPrinterSearch) as its recordsource. Obviously, the popup needs to appear as soon as the query has been run, but I'm not sure what code to use or where to put it...

I know I need some sort of (pseudo)

If frmPrinters.cboRooms Is Null
MsgBox "No Data"
Close frmPrinters
Open frmPrinterSearch
End If

Something like that. But obviously I need to do it for both cboRooms and cboDepartments, after the form has attempted to populate itself with data from the query (otherwise it return null values anyway I guess).

Any help with the code and where to put it much appreciated.

Regards,

Steve Swallow

 

[neileg]

Use DCount() to count the number of records before your query runs. If the number is zero, then there are no records in the dataset.

 

[jesusoneez]

That sounds a bit over complicated to me, but then I don't know what I'm doing half the time. I ended up getting round it by shoving this in the load event of the results display form;

Private Sub Form_Load()
    If IsNull(Me.cboRoomID) Then
        MsgBox "No Records", vbInformation
        Docmd.close acform, "frmPrinters"
    End If
End Sub

This works fine to an extent. The search button on the search form has the following code;

Private Sub lblSearch_Click()
    DoCmd.OpenForm "frmPrinters", acNormal
    DoCmd.Close acForm, "frmPrinterSearch"
End Sub

As you can see it's all pretty simple. I run in to problems with two things.

1. If I enter something into cboRoomID on the search form, and no records are returned, I should then be able to close the messagebox leaving the search form there. But it disappears. I realise that's because of DoCmd.Close acForm, "frmPrinterSearch", but if I take that out and perform a valid search, it obviously doesn't go away when a search produces results.

2. If I change my code so the search form stays there on a bad search, if I then remove my options from the combo boxes leaving them blank, then hit search, it should return all records, but it comes up with the msgbox saying there are no records. I recognise that this is because the combo box values are no longer null, and I've tried requery in various places without any luck.

If your DCount() idea is going to be simpler then I'd like to give it a go, but as you can see, my VBA is basic at best and I'll need a little more help.

Thanks,

Steve

 

[neileg]

Have a look at Access help on DCount(). I think you'll find it pretty straightforward.

I don't do complicated VBA, this isn't my day job!