Question about forms design (1 Viewer)

slharman1

Member
Local time
Today, 08:43
Joined
Mar 8, 2021
Messages
467
I have a form that list all of my estimates.
Question is:
Should I create a separate form to show all of the active estimates and open that form when I want only the active estimates OR
Should I modify the properties of the form to use a query that limits the list to only active forms?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:43
Joined
Aug 30, 2003
Messages
36,118
I'd use a single form and either manipulate the source or modify the filter property as desired. Having a form that displays all records can experience performance issues if you get a lot of records,
 

slharman1

Member
Local time
Today, 08:43
Joined
Mar 8, 2021
Messages
467
I'd use a single form and either manipulate the source or modify the filter property as desired. Having a form that displays all records can experience performance issues if you get a lot of records,
sometimes the user needs to see all but most of the time user needs active only.
2 forms and queries or one form and modify?
Not sure how to modify the query on opening the form
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:43
Joined
Aug 30, 2003
Messages
36,118
You can toggle the record source:

Me.RecordSource = "SELECT..."

or set a filter

Me.Filter = "Active = True"
Me.FilterOn = True
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:43
Joined
Jan 23, 2006
Messages
15,364
You could have a single form that defaults to show only ACTIVE records, and a button/checkbox or whatever that when clicked would show ALL records. You would also have some info for the user re the button/checkbox to show ALL.
 

slharman1

Member
Local time
Today, 08:43
Joined
Mar 8, 2021
Messages
467
You can toggle the record source:

Me.RecordSource = "SELECT..."

or set a filter

Me.Filter = "Active = True"
Me.FilterOn = True
Cool so multiple queries not multiple forms.
Thanks Pbaldy!
 

slharman1

Member
Local time
Today, 08:43
Joined
Mar 8, 2021
Messages
467
You could have a single form that defaults to show only ACTIVE records, and a button/checkbox or whatever that when clicked would show ALL records. You would also have some info for the user re the button/checkbox to show ALL.
Thanks
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:43
Joined
Aug 30, 2003
Messages
36,118
Happy to help. You can use saved queries or SQL in VBA.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:43
Joined
Feb 19, 2002
Messages
42,981
If you are worried about slowness with lots of records, then using form filters doesn't solve that problem since the entire recordsource is loaded into memory after being transferred across the LAN. Add an unbound option group to the header of the form. Make the options Active and all. use 1 and 2 for the values.

Change the select clause to reference this option group.

Select ...
From ...
Where IIf(Forms!yourform!optActive = 2, True, False) Or ActiveYN = True;
If the option group = 2, all records will be returned otherwise only active records will be returned.

In the AfterUpdate event of the OptionGroup, requery the form to apply the new criteria.
 

Sun_Force

Active member
Local time
Today, 22:43
Joined
Aug 29, 2020
Messages
396
If you are worried about slowness with lots of records, then using form filters doesn't solve that problem since the entire recordsource is loaded into memory after being transferred across the LAN.

@Pat Hartman
How about when opening a form with docmd and set a condition to its WHERE argument? Is the entire recordsource is transferred too?
DoCmd.OpenForm "MyForm",,,"Active=True"

Active=True is set as Where condition not Filter name.

Thanks.
 

slharman1

Member
Local time
Today, 08:43
Joined
Mar 8, 2021
Messages
467
If you are worried about slowness with lots of records, then using form filters doesn't solve that problem since the entire recordsource is loaded into memory after being transferred across the LAN. Add an unbound option group to the header of the form. Make the options Active and all. use 1 and 2 for the values.

Change the select clause to reference this option group.

Select ...
From ...
Where IIf(Forms!yourform!optActive = 2, True, False) Or ActiveYN = True;
If the option group = 2, all records will be returned otherwise only active records will be returned.

In the AfterUpdate event of the OptionGroup, requery the form to apply the new criteria.
Thanks Pat. Really I was just looking to not have so many forms in the FE. It’s hard for a newb like me to keep track of everything. My naming convention is getting better though. Thank all for all the help, I think I am actually learning something.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:43
Joined
Feb 19, 2002
Messages
42,981
How about when opening a form with docmd and set a condition to its WHERE argument?
The criteria in the docmd is added to whatever criteria already exists in the query and only selected records are returned.
Thanks Pat.
You're welcome. The problem with duplicating forms, especially update forms is that you have the same code in multiple places and you have to keep it synchronized so never use separate forms for Add and Update or for Active and Inactive, etc. You might need a little extra code but that's OK. For example You might need code in the BeforeUpdate event of the form to prevent inactive record s from being updated directly. You would add a button to reactivate them before allowing an update.
 

slharman1

Member
Local time
Today, 08:43
Joined
Mar 8, 2021
Messages
467
The criteria in the docmd is added to whatever criteria already exists in the query and only selected records are returned.

You're welcome. The problem with duplicating forms, especially update forms is that you have the same code in multiple places and you have to keep it synchronized so never use separate forms for Add and Update or for Active and Inactive, etc. You might need a little extra code but that's OK. For example You might need code in the BeforeUpdate event of the form to prevent inactive record s from being updated directly. You would add a button to reactivate them before allowing an update.
Thanks Pat. Been doing some cleanup on my db today. Created queries to use and change the record source with a command button, but I always for get about the option box, is that a better approach?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:43
Joined
Feb 19, 2002
Messages
42,981
You're welcome. I use various techniques. When the criteria is on the actual edit form, it is usually a combo or maybe an option group but I probably wouldn't use just the option group because it diesn't filter the records enough. Talk to your users. When they go to a form, what do they know? I want to update xyz company's address so you have a combo that lets the user select a single company. it is actually rare that a user just wants to randomly scroll through records until he finds what he is looking for.

If the user needs multiple ways to slice and dice, I will probably make a search form. The search form will open the edit form directly if only a single record is found or it will open a list form that can be filtered further . Then they double-click on a column in the list form to go to a specific record.
search.PNG
 

Users who are viewing this thread

Top Bottom