Blank List Control when data is present.

  • Thread starter Thread starter Martin
  • Start date Start date
M

Martin

I work in a school and am trying to link three list controls together so that
choosing a year group in List A results in a list of classes in List B.
Choosing a class in B results in a list of students in List C.
i.e.
A B C
Year Group -> Class Group -> Student List

The problem is that List C contains student names but they are not dislayed.
The effect is similar to setting the text colour to white. Thus while I can
select a student by clicking on the list C, I do not know which student it is
since the list appears to be empty.

The correct data is in the control (verified through VBA), the items can be
selected (a solid black bar highlights the student's name), and the selected
items can be extracted through VBA.

The text colour is not white.
The column width is blank (and setting this to 6cm has no effect)

The code for clicking on YearGroup (A) (and filling teaching group (B) -
working) is

Private Sub lstYearGroup_AfterUpdate()
Me!lstRegGroup.RowSource = "SELECT knownRegGroup.RegGroup FROM knownRegGroup
WHERE (((knownRegGroup.[Year Group])=""" & Me!lstYearGroup.Value & """));"
Me!lstRegGroup.Requery

Me!lstStudent.RowSource = ""
Me!lstStudent.Requery

End Sub


The code of clicking on Reg group (B) (and filling student list C - not
working) is

Private Sub lstRegGroup_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT knownPupils.Student FROM knownPupils "
strSQL = strSQL & "WHERE (((knownPupils.[Year Group]) = """ &
Me!lstYearGroup.Value & """) And ((knownPupils.[Reg Group]) = """ &
Me!lstRegGroup.Value & """)) "
strSQL = strSQL & "ORDER BY knownPupils.Student;"

Me!lstStudent.RowSource = strSQL
Me!lstStudent.Requery

End Sub


Any suggestions would be welcome.

Many thanks

Martin.
 
Check the ColumnCount and ColumnWidths properties for the ListC listbox. The
values should be 1 and 4" (or whatever width you want) respectively.
 
I had tried that. I left the width properties blank and set them to two
different widths. Neither worked exactly.

The database is on a mapped shared drive. It has since come to light that
that it depends on which computer I am sitting as to whether the third list
box works.

As far as I can see, they are similar software setups ... office 2003 SP3,
and I cannot understand why 2 out of 3 list box controls seem to work well.

Any suggestions as to where to look would be very gratefully recieved.

Regards

Martin.

Ken Snell MVP said:
Check the ColumnCount and ColumnWidths properties for the ListC listbox. The
values should be 1 and 4" (or whatever width you want) respectively.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Martin said:
I work in a school and am trying to link three list controls together so
that
choosing a year group in List A results in a list of classes in List B.
Choosing a class in B results in a list of students in List C.
i.e.
A B C
Year Group -> Class Group -> Student List

The problem is that List C contains student names but they are not
dislayed.
The effect is similar to setting the text colour to white. Thus while I
can
select a student by clicking on the list C, I do not know which student it
is
since the list appears to be empty.

The correct data is in the control (verified through VBA), the items can
be
selected (a solid black bar highlights the student's name), and the
selected
items can be extracted through VBA.

The text colour is not white.
The column width is blank (and setting this to 6cm has no effect)

The code for clicking on YearGroup (A) (and filling teaching group (B) -
working) is

Private Sub lstYearGroup_AfterUpdate()
Me!lstRegGroup.RowSource = "SELECT knownRegGroup.RegGroup FROM
knownRegGroup
WHERE (((knownRegGroup.[Year Group])=""" & Me!lstYearGroup.Value & """));"
Me!lstRegGroup.Requery

Me!lstStudent.RowSource = ""
Me!lstStudent.Requery

End Sub


The code of clicking on Reg group (B) (and filling student list C - not
working) is

Private Sub lstRegGroup_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT knownPupils.Student FROM knownPupils "
strSQL = strSQL & "WHERE (((knownPupils.[Year Group]) = """ &
Me!lstYearGroup.Value & """) And ((knownPupils.[Reg Group]) = """ &
Me!lstRegGroup.Value & """)) "
strSQL = strSQL & "ORDER BY knownPupils.Student;"

Me!lstStudent.RowSource = strSQL
Me!lstStudent.Requery

End Sub


Any suggestions would be welcome.

Many thanks

Martin.
Click to expand...
Click to expand...
 
Does the listbox control (or the field to which it's bound) have a value in
the Format property? If yes, you've been bitten by a new bug introduced by
Office 2003 SP3 package. You'll need to apply a hotfix:

Description of the Access 2003 post-Service Pack 3 hotfix package:
December 18, 2007 - 945674

http://support.microsoft.com/kb/945674

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Martin said:
I had tried that. I left the width properties blank and set them to two
different widths. Neither worked exactly.

The database is on a mapped shared drive. It has since come to light that
that it depends on which computer I am sitting as to whether the third
list
box works.

As far as I can see, they are similar software setups ... office 2003 SP3,
and I cannot understand why 2 out of 3 list box controls seem to work
well.

Any suggestions as to where to look would be very gratefully recieved.

Regards

Martin.

Ken Snell MVP said:
Check the ColumnCount and ColumnWidths properties for the ListC listbox.
The
values should be 1 and 4" (or whatever width you want) respectively.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Martin said:
I work in a school and am trying to link three list controls together so
that
choosing a year group in List A results in a list of classes in List B.
Choosing a class in B results in a list of students in List C.
i.e.
A B C
Year Group -> Class Group -> Student List

The problem is that List C contains student names but they are not
dislayed.
The effect is similar to setting the text colour to white. Thus while I
can
select a student by clicking on the list C, I do not know which student
it
is
since the list appears to be empty.

The correct data is in the control (verified through VBA), the items
can
be
selected (a solid black bar highlights the student's name), and the
selected
items can be extracted through VBA.

The text colour is not white.
The column width is blank (and setting this to 6cm has no effect)

The code for clicking on YearGroup (A) (and filling teaching group
(B) -
working) is

Private Sub lstYearGroup_AfterUpdate()
Me!lstRegGroup.RowSource = "SELECT knownRegGroup.RegGroup FROM
knownRegGroup
WHERE (((knownRegGroup.[Year Group])=""" & Me!lstYearGroup.Value &
"""));"
Me!lstRegGroup.Requery

Me!lstStudent.RowSource = ""
Me!lstStudent.Requery

End Sub


The code of clicking on Reg group (B) (and filling student list C - not
working) is

Private Sub lstRegGroup_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT knownPupils.Student FROM knownPupils "
strSQL = strSQL & "WHERE (((knownPupils.[Year Group]) = """ &
Me!lstYearGroup.Value & """) And ((knownPupils.[Reg Group]) = """ &
Me!lstRegGroup.Value & """)) "
strSQL = strSQL & "ORDER BY knownPupils.Student;"

Me!lstStudent.RowSource = strSQL
Me!lstStudent.Requery

End Sub


Any suggestions would be welcome.

Many thanks

Martin.
Click to expand...
Click to expand...
Click to expand...
 
Fixed ... thankyou

The underlying table was a linked spreadsheet.

I have simply added &"" to the listbox control source:

strSQL = "SELECT knownPupils.Student &"""" FROM knownPupils "

as per article 945280 which gives a VBA work around.
The locked nature of the school network means that hotfixes cannot be
applied except for once a year.

Many thanks

Regards

Martin.

Ken Snell MVP said:
Does the listbox control (or the field to which it's bound) have a value in
the Format property? If yes, you've been bitten by a new bug introduced by
Office 2003 SP3 package. You'll need to apply a hotfix:

Description of the Access 2003 post-Service Pack 3 hotfix package:
December 18, 2007 - 945674

http://support.microsoft.com/kb/945674

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Martin said:
I had tried that. I left the width properties blank and set them to two
different widths. Neither worked exactly.

The database is on a mapped shared drive. It has since come to light that
that it depends on which computer I am sitting as to whether the third
list
box works.

As far as I can see, they are similar software setups ... office 2003 SP3,
and I cannot understand why 2 out of 3 list box controls seem to work
well.

Any suggestions as to where to look would be very gratefully recieved.

Regards

Martin.

Ken Snell MVP said:
Check the ColumnCount and ColumnWidths properties for the ListC listbox.
The
values should be 1 and 4" (or whatever width you want) respectively.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I work in a school and am trying to link three list controls together so
that
choosing a year group in List A results in a list of classes in List B.
Choosing a class in B results in a list of students in List C.
i.e.
A B C
Year Group -> Class Group -> Student List

The problem is that List C contains student names but they are not
dislayed.
The effect is similar to setting the text colour to white. Thus while I
can
select a student by clicking on the list C, I do not know which student
it
is
since the list appears to be empty.

The correct data is in the control (verified through VBA), the items
can
be
selected (a solid black bar highlights the student's name), and the
selected
items can be extracted through VBA.

The text colour is not white.
The column width is blank (and setting this to 6cm has no effect)

The code for clicking on YearGroup (A) (and filling teaching group
(B) -
working) is

Private Sub lstYearGroup_AfterUpdate()
Me!lstRegGroup.RowSource = "SELECT knownRegGroup.RegGroup FROM
knownRegGroup
WHERE (((knownRegGroup.[Year Group])=""" & Me!lstYearGroup.Value &
"""));"
Me!lstRegGroup.Requery

Me!lstStudent.RowSource = ""
Me!lstStudent.Requery

End Sub


The code of clicking on Reg group (B) (and filling student list C - not
working) is

Private Sub lstRegGroup_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT knownPupils.Student FROM knownPupils "
strSQL = strSQL & "WHERE (((knownPupils.[Year Group]) = """ &
Me!lstYearGroup.Value & """) And ((knownPupils.[Reg Group]) = """ &
Me!lstRegGroup.Value & """)) "
strSQL = strSQL & "ORDER BY knownPupils.Student;"

Me!lstStudent.RowSource = strSQL
Me!lstStudent.Requery

End Sub


Any suggestions would be welcome.

Many thanks

Martin.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top