load list box function bug

[Brad Hood]

Hi,
In our company we use Access 2000 as a "thin client" getting all
of our information from a SQL Server database. We use a custom
function to populate list and combo boxes which, for some records, is
returning some wierd results. If their is a ampersand and a space in a
name returned into the array that populates the list box, the list box
appears to be doubling the text, i.e, "T&est Company" will show up as
"T&est CompanTyest Company" in the list box. I was wondering if anyone
had encountered this problem in the past since I can't seem to find
any similar posts.

Access 2000 version: 9.0.3821 SR-1

Thanks for your time,

Brad Hood

The simplified code is below:

Create a Form and add a list box called: lstCompany
Paste in the Form Load sub: lstCompany.RowSourceType = "LoadListBoxes"

create a new module and paste in the following code:

Public Function LoadListBoxes(ctl As Control, id As Variant, _
row As Long, col As Long, code As Integer) As Variant
Static vReturn As Variant
Static iCount As Integer
Static iCompanyCount As Integer
Static CompanyArray() As String
Dim i As Integer
Select Case code
Case acLBInitialize
Select Case ctl.Name
Case "lstCompany"
ReDim CompanyArray(0)
CompanyArray(i) = "T&est Company"
i = i + 1
iCompanyCount = i
vReturn = i
End Select
Case acLBOpen
vReturn = Timer
Case acLBGetRowCount
Select Case ctl.Name
Case "lstCompany"
vReturn = iCompanyCount
End Select
Case acLBGetColumnCount
vReturn = 1
Case acLBGetColumnWidth
vReturn = True
Case acLBGetValue
Select Case ctl.Name
Case "lstCompany"
vReturn = CompanyArray(row)
End Select
Case acLBEnd
Select Case ctl.Name
Case "lstExistingCompanies"
Erase CompanyArray
iCompanyCount = 0
End Select
End Select
LoadListBoxes = vReturn
End Function

[Dave Perry]

Brad,

Rather than "Static vReturn As Variant"
Try "Dim vReturn As Variant"

For correctness, you may also want to look at the lines:
Case acLBEnd
Select Case ctl.Name
Case "lstExistingCompanies" <<<<<

Looks like you wanted:
Case "lstCompany"

-dp

Sp*******@msn.com (Brad Hood) wrote in message news:<56*************************@posting.google.c om>...

Hi,
In our company we use Access 2000 as a "thin client" getting all
of our information from a SQL Server database. We use a custom
function to populate list and combo boxes which, for some records, is
returning some wierd results. If their is a ampersand and a space in a
name returned into the array that populates the list box, the list box
appears to be doubling the text, i.e, "T&est Company" will show up as
"T&est CompanTyest Company" in the list box. I was wondering if anyone
had encountered this problem in the past since I can't seem to find
any similar posts.

Access 2000 version: 9.0.3821 SR-1

Thanks for your time,

Brad Hood

The simplified code is below:

Create a Form and add a list box called: lstCompany
Paste in the Form Load sub: lstCompany.RowSourceType = "LoadListBoxes"

create a new module and paste in the following code:

Public Function LoadListBoxes(ctl As Control, id As Variant, _
row As Long, col As Long, code As Integer) As Variant
Static vReturn As Variant
Static iCount As Integer
Static iCompanyCount As Integer
Static CompanyArray() As String
Dim i As Integer
Select Case code
Case acLBInitialize
Select Case ctl.Name
Case "lstCompany"
ReDim CompanyArray(0)
CompanyArray(i) = "T&est Company"
i = i + 1
iCompanyCount = i
vReturn = i
End Select
Case acLBOpen
vReturn = Timer
Case acLBGetRowCount
Select Case ctl.Name
Case "lstCompany"
vReturn = iCompanyCount
End Select
Case acLBGetColumnCount
vReturn = 1
Case acLBGetColumnWidth
vReturn = True
Case acLBGetValue
Select Case ctl.Name
Case "lstCompany"
vReturn = CompanyArray(row)
End Select
Case acLBEnd
Select Case ctl.Name
Case "lstExistingCompanies"
Erase CompanyArray
iCompanyCount = 0
End Select
End Select
LoadListBoxes = vReturn
End Function

[Brad Hood]

Thanks. The dim declaration did the trick. Maybe someone should let
microsoft now about this in their "Creating Combo Boxes and List Boxes
in Microsoft Access" article :)

http://www.microsoft.com/accessdev/articles/combo.htm

dp****@app-tech.com (Dave Perry) wrote in message news:<a0**************************@posting.google. com>...

Brad,

Rather than "Static vReturn As Variant"
Try "Dim vReturn As Variant"

For correctness, you may also want to look at the lines:
Case acLBEnd
Select Case ctl.Name
Case "lstExistingCompanies" <<<<<

Looks like you wanted:
Case "lstCompany"

-dp

Sp*******@msn.com (Brad Hood) wrote in message news:<56*************************@posting.google.c om>...

Hi,
In our company we use Access 2000 as a "thin client" getting all
of our information from a SQL Server database. We use a custom
function to populate list and combo boxes which, for some records, is
returning some wierd results. If their is a ampersand and a space in a
name returned into the array that populates the list box, the list box
appears to be doubling the text, i.e, "T&est Company" will show up as
"T&est CompanTyest Company" in the list box. I was wondering if anyone
had encountered this problem in the past since I can't seem to find
any similar posts.

Access 2000 version: 9.0.3821 SR-1

Thanks for your time,

Brad Hood

The simplified code is below:

Create a Form and add a list box called: lstCompany
Paste in the Form Load sub: lstCompany.RowSourceType = "LoadListBoxes"

create a new module and paste in the following code:

Public Function LoadListBoxes(ctl As Control, id As Variant, _
row As Long, col As Long, code As Integer) As Variant
Static vReturn As Variant
Static iCount As Integer
Static iCompanyCount As Integer
Static CompanyArray() As String
Dim i As Integer
Select Case code
Case acLBInitialize
Select Case ctl.Name
Case "lstCompany"
ReDim CompanyArray(0)
CompanyArray(i) = "T&est Company"
i = i + 1
iCompanyCount = i
vReturn = i
End Select
Case acLBOpen
vReturn = Timer
Case acLBGetRowCount
Select Case ctl.Name
Case "lstCompany"
vReturn = iCompanyCount
End Select
Case acLBGetColumnCount
vReturn = 1
Case acLBGetColumnWidth
vReturn = True
Case acLBGetValue
Select Case ctl.Name
Case "lstCompany"
vReturn = CompanyArray(row)
End Select
Case acLBEnd
Select Case ctl.Name
Case "lstExistingCompanies"
Erase CompanyArray
iCompanyCount = 0
End Select
End Select
LoadListBoxes = vReturn
End Function