Changing the record source of a list box.

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

Guest

Hi, i have a form which has a list box which looks up values from a table. i
have a set of toggle buttons which apply a filter to the form, the only
problem is the list box does not update with the filtered results.
if it is any help i have made a screenshot of the form which can be found at
http://www.computingforum.co.uk/icewolf/screenshot.PNG
i have found some code which i thought might work, but it didnt seem to
work, the code was:
Private Sub myOptionGroup_AfterUpdate()
Dim sSQL As String

Select Case Me!myOptionGroup.Value
Case 1: sSQL = "SELECT........."
Case 2: sSQL = "SELECT........."
Case 3: sSQL = "SELECT........."
End Select

Me!lstMyList.RowSource = sSQL
End Sub

Any help would be greatly appreciated,
Yours,
Colin Field
 
"didnt seem to work" isn't much to go by.

You could try putting Me!lstMyList.Refresh immediately after
Me!lstMyList.RowSource = sSQL

If that doesn't work, post back with some details of what the problem is.
 
Sorry for the lack of explanation, i will try to put in a bit more detail.
on the form that is in the screnshot, it is linked to a table with every
pupil in my school, each pupil is in a certain house. i have a set of toggle
buttons at the top which apply a filter to the form so that when "A" is
clicked, all the pupils in Alanbrooke house are shown, the problem is that
the list box does not change when these toggle buttons are clicked, it just
shows the list of everyone and i have to use the record forward and backward
buttons at the bottom of the form.
i found the code which i mentioned before in a another topic, when i put it
in my form, i was unsure of what to change so that the code referred to my
form. this is the code that i put in my form:
Private Sub HouseIDFilters_AfterUpdate()
Dim sSQL As String

Select Case Me!HouseIDFilters.Value
Case 1: sSQL = "SELECT [A].[ID], [A].[Surname] FROM [A]"
Case 2: sSQL = "SELECT [WY].[ID], [WY].[Surname] FROM [WY]"
Case 3: sSQL = "SELECT [C].[ID], [C].[Surname] FROM [C]"
End Select

Me!List73.RowSource = sSQL
Me!lstMyList.Refresh
End Sub

on my form, the option group is called HouseIDFilters, the list box is
called list73, the form is called EPOS and the table to which the form is
linked to is Main Database. i have also made queries for each house which i
attempted to use with the code above. when i tried to use the code, nothing
was different to when i didnt use it.
if looking directly at the database would be any help, i have put a copy on
my site, there is a login screen, but this can be bypassed by pressing shift
at startup.
http://www.computingforum.co.uk/icewolf/CFW-Colin.mdb

Thank you very much for your help,
Colin Field
 
Just to make sure the event is firing, try putting

MsgBox Me!HouseIDFilters.Value

directly before the Select Case statement.

If the message box appears, does it have the value you expect?

If the message box doesn't appear, go into the properties for HouseIDFilters
and check to ensure that AfterUpdate event property says [Event Procedure]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Colin Field said:
Sorry for the lack of explanation, i will try to put in a bit more detail.
on the form that is in the screnshot, it is linked to a table with every
pupil in my school, each pupil is in a certain house. i have a set of
toggle
buttons at the top which apply a filter to the form so that when "A" is
clicked, all the pupils in Alanbrooke house are shown, the problem is that
the list box does not change when these toggle buttons are clicked, it
just
shows the list of everyone and i have to use the record forward and
backward
buttons at the bottom of the form.
i found the code which i mentioned before in a another topic, when i put
it
in my form, i was unsure of what to change so that the code referred to my
form. this is the code that i put in my form:
Private Sub HouseIDFilters_AfterUpdate()
Dim sSQL As String

Select Case Me!HouseIDFilters.Value
Case 1: sSQL = "SELECT [A].[ID], [A].[Surname] FROM [A]"
Case 2: sSQL = "SELECT [WY].[ID], [WY].[Surname] FROM [WY]"
Case 3: sSQL = "SELECT [C].[ID], [C].[Surname] FROM [C]"
End Select

Me!List73.RowSource = sSQL
Me!lstMyList.Refresh
End Sub

on my form, the option group is called HouseIDFilters, the list box is
called list73, the form is called EPOS and the table to which the form is
linked to is Main Database. i have also made queries for each house which
i
attempted to use with the code above. when i tried to use the code,
nothing
was different to when i didnt use it.
if looking directly at the database would be any help, i have put a copy
on
my site, there is a login screen, but this can be bypassed by pressing
shift
at startup.
http://www.computingforum.co.uk/icewolf/CFW-Colin.mdb

Thank you very much for your help,
Colin Field
Douglas J. Steele said:
"didnt seem to work" isn't much to go by.

You could try putting Me!lstMyList.Refresh immediately after
Me!lstMyList.RowSource = sSQL

If that doesn't work, post back with some details of what the problem is.
Click to expand...
Click to expand...
 
Hi,
it is now working, the list box now changes its contents depending on what
button i press, but however when i click on a name in the list box the record
no longer changes for the rest of the form.
any ideas on how to make this happen?
the code i am using is:
Private Sub HouseIDFilters_AfterUpdate()
Dim sSQL As String

Select Case Me!HouseIDFilters.Value
Case 1: sSQL = "SELECT [A].[ID], [A].[Surname] FROM [A]"
Case 2: sSQL = "SELECT [WY].[ID], [WY].[Surname] FROM [WY]"
Case 3: sSQL = "SELECT [C].[ID], [C].[Surname] FROM [C]"
End Select

Me!List73.RowSource = sSQL

MsgBox Me!HouseIDFilters.Value
End Sub

thank you very much for your help,
Colin Field

Colin Field said:
Sorry for the lack of explanation, i will try to put in a bit more detail.
on the form that is in the screnshot, it is linked to a table with every
pupil in my school, each pupil is in a certain house. i have a set of toggle
buttons at the top which apply a filter to the form so that when "A" is
clicked, all the pupils in Alanbrooke house are shown, the problem is that
the list box does not change when these toggle buttons are clicked, it just
shows the list of everyone and i have to use the record forward and backward
buttons at the bottom of the form.
i found the code which i mentioned before in a another topic, when i put it
in my form, i was unsure of what to change so that the code referred to my
form. this is the code that i put in my form:
Private Sub HouseIDFilters_AfterUpdate()
Dim sSQL As String

Select Case Me!HouseIDFilters.Value
Case 1: sSQL = "SELECT [A].[ID], [A].[Surname] FROM [A]"
Case 2: sSQL = "SELECT [WY].[ID], [WY].[Surname] FROM [WY]"
Case 3: sSQL = "SELECT [C].[ID], [C].[Surname] FROM [C]"
End Select

Me!List73.RowSource = sSQL
Me!lstMyList.Refresh
End Sub

on my form, the option group is called HouseIDFilters, the list box is
called list73, the form is called EPOS and the table to which the form is
linked to is Main Database. i have also made queries for each house which i
attempted to use with the code above. when i tried to use the code, nothing
was different to when i didnt use it.
if looking directly at the database would be any help, i have put a copy on
my site, there is a login screen, but this can be bypassed by pressing shift
at startup.
http://www.computingforum.co.uk/icewolf/CFW-Colin.mdb

Thank you very much for your help,
Colin Field
Douglas J. Steele said:
"didnt seem to work" isn't much to go by.

You could try putting Me!lstMyList.Refresh immediately after
Me!lstMyList.RowSource = sSQL

If that doesn't work, post back with some details of what the problem is.
Click to expand...
Click to expand...
 
I think you need to requery the form's control source...


Colin Field said:
Hi,
it is now working, the list box now changes its contents depending on what
button i press, but however when i click on a name in the list box the record
no longer changes for the rest of the form.
any ideas on how to make this happen?
the code i am using is:
Private Sub HouseIDFilters_AfterUpdate()
Dim sSQL As String

Select Case Me!HouseIDFilters.Value
Case 1: sSQL = "SELECT [A].[ID], [A].[Surname] FROM [A]"
Case 2: sSQL = "SELECT [WY].[ID], [WY].[Surname] FROM [WY]"
Case 3: sSQL = "SELECT [C].[ID], [C].[Surname] FROM [C]"
End Select

Me!List73.RowSource = sSQL

MsgBox Me!HouseIDFilters.Value
End Sub

thank you very much for your help,
Colin Field

Colin Field said:
Sorry for the lack of explanation, i will try to put in a bit more detail.
on the form that is in the screnshot, it is linked to a table with every
pupil in my school, each pupil is in a certain house. i have a set of toggle
buttons at the top which apply a filter to the form so that when "A" is
clicked, all the pupils in Alanbrooke house are shown, the problem is that
the list box does not change when these toggle buttons are clicked, it just
shows the list of everyone and i have to use the record forward and backward
buttons at the bottom of the form.
i found the code which i mentioned before in a another topic, when i put it
in my form, i was unsure of what to change so that the code referred to my
form. this is the code that i put in my form:
Private Sub HouseIDFilters_AfterUpdate()
Dim sSQL As String

Select Case Me!HouseIDFilters.Value
Case 1: sSQL = "SELECT [A].[ID], [A].[Surname] FROM [A]"
Case 2: sSQL = "SELECT [WY].[ID], [WY].[Surname] FROM [WY]"
Case 3: sSQL = "SELECT [C].[ID], [C].[Surname] FROM [C]"
End Select

Me!List73.RowSource = sSQL
Me!lstMyList.Refresh
End Sub

on my form, the option group is called HouseIDFilters, the list box is
called list73, the form is called EPOS and the table to which the form is
linked to is Main Database. i have also made queries for each house which i
attempted to use with the code above. when i tried to use the code, nothing
was different to when i didnt use it.
if looking directly at the database would be any help, i have put a copy on
my site, there is a login screen, but this can be bypassed by pressing shift
at startup.
http://www.computingforum.co.uk/icewolf/CFW-Colin.mdb

Thank you very much for your help,
Colin Field
Douglas J. Steele said:
"didnt seem to work" isn't much to go by.

You could try putting Me!lstMyList.Refresh immediately after
Me!lstMyList.RowSource = sSQL

If that doesn't work, post back with some details of what the problem is.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hi, i have a form which has a list box which looks up values from a table.
i
have a set of toggle buttons which apply a filter to the form, the only
problem is the list box does not update with the filtered results.
if it is any help i have made a screenshot of the form which can be found
at
http://www.computingforum.co.uk/icewolf/screenshot.PNG
i have found some code which i thought might work, but it didnt seem to
work, the code was:
Private Sub myOptionGroup_AfterUpdate()
Dim sSQL As String

Select Case Me!myOptionGroup.Value
Case 1: sSQL = "SELECT........."
Case 2: sSQL = "SELECT........."
Case 3: sSQL = "SELECT........."
End Select

Me!lstMyList.RowSource = sSQL
End Sub

Any help would be greatly appreciated,
Yours,
Colin Field
Click to expand...
Click to expand...
Click to expand...
 
Back
Top