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

kengooch

Member
Local time
Yesterday, 21:15
Joined
Feb 29, 2012
Messages
101
I have a form and when I open it, I set the value of my lookup field to a value that intentionally has no records to force the user to click the ComboBox and choose their Department. The line of code being used to accomplish this is:
Code:
    DoCmd.SearchForRecord , "", acFirst, "[tDeptAbbrv] = " & "'Select'"
I would also like to place a button on the form so that the user can quickly and easily clear the current selection and actually then show all of the records in the underlying query.

I created a button called "bShowAll" and added the following code, but it leaves the form with no entries.
Code:
    .SearchForRecord , "", acFirst, "[tDeptAbbrv] = " & "'*'"

I thought that the * would allow it to show all records, but it doesn't work.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:15
Joined
May 7, 2009
Messages
16,393
can you use:

docmd.GoToRecord,,acFirst
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:15
Joined
Sep 21, 2011
Messages
10,531
Doesn't that syntax just set a filter to the form?
Try setting the filter to nothing?

Edit: I was thinking of opening a form with that syntax, so please ignore.
 
Last edited:

SHANEMAC51

Active member
Local time
Today, 07:15
Joined
Jan 28, 2022
Messages
288
The line of code being used to accomplish this is:
this string searches for the first string with the value of the word Select

Code:
DoCmd.SearchForRecord , "", acFirst, "[tDeptAbbrv] = " & "'Select'"

you will not see the second and further, 
as well as the rows with the value Select 1 or Select2

to see all the lines with the word Select*, you need to use another command

me.filter= "[tDeptAbbrv] = " & "'Select*'"
me.filteron=true
even to see everything , but by another word, for example print*

me.filter= "[tDeptAbbrv] = '" & me.field_search & "*''"
 

LarryE

Active member
Local time
Yesterday, 21:15
Joined
Aug 18, 2021
Messages
159
DoCmd.ShowAllRecords will show all records.
 

bastanu

AWF VIP
Local time
Yesterday, 21:15
Joined
Apr 13, 2010
Messages
1,173
The wild characters ("*","?") are used with the Like not = operator:
.SearchForRecord , "", acFirst, "[tDeptAbbrv] Like '*'"
 

kengooch

Member
Local time
Yesterday, 21:15
Joined
Feb 29, 2012
Messages
101
Maybe I should approach, showing this from another VBA perspective? Could I change the open form options and use a Filter and then turn that filter on or off?
 

Minty

AWF VIP
Local time
Today, 05:15
Joined
Jul 26, 2013
Messages
9,286
If you use a filter or where clause of Where 1=0 during open you will get no records and a quick form load.
You can then apply a new recordsource in the form after a control update to only return the records you want. Something like

Me.RecordSource = "SELECT * FROM YourTable WHERE MyField_ID = " & Me.LookupCombo
 

GPGeorge

Grover Park George
Local time
Yesterday, 21:15
Joined
Nov 25, 2004
Messages
665
Maybe I should approach, showing this from another VBA perspective? Could I change the open form options and use a Filter and then turn that filter on or off?
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.
 

LarryE

Active member
Local time
Yesterday, 21:15
Joined
Aug 18, 2021
Messages
159
This will set your filter to nothing:
Code:
Me.Form.Filter = ""
Me.Form.FilterOn = True
Me.Requery

This is supposed to remove all filters and show all records:
DoCmd.RunCommand acCmdRemoveAllFilters
DoCmd.ShowAllRecords
 

Eugene-LS

Registered User.
Local time
Today, 07:15
Joined
Dec 7, 2018
Messages
351
This will set your filter to nothing:
I completely agree with you, but do we really need to store it?
Then one line is enough:
Me.FilterOn = False 'True

Me.Form.Filter = ""
Form statment is by default ... so:
Code:
Me.Filter = ""
- is quite enough
Me.Form.FilterOn = True
After comand : Me.Form.Filter = "" ???
May be: Me.Form.FilterOn = False
 

LarryE

Active member
Local time
Yesterday, 21:15
Joined
Aug 18, 2021
Messages
159
Well, you do need to actually activate the filter with FilterOn=True first, otherwise the filter will not activate. If you wish to FilterOn=False after that then go ahead. But this may not be needed if the other commands work.
 

Eugene-LS

Registered User.
Local time
Today, 07:15
Joined
Dec 7, 2018
Messages
351
If you wish to FilterOn=False after that then go ahead. But this may not be needed if the other commands work.
Commands:
Code:
        DoCmd.RunCommand acCmdRemoveAllFilters
        DoCmd.ShowAllRecords
- aren't they full equivalent of lines:
Code:
        Me.Filter = ""
        Me.FilterOn = False
???
 

LarryE

Active member
Local time
Yesterday, 21:15
Joined
Aug 18, 2021
Messages
159
Supposed to be but I don't know why the OP said DoCmd.ShowAllRecords did not work either. It has always worked for me to remove filters and show all the records on a form.
 

Eugene-LS

Registered User.
Local time
Today, 07:15
Joined
Dec 7, 2018
Messages
351
Supposed to be but I don't know why the OP said DoCmd.ShowAllRecords did not work either.
It works only with Active (with has focus) form ...
But:
Code:
        Forms("FrmName").Filter = ""
        Forms("FrmName").FilterOn = False
We able apply to ANY open (even invisible) form ...
 
Last edited:

Users who are viewing this thread

Top Bottom