Solved Show All Records on a Form in Access VBA (1 Viewer)

kengooch

Member
Local time
Today, 12:01
Joined
Feb 29, 2012
Messages
137
At the risk of muddying the waters at an advantage stage of the discussion, I need to comment on the fundamental design approach itself.

What you are doing is binding a table (or query based only on that table) to the form and loading the entire recordset from that table whenever the form opens. After it's open, you intend to apply filters to dump all but one record from the current state of that recordset. Then, remove the filter and reload all of the records again, only to apply a different filter.

Lots and lots and lots of records coming and going in that approach.

In fact, Access is pretty good at it and as long as your tables have few records, it's not a problem per se. You don't see the I/O cost of reading and dumping records over and over.

That said, there's a better way, especially as the number of records involved grows over time. And if you ever move to a remotely hosted server based database such as SQL Server, you will be hit with a huge performance penalty.

It's more efficient and less costly to load ONE RECORD AT A TIME unless you REALLY, REALLY have to be able to scroll dozens, or hundreds, or thousands of records in the form. Not often is that practical, especially when recordsets grow over a couple dozen records at a time. People don't need or want that ability.

The method I recommend (and I think most others would, but we'll see....) is to combine a combo box or list box on the form, in the form's header section along with a query as the form's recordsource with a WHERE clause to limit the records to one.

The combo box has the entire set of records as its rowsource, using two fields usually. Those are the Primary Key for the table and a user-readable field that can identify which record to select.

SELECT PrimaryKey, LastName & ", " & First As SelectableName
FROM tblPerson
ORDER BY LastName & ", " & First

Now, the form's recordsource is something like:

SELECT *
FROM tblPerson
WHERE tblPerson.PrimaryKey = Forms!frmPerson.cboSelectaPerson

That way one and only one record ever gets loaded into the form at a time. It still has the combo box with many records which can be a factor. If so, there are other ways to address that, though.

In order to allow the user to take a coffee break while the form loads a couple thousand people records, you can do it this way.

SELECT PrimaryKey, LastName & ", " & First As SelectableName
FROM tblPerson
UNION
SELECT 0, " <Show Me Everything>" As SelectableName
FROM TblPerson
ORDER BY SelectableName

And the form's recordsource can be:

SELECT *
FROM tblPerson
WHERE tblPerson.PrimaryKey = Iif(Forms!frmPerson.cboSelectaPerson = 0, <>0, Forms!frmPerson.cboSelectaPerson)

The assumption behind this particular syntax is that "PrimaryKey" is an Autonumber and that it will, therefore, never have a value of 0.
Thanks for this information and the coding. I actually have two forms, the data entry form and then the log book form. The data entry form does exactly what you say, it allows me to look up an individual record and/or add and delete records. But we also need to see the whole log book and then I use multi-select list boxes to allow the user to quickly isolate all records based upon 5 different choices, with multiple options per each choice. So or example I can see all patients from the 3rd quarter of 2021, in Histopathology, who were diagnosed with Colon Cance and Rectal Cancer, and whose Physician was Dr. Smith and the Pathologist that did the review was Dr. Jones. I just needed to be able to clear the filters and reset the select boxes. I was able to find a solution and it all works very nicely now.
 

GPGeorge

Grover Park George
Local time
Today, 12:01
Joined
Nov 25, 2004
Messages
1,776
Thanks for this information and the coding. I actually have two forms, the data entry form and then the log book form. The data entry form does exactly what you say, it allows me to look up an individual record and/or add and delete records. But we also need to see the whole log book and then I use multi-select list boxes to allow the user to quickly isolate all records based upon 5 different choices, with multiple options per each choice. So or example I can see all patients from the 3rd quarter of 2021, in Histopathology, who were diagnosed with Colon Cance and Rectal Cancer, and whose Physician was Dr. Smith and the Pathologist that did the review was Dr. Jones. I just needed to be able to clear the filters and reset the select boxes. I was able to find a solution and it all works very nicely now.
To be clear, loading the entire recordset is not the best practice for forms. You are indeed applying filters, but only after dragging every record from the table into the form's recordsource. This isn't such a problem with smaller Access relational database applications, but you'll find it to be a problem as the number of records grows, or if you upsize to a server based database back end.

Here's the alternative:

SELECT Field1, Field2, Field3, Field4
FROM tblYourTableNameGoesHERE
WHERE 1=0

That loads no records at all initially; it's very fast.

Then, as you apply filters for the various attributes you mention: time period, diagnoses, physicians, etc. you rewrite the SQL, or apply new filters to it, to allow the recordset to GROW, not SHRINK.

SELECT Field1, Field2, Field3, Field4
FROM tblYourTableNameGoesHERE
WHERE TimePeriod = "3rd Quarter, 2021"

And so on. This approach ensures loading times are as quick as possible, both initially and as you conduct additional searches. There are never more records in the form's recordsource than allowed by the widest filter applied.

And, if you really, really, want the user to see all of the records, the filter can be changed to

SELECT Field1, Field2, Field3, Field4
FROM tblYourTableNameGoesHERE
WHERE 1=1
 

kengooch

Member
Local time
Today, 12:01
Joined
Feb 29, 2012
Messages
137
To be clear, loading the entire recordset is not the best practice for forms. You are indeed applying filters, but only after dragging every record from the table into the form's recordsource. This isn't such a problem with smaller Access relational database applications, but you'll find it to be a problem as the number of records grows, or if you upsize to a server based database back end.

Here's the alternative:

SELECT Field1, Field2, Field3, Field4
FROM tblYourTableNameGoesHERE
WHERE 1=0

That loads no records at all initially; it's very fast.

Then, as you apply filters for the various attributes you mention: time period, diagnoses, physicians, etc. you rewrite the SQL, or apply new filters to it, to allow the recordset to GROW, not SHRINK.

SELECT Field1, Field2, Field3, Field4
FROM tblYourTableNameGoesHERE
WHERE TimePeriod = "3rd Quarter, 2021"

And so on. This approach ensures loading times are as quick as possible, both initially and as you conduct additional searches. There are never more records in the form's recordsource than allowed by the widest filter applied.

And, if you really, really, want the user to see all of the records, the filter can be changed to

SELECT Field1, Field2, Field3, Field4
FROM tblYourTableNameGoesHERE
WHERE 1=1
Wow... you learn something new every day!! Thanks so much!!!
 

Users who are viewing this thread

Top Bottom