| Computers Forum Index » Computer - Databases - MS Access » Change the criteria of a query through code... |
|
Page 1 of 1 |
|
| Author |
Message |
| Arlette... |
 Posted: Sun Nov 01, 2009 12:51 pm |
|
|
|
Guest
|
I have a form which has 2 dropdowns that the user needs to select. One
is on Year and the other "Active or not". The 2nd dropdown has 3
options - Yes, No and All. The first dropdown and the 2nd work fine
together for the 2 options - Yes and No. However, when i select All
in the 2nd dropdown it doesnt show any records, though there are
records in the database. How do i work this out? |
|
|
| Back to top |
|
|
|
| Roger... |
| Posted: Sun Nov 01, 2009 1:49 pm |
|
|
|
Guest
|
On Nov 1, 5:51 am, Arlette <arlette.deso... at (no spam) gmail.com> wrote:
Quote: I have a form which has 2 dropdowns that the user needs to select. One
is on Year and the other "Active or not". The 2nd dropdown has 3
options - Yes, No and All. The first dropdown and the 2nd work fine
together for the 2 options - Yes and No. However, when i select All
in the 2nd dropdown it doesnt show any records, though there are
records in the database. How do i work this out?
what does your query look like when the 'all' option is selected |
|
|
| Back to top |
|
|
|
| Arlette... |
| Posted: Sun Nov 01, 2009 3:15 pm |
|
|
|
Guest
|
On Nov 1, 6:49 pm, Roger <lesperan... at (no spam) natpro.com> wrote:
Quote: On Nov 1, 5:51 am, Arlette <arlette.deso... at (no spam) gmail.com> wrote:
I have a form which has 2 dropdowns that the user needs to select. One
is on Year and the other "Active or not". The 2nd dropdown has 3
options - Yes, No and All. The first dropdown and the 2nd work fine
together for the 2 options - Yes and No. However, when i select All
in the 2nd dropdown it doesnt show any records, though there are
records in the database. How do i work this out?
what does your query look like when the 'all' option is selected
The subform which should reflect the data, shows a blank record. |
|
|
| Back to top |
|
|
|
| Arlette... |
| Posted: Sun Nov 01, 2009 5:04 pm |
|
|
|
Guest
|
| thanks i will try this out...what does NZ do? |
|
|
| Back to top |
|
|
|
| Salad... |
| Posted: Sun Nov 01, 2009 9:43 pm |
|
|
|
Guest
|
Arlette wrote:
Quote: I have a form which has 2 dropdowns that the user needs to select. One
is on Year and the other "Active or not". The 2nd dropdown has 3
options - Yes, No and All. The first dropdown and the 2nd work fine
together for the 2 options - Yes and No. However, when i select All
in the 2nd dropdown it doesnt show any records, though there are
records in the database. How do i work this out?
I might call a sub in the after update of each combbox
ResetFilter
Then (change names to refelect yours)
Private Sub ResetFilter
Dim strF As String
If Not IsNull(Me.Combo1) then
strF = "Year = " & Me.Combo1 & " And "
Endif
If NZ(Me.Combo2,"All") <> "All" then
strF = strF & "Active = '" & Me.combo2 & "' And "
Endif
'remove the word And at the end of the filter statement
If strF > "" then strF = left(strF,Len(strF)-5)
'for debug purposes only
msgbox "Filter is " & strF
'set the filter
Me.Filter = strF
Me.FilterOn = (strF > "")
End Sub
....or something like that |
|
|
| Back to top |
|
|
|
| Arlette... |
| Posted: Mon Nov 02, 2009 4:27 am |
|
|
|
Guest
|
I tried the code you gave above, but its not working. Let me try and
explain my issue again.
Generally, when you put a search criteria in the query field, if you
want all the records, you wont specify anything. But if you want just
the "Yes" or "No", you will specify accordingly. In the same way, when
the user selects "Yes" in the dropdown on the form, the query uses
that "Yes" as the criteria and provides the results. The same way it
works with "No". However, when the user selects "All" from the
dropdown, ideally the criteria shouldnt specify anything as i need all
the records. So how do i blank out the criteria in the query whenever
the user selects "All" in the form? |
|
|
| Back to top |
|
|
|
| Arlette... |
| Posted: Mon Nov 02, 2009 5:09 am |
|
|
|
Guest
|
The filter for the 1st dropdown works perfectly fine. However, when i
select the 2nd dropdown, there are problems. If i select Yes in the
2nd dropdown it works fine as well as "No". But when i select "All"
it doesnt give me all the records.
Are you attempting to filter records of the form? - Yes
Are you calling ResetFilter from the AfterUpdate events of the
comboboxes? - Yes
Have you changed the field names in ResetFilter to reflect yours? -
Yes
Did you leave in the msgbox line to see the filter's value? IOW,
uncomment it when debugging, comment out when it works. - Yes
Does your code compile? - I dont think i need to have it compile. It
works fine otherwise. Only when i select "All" in the 2nd dropdown, i
get a blank sub-form. |
|
|
| Back to top |
|
|
|
| Salad... |
| Posted: Mon Nov 02, 2009 6:15 am |
|
|
|
Guest
|
Arlette wrote:
Quote: thanks i will try this out...what does NZ do?
From help:
"Nz Function
You can use the Nz function to return zero, a zero-length string (" "),
or another specified value when a Variant is Null. For example, you can
use this function to convert a Null value to another value and prevent
it from propagating through an expression."
If you have a combo box that displays stuff from a table; 1st col an
ID/key and the second column the data to display and you clear out the
data the result is null. You might want to compare a value to a value
so NZ might be the solution.
You can also look at the IsNull function as well. |
|
|
| Back to top |
|
|
|
| Salad... |
| Posted: Mon Nov 02, 2009 6:15 am |
|
|
|
Guest
|
Arlette wrote:
Quote: I tried the code you gave above, but its not working. Let me try and
explain my issue again.
Generally, when you put a search criteria in the query field, if you
want all the records, you wont specify anything. But if you want just
the "Yes" or "No", you will specify accordingly. In the same way, when
the user selects "Yes" in the dropdown on the form, the query uses
that "Yes" as the criteria and provides the results. The same way it
works with "No". However, when the user selects "All" from the
dropdown, ideally the criteria shouldnt specify anything as i need all
the records. So how do i blank out the criteria in the query whenever
the user selects "All" in the form?
Are you attempting to filter records of the form?
Are you calling ResetFilter from the AfterUpdate events of the comboboxes?
Have you changed the field names in ResetFilter to reflect yours?
Did you leave in the msgbox line to see the filter's value? IOW,
uncomment it when debugging, comment out when it works.
Does your code compile?
I don't know what you mean when you say "its not working". Are you
getting errors? Are you passing Strings when you should pass numerics
or visa versa? Do you know what the filter is that is being set (via
msgbox line)?
Put/Insert the word Stop as the first line in the ResetFilter sub and
step thru the code line by line and see what logic is taken.
It's hard to say what the problem is with little info to operate on. |
|
|
| Back to top |
|
|
|
| Salad... |
| Posted: Mon Nov 02, 2009 9:46 pm |
|
|
|
Guest
|
Arlette wrote:
Quote: The filter for the 1st dropdown works perfectly fine. However, when i
select the 2nd dropdown, there are problems. If i select Yes in the
2nd dropdown it works fine as well as "No". But when i select "All"
it doesnt give me all the records.
Are you attempting to filter records of the form? - Yes
Are you calling ResetFilter from the AfterUpdate events of the
comboboxes? - Yes
Have you changed the field names in ResetFilter to reflect yours? -
Yes
Did you leave in the msgbox line to see the filter's value? IOW,
uncomment it when debugging, comment out when it works. - Yes
What is the output of strF? You can change
msgbox strF
to
debug.print strF
and get the value from the immediate window in a code module. Providing
useful data to a responder is helpful.
It should look something like
Year = 2009 And Active = 'No'
if you selected both a year number and No (it may need to be 0 for false
and -1 for true if Active is a yes/no field.
And it should look like
Active = 'No'
it you cleared out the year
and it should look like
Year = 2009
if a year and and All was selected
Is Active a Yes/No field (true/false)? I might have a 2 column combo
then with the bound column of 1 with a width of 0 and the description
(yes, no, all) the second column with a width of 1". The values could
be sothing like
1;"All";-1;"Yes";0;"No"
as a value list.
Please don't snip replies since a responder might need to go back to
prior messages to retrieve or review that which you snipped.
Quote:
Does your code compile? - I dont think i need to have it compile. It
works fine otherwise. Only when i select "All" in the 2nd dropdown, i
get a blank sub-form. |
|
|
| Back to top |
|
|
|
|
