Refreshing form with all records

[Tony Williams]

I have a form that is based on a table. The form has a command button that
when clicked runs a query that populates the form with the results. I have
another command button which when I press it I want it to refresh the form
with all the records.
Can someone help me with some code to do that?
I'm a VBA novice (but learning)so please treat me with care

 

[Jeff Boyce]

Tony

"... with the results ..." ??of what?? the query? what does the query do?
" ... with all the records..." so the query shows some records (like a
filter does) and the other button shows them all?

Here's another approach to consider...

If your form is "loaded" with either all the table's records or some subset,
it can be slow to load.

Instead, consider using an unbound combobox in the form's header to allow
you/your user to select a single record for display, then in the AfterUpdate
event of that combobox, requery the form. Base the form on a query that
looks to the form's combobox for its selection criterion.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tony Williams]

Sorry jeff a little more explanation. The form is a search form which
initially shows some of the fields for all the records in the table in a
Continuous Forms format. There is a text box that the user inputs all or part
of any one of two fields and then the onclick on the command button runs a
query which filters the records that contain the string the user has input.
They can then view an individual record by selecting it and clicking another
buttton to open a form which shows all the fields for that record. However if
the user decides they want to view all the records rather than select one
from the query results then they click a button which says "Show all" and
the form is populated with all the records. It's that last part that I want
the code for. I've tried
Me.RecordSource = "Select * FROM [tblname]" (where tblname is the name of my
table)
but that only shows the first record in the table whereas I want them all.

I hope that explains what I'm trying to do. It sounds complicated when I
know but in practice it's quite user friendly.

Thanks
Tony

 

[Jeff Boyce]

Tony

If this were mine, I'd add a breakpoint in the code so I could step through
each line to see what is/isn't happening.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry jeff a little more explanation. The form is a search form which
initially shows some of the fields for all the records in the table in a
Continuous Forms format. There is a text box that the user inputs all or
part
of any one of two fields and then the onclick on the command button runs a
query which filters the records that contain the string the user has
input.
They can then view an individual record by selecting it and clicking
another
buttton to open a form which shows all the fields for that record. However
if
the user decides they want to view all the records rather than select one
from the query results then they click a button which says "Show all" and
the form is populated with all the records. It's that last part that I
want
the code for. I've tried
Me.RecordSource = "Select * FROM [tblname]" (where tblname is the name of
my
table)
but that only shows the first record in the table whereas I want them all.

I hope that explains what I'm trying to do. It sounds complicated when I
know but in practice it's quite user friendly.

Thanks
Tony

Tony

"... with the results ..." ??of what?? the query? what does the query
do?
" ... with all the records..." so the query shows some records (like a
filter does) and the other button shows them all?

Here's another approach to consider...

If your form is "loaded" with either all the table's records or some
subset,
it can be slow to load.

Instead, consider using an unbound combobox in the form's header to allow
you/your user to select a single record for display, then in the
AfterUpdate
event of that combobox, requery the form. Base the form on a query that
looks to the form's combobox for its selection criterion.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tony Williams]

Hi Jeff. Partial success. I changed the code to select from a query which I
created that included all the fields that appeared on the form I was
refreshing and that worked but took a while as there are over 250,000
records. Don't know why the code worked with a query but not straight from
the table.
Anyway as I said I have a partial if not quick solution.
Cheers
Tony

Tony

If this were mine, I'd add a breakpoint in the code so I could step through
each line to see what is/isn't happening.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry jeff a little more explanation. The form is a search form which
initially shows some of the fields for all the records in the table in a
Continuous Forms format. There is a text box that the user inputs all or
part
of any one of two fields and then the onclick on the command button runs a
query which filters the records that contain the string the user has
input.
They can then view an individual record by selecting it and clicking
another
buttton to open a form which shows all the fields for that record. However
if
the user decides they want to view all the records rather than select one
from the query results then they click a button which says "Show all" and
the form is populated with all the records. It's that last part that I
want
the code for. I've tried
Me.RecordSource = "Select * FROM [tblname]" (where tblname is the name of
my
table)
but that only shows the first record in the table whereas I want them all.

I hope that explains what I'm trying to do. It sounds complicated when I
know but in practice it's quite user friendly.

Thanks
Tony

Tony

"... with the results ..." ??of what?? the query? what does the query
do?
" ... with all the records..." so the query shows some records (like a
filter does) and the other button shows them all?

Here's another approach to consider...

If your form is "loaded" with either all the table's records or some
subset,
it can be slow to load.

Instead, consider using an unbound combobox in the form's header to allow
you/your user to select a single record for display, then in the
AfterUpdate
event of that combobox, requery the form. Base the form on a query that
looks to the form's combobox for its selection criterion.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have a form that is based on a table. The form has a command button
that
when clicked runs a query that populates the form with the results. I
have
another command button which when I press it I want it to refresh the
form
with all the records.
Can someone help me with some code to do that?
I'm a VBA novice (but learning)so please treat me with care