Filter Data based on Form

[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

 

[AccessVandal via AccessMonster.com]

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.

 

[Stuart McCall]

I would use the listbox onclick event for this.

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.

 

[mattc66 via AccessMonster.com]

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,

[quoted text clipped - 8 lines]

Thanks
Matt

 

[mattc66 via AccessMonster.com]

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,

[quoted text clipped - 8 lines]

Thanks
Matt

 

[AccessVandal via AccessMonster.com]

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

 

[AccessVandal via AccessMonster.com]

Coln as in "Select Field1, Feild2, Feild3 ..... From Table ...."

 

[AccessVandal via AccessMonster.com]

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.

 

[Stuart McCall]

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.

Look again. I didn't mention a mouseover event. Far from it.

 

[AccessVandal via AccessMonster.com]

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?

 

[Stuart McCall]

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?

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>.