Filter Data based on Form

  • Thread starter Thread starter mattc66 via AccessMonster.com
  • Start date Start date
M

mattc66 via AccessMonster.com

Hi All,

I have a form that is based on a query. I also have a unbound list box that
the user can select a location from. The Locations are Seattle, Bellevue and
Redmond.

When the user selects a location I would like the form to only display the
records with those locations. This is a Single Form not a datasheet view and
this is not a subform.

Thanks
Matt
 
I would use the listbox onclick event for this.

Dim strSQL as String

strSQL = “Select coln,coln,…. From Table Where Location = ‘†& me.listbox &
“’â€

Me.RecordSource = strSQL ‘update the form’s recordsource

I am assuming that the listbox Bound Column to 1 and the Field is “Locationâ€.

If you want to reset back the form’s recordsource to the original SQL syntax,
use a button event as the above with some alteration to the variable “strSQLâ€
to match the original SQL string.
 
AccessVandal via AccessMonster.com said:
I would use the listbox onclick event for this.
Click to expand...

PMFJI. Are you aware that the click event fires every time an arrow key is
used to move the selection up and down a listbox? Imagine the flurry of
network activity should you have just one user doing that..

Personally, I'd go for using the AfterUpdate event.
 
What is "coIn" ?
I would use the listbox onclick event for this.

Dim strSQL as String

strSQL = “Select coln,coln,…. From Table Where Location = ‘†& me.listbox &
“’â€

Me.RecordSource = strSQL ‘update the form’s recordsource

I am assuming that the listbox Bound Column to 1 and the Field is “Locationâ€.

If you want to reset back the form’s recordsource to the original SQL syntax,
use a button event as the above with some alteration to the variable “strSQLâ€
to match the original SQL string.
Hi All,
Click to expand...
[quoted text clipped - 8 lines]
Thanks
Matt
Click to expand...
Click to expand...
 
Here is my attempt at creating the SQL statement. It's not working. I get a
message that says I screwed up. I am missing something.

Private Sub txtLOC_AfterUpdate()

Dim strSQL As String

strSQL = "Select *, From qryInvData Where [LOC] = '" & Me.txtLOC & " '"

Me.RecordSource = strSQL

End Sub
I would use the listbox onclick event for this.

Dim strSQL as String

strSQL = “Select coln,coln,…. From Table Where Location = ‘†& me.listbox &
“’â€

Me.RecordSource = strSQL ‘update the form’s recordsource

I am assuming that the listbox Bound Column to 1 and the Field is “Locationâ€.

If you want to reset back the form’s recordsource to the original SQL syntax,
use a button event as the above with some alteration to the variable “strSQLâ€
to match the original SQL string.
Hi All,
Click to expand...
[quoted text clipped - 8 lines]
Thanks
Matt
Click to expand...
Click to expand...
 
Remove the comma before the "From". And you must make sure that the bound
column is the correct one.
Here is my attempt at creating the SQL statement. It's not working. I get a
message that says I screwed up. I am missing something.

Private Sub txtLOC_AfterUpdate()

Dim strSQL As String

strSQL = "Select *, From qryInvData Where [LOC] = '" & Me.txtLOC & " '"

Me.RecordSource = strSQL

End Sub
Click to expand...
 
Coln as in "Select Field1, Feild2, Feild3 ..... From Table ...."
 
The listbox does not have a mouse over event. This condition applies if the
MultiSelect is set to "None". If you're using a ActiveX, yes.
 
AccessVandal via AccessMonster.com said:
The listbox does not have a mouse over event. This condition applies if
the
MultiSelect is set to "None". If you're using a ActiveX, yes.
Click to expand...

Look again. I didn't mention a mouseover event. Far from it.
 
If you mean the keyboard Up/Down arrow keys, it’s not going to make any
difference.

But which developer uses a very large data to display a lookup?

If you have a very large data as a lookup, why use the back-end when you can
put it into the front-end?

Then again, why use combobox or listbox for a huge list of data?
 
AccessVandal via AccessMonster.com said:
If you mean the keyboard Up/Down arrow keys, it’s not going to make any
difference.

But which developer uses a very large data to display a lookup?

If you have a very large data as a lookup, why use the back-end when you
can
put it into the front-end?

Then again, why use combobox or listbox for a huge list of data?
Click to expand...

I never mentioned 'huge lists of data' either. I also never mentioned 'using
the back-end' (whatever you mean by that). You seem to enjoy putting words
in my mouth.

My point is simply that, by using the click event you are causing Access to
do unnecessary work.

However, do whatever you feel is right. I couldn't care less now <sigh>.
 
Oh great! Now I'm label as a bad guy. Mr Sensitive, I never put any of that
into your mouth. That's your own personal assumption and insinuation. Look
again and reflect on what you had posted.
 
Back
Top