Filters vs. Queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

At the moment my form is filtered based on the value of a combobox. Am I
right in assuming that all the records from the query the form is based on
are loaded and then filtered? As the database grows (at a rate of roughly 300
records per week, so not particularly quickly), isn't this form going to
start to open very slowly?

Given the fact that the form is based on a query, how would I get it to load
no records at all until the combobox has a value, then only show those
records that have that combobox value? I've tried putting
[Forms]![MyForm]![MyUnboundControl] in the appropriate query criteria, then
requerying the form in the AfterUpdate event of the combobox but no records
are displayed (I also tried the OnChange event).

Thanks

Dave
 
Hello "David M C".

David M C said:
At the moment my form is filtered based on the value of a combobox.
Am I right in assuming that all the records from the query the form is
based on are loaded and then filtered? As the database grows
(at a rate of roughly 300 records per week, so not particularly quickly),
isn't this form going to start to open very slowly?
Click to expand...

I think so, too.
Given the fact that the form is based on a query, how would I get it
to load no records at all until the combobox has a value, then only
show those records that have that combobox value?
I've tried putting [Forms]![MyForm]![MyUnboundControl] in the
appropriate query criteria, then requerying the form in the AfterUpdate
event of the combobox but no records are displayed (I also tried the
OnChange event).
Click to expand...

Sounds correct. Check the record source of the form, check if a filter is
still active, check if the criterion is used for the correct query field
(the one corresponding to bound column of the combo box). Don't use the
OnChange event, AfterUpdate is ok.
 
The form is based on a query.

qryWorks:

TaskID
TaskName
TaskTypeID
Price

Here's the SQL:

SELECT tblTask.TaskID, tblTask.TaskName, tblTask.TaskTypeID, tblTask.Price
FROM tblTask;

I have several unbound combobox's that are used to select the appropriate
TaskID (I know this works because the filter I'm currently using is based on
the same set of combobox's). The form has a subform that shows the "many"
side of a 1-to-many relationship between tblTask and tblTransaction. This
subform is also based on a query:

SELECT tblTransaction.TaskID, tblTransaction.TransactionID,
tblTransaction.TransactionDate, tblTransaction.TransactionTypeID,
tblTransaction.Details, tblTransaction.EmployeeID,
tblTransaction.PercentComp, tblTask.Price,
CCur([tblTask.Price]*([PercentComp]/100)) AS LineTotal
FROM tblTask INNER JOIN tblTransaction ON tblTask.TaskID =
tblTransaction.TaskID;

I disabled the filter then tried changing the query the main form is based
on to:

SELECT tblTask.TaskID, tblTask.TaskName, tblTask.TaskTypeID, tblTask.Price
FROM tblTask
WHERE (((tblTask.TaskID)=[Forms]![frmWorks]![SelectTaskID]));

I added Me.Requery to the AfterUpdate event of the appropriate combobox. In
my mind, this is all you should need to do. However it doesn't work. The
query doesn't appear to return any records. Remember, the form design works
perfectly well when using a filter.

Ideas?

Thanks

Dave

Wolfgang Kais said:
Hello "David M C".

David M C said:
At the moment my form is filtered based on the value of a combobox.
Am I right in assuming that all the records from the query the form is
based on are loaded and then filtered? As the database grows
(at a rate of roughly 300 records per week, so not particularly quickly),
isn't this form going to start to open very slowly?
Click to expand...

I think so, too.
Given the fact that the form is based on a query, how would I get it
to load no records at all until the combobox has a value, then only
show those records that have that combobox value?
I've tried putting [Forms]![MyForm]![MyUnboundControl] in the
appropriate query criteria, then requerying the form in the AfterUpdate
event of the combobox but no records are displayed (I also tried the
OnChange event).
Click to expand...

Sounds correct. Check the record source of the form, check if a filter is
still active, check if the criterion is used for the correct query field
(the one corresponding to bound column of the combo box). Don't use the
OnChange event, AfterUpdate is ok.
Click to expand...
 
An extra bit of information. If I run the query without the form open, and
enter the parameter it asks for (because it can't find the control), the
expected records are returned. It would appear there is a problem getting the
value from the combobox to the query. Note, I do not have this problem using
filters.

Dave

David M C said:
The form is based on a query.

qryWorks:

TaskID
TaskName
TaskTypeID
Price

Here's the SQL:

SELECT tblTask.TaskID, tblTask.TaskName, tblTask.TaskTypeID, tblTask.Price
FROM tblTask;

I have several unbound combobox's that are used to select the appropriate
TaskID (I know this works because the filter I'm currently using is based on
the same set of combobox's). The form has a subform that shows the "many"
side of a 1-to-many relationship between tblTask and tblTransaction. This
subform is also based on a query:

SELECT tblTransaction.TaskID, tblTransaction.TransactionID,
tblTransaction.TransactionDate, tblTransaction.TransactionTypeID,
tblTransaction.Details, tblTransaction.EmployeeID,
tblTransaction.PercentComp, tblTask.Price,
CCur([tblTask.Price]*([PercentComp]/100)) AS LineTotal
FROM tblTask INNER JOIN tblTransaction ON tblTask.TaskID =
tblTransaction.TaskID;

I disabled the filter then tried changing the query the main form is based
on to:

SELECT tblTask.TaskID, tblTask.TaskName, tblTask.TaskTypeID, tblTask.Price
FROM tblTask
WHERE (((tblTask.TaskID)=[Forms]![frmWorks]![SelectTaskID]));

I added Me.Requery to the AfterUpdate event of the appropriate combobox. In
my mind, this is all you should need to do. However it doesn't work. The
query doesn't appear to return any records. Remember, the form design works
perfectly well when using a filter.

Ideas?

Thanks

Dave

Wolfgang Kais said:
Hello "David M C".

David M C said:
At the moment my form is filtered based on the value of a combobox.
Am I right in assuming that all the records from the query the form is
based on are loaded and then filtered? As the database grows
(at a rate of roughly 300 records per week, so not particularly quickly),
isn't this form going to start to open very slowly?
Click to expand...

I think so, too.
Given the fact that the form is based on a query, how would I get it
to load no records at all until the combobox has a value, then only
show those records that have that combobox value?
I've tried putting [Forms]![MyForm]![MyUnboundControl] in the
appropriate query criteria, then requerying the form in the AfterUpdate
event of the combobox but no records are displayed (I also tried the
OnChange event).
Click to expand...

Sounds correct. Check the record source of the form, check if a filter is
still active, check if the criterion is used for the correct query field
(the one corresponding to bound column of the combo box). Don't use the
OnChange event, AfterUpdate is ok.
Click to expand...
Click to expand...
 
Figured it out. It was the simplest most basic mistake. The control is called
SelectTask, not SelectTaskID. Shouldn't this have returned some type of
error, since the control wouldn't have been found on the form?

Dave

David M C said:
An extra bit of information. If I run the query without the form open, and
enter the parameter it asks for (because it can't find the control), the
expected records are returned. It would appear there is a problem getting the
value from the combobox to the query. Note, I do not have this problem using
filters.

Dave

David M C said:
The form is based on a query.

qryWorks:

TaskID
TaskName
TaskTypeID
Price

Here's the SQL:

SELECT tblTask.TaskID, tblTask.TaskName, tblTask.TaskTypeID, tblTask.Price
FROM tblTask;

I have several unbound combobox's that are used to select the appropriate
TaskID (I know this works because the filter I'm currently using is based on
the same set of combobox's). The form has a subform that shows the "many"
side of a 1-to-many relationship between tblTask and tblTransaction. This
subform is also based on a query:

SELECT tblTransaction.TaskID, tblTransaction.TransactionID,
tblTransaction.TransactionDate, tblTransaction.TransactionTypeID,
tblTransaction.Details, tblTransaction.EmployeeID,
tblTransaction.PercentComp, tblTask.Price,
CCur([tblTask.Price]*([PercentComp]/100)) AS LineTotal
FROM tblTask INNER JOIN tblTransaction ON tblTask.TaskID =
tblTransaction.TaskID;

I disabled the filter then tried changing the query the main form is based
on to:

SELECT tblTask.TaskID, tblTask.TaskName, tblTask.TaskTypeID, tblTask.Price
FROM tblTask
WHERE (((tblTask.TaskID)=[Forms]![frmWorks]![SelectTaskID]));

I added Me.Requery to the AfterUpdate event of the appropriate combobox. In
my mind, this is all you should need to do. However it doesn't work. The
query doesn't appear to return any records. Remember, the form design works
perfectly well when using a filter.

Ideas?

Thanks

Dave

Wolfgang Kais said:
Hello "David M C".

:
At the moment my form is filtered based on the value of a combobox.
Am I right in assuming that all the records from the query the form is
based on are loaded and then filtered? As the database grows
(at a rate of roughly 300 records per week, so not particularly quickly),
isn't this form going to start to open very slowly?

I think so, too.

Given the fact that the form is based on a query, how would I get it
to load no records at all until the combobox has a value, then only
show those records that have that combobox value?
I've tried putting [Forms]![MyForm]![MyUnboundControl] in the
appropriate query criteria, then requerying the form in the AfterUpdate
event of the combobox but no records are displayed (I also tried the
OnChange event).

Sounds correct. Check the record source of the form, check if a filter is
still active, check if the criterion is used for the correct query field
(the one corresponding to bound column of the combo box). Don't use the
OnChange event, AfterUpdate is ok.
Click to expand...
Click to expand...
Click to expand...
 
David M C said:
Figured it out. It was the simplest most basic mistake. The control is called
SelectTask, not SelectTaskID. Shouldn't this have returned some type of
error, since the control wouldn't have been found on the form?
Click to expand...

No, the fact that it asked for SelectTaskID should have alerted you to that fact.
If it gave an error instead, you'd never be able to have parameter queries.

Tom Lake
 
You misunderstand me. When this was used as the parameter to the query, and
the form was open, Access didn't blink an eye (no error messages), it just
didn't return any records. I was only asked for a parameter when the form
wasn't open (which is to be expected).

Dave
 
Yes, it's like that for me too. It might be because the
query is parsed before the form becomes available, or
it might just be that some kinds of errors are lost
as you open the form. Have a look in the DAO errors
collection to see if there is anything there.

BTW, I think if you have a filter saved with a form,
it is applied before the first data query. Then
the form finishes loading, then the data is re-queried
if the filter has changed (as it must if the filter
refers to a control on the form).

(david)
 
Back
Top