Form using multiple queries

[russdwright]

I have an Access file that has 18 different queries I created, all of which have been created to get a count of records.

I would like to setup a form that can be opened to see a "digest view" of all these numbers, but then clicking on the listed item would bring up the detailed report showing the breakdown of the count between each category.



Everything I have read says to create sub-forms, but every sub-form I create makes the entire form look big and clunky when I only need a little space for each data point.

Any and all input is greatly appreciated!

[davegri]

You can get a recordcount for a query with this function:

Public Function qCount(arg As String) As Long
    Dim qd As DAO.Recordset
    Set qd = CurrentDb.OpenRecordset(arg)
    qCount = qd.RecordCount
End Function

Then

sub Form_Load()
     txtBox1 = qCount("qName1")
     txtBox2 = qCount("qName2")
     .
     .
     txtBox18 = qCount("qName18")
end sub

The doubleClick event on the textboxes could be coded to open the query or open a form that displays the query contents.

[russdwright]

Do I create a blank form, add the text boxes then input the code in VBA to run on the entire form?

I don't want to use a DoubleClick event to show the counts; I'd rather have the summary appear immediately upon opening the form and have an option that, when selected, shows another set of information. I then want to setup code for when the heading for the query in question is clicked on, the report for that query loads to give more detailed information.

[davegri]

Do I create a blank form, add the text boxes then input the code in VBA to run on the entire form?

Yes. The counts will appear when you open the form.

The double click is to view any one of the 18 count details after the form shows the counts.

[russdwright]

OK, where do I place these functions in VBA? Do I put the Public Function in General and the Private Sub in the Form area?

I've done this for a few of the fields under that assumption and they all show "1" as the result.

[davegri]

You can put both in the form area.

they all show "1" as the result

Is that the correct sum? If not, add the two red lines.

Public Function qCount(arg As String) As Long
    Dim qd As DAO.Recordset
    Set qd = CurrentDb.OpenRecordset(arg)
    qd.movelast
    qd.movefirst 
    qCount = qd.RecordCount
End Function

[russdwright]

The change worked, but I am getting this when it runs (even though the counts successfully load):

Run-time error '3061':

Too few parameters. Expected 1.

I'm assuming I need to add something to the (arg) parenthetical, but I'm not sure what.

[davegri]

Check the query names for correct spelling that you are plugging in.

[russdwright]

The query and text box names are being copied directly from Access, so I have verified everything there

The highlighted line when I attempt to Debug is Set qd = CurrentDb.OpenRecordset(arg)and data stops loading after the 3rd text box.

I tried DCount instead and instead get a Run-time error ‘2471’. Again, when I debug, it is stopped at the third text box. I try using REM and every line beyond the second one brings back the error.

[davegri]

and data stops loading after the 3rd text box.

Then there's something wrong about the 4th query. The error indicated is an error in the query itself.
What happens when you try to run that query standalone?
Here's a code mod that will handle queries that return zero records:

Public Function qCount(arg As String) As Long
    Dim qd As DAO.Recordset
    Dim db As DAO.Database
    Set db = CurrentDb
    Set qd = db.OpenRecordset(arg)
    On Error Resume Next
    qd.MoveLast
    qd.MoveFirst
    On Error GoTo 0
    qCount = qd.RecordCount
    Set qd = Nothing
End Function

[russdwright]

I REM out everything except that query and the same thing happens. It also happens when I REM any other line except one, not including the first two queries.

Again, the Debug points to this line: Set qd = CurrentDb.OpenRecordset(arg)

[davegri]

I REM out everything except that query and the same thing happens.

Again, what happens when you run that query (and all the others) from the Navigation Pane? Does it run without errors and show you a datasheet of its results?

[russdwright]

OK, I double-checked the query in question and it turns out it was missing a field. I added the field and that resolved the issue.

[davegri]

and that resolved the issue.

Good deal. The DCount function that you mentioned should work as well.