Callback for MultiColumn List Box

Gail · Nov 21, 2003

I am using a callback function provided by
Microsoft Knowledge Base Article # 210442
for a list box that displays a single column list of names
from a stored query. I want to include a second column in
the list box that displays the ID number (datatype long)
for each row, making it a multi-column list box. I have
searched extensively for an example of an array that will
return a multi-column list but have not found anything
that shows specifically how to do it. Here is the part of
my code that return the Name field:

Select Case intCode
Case acLBInitialize
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT PatientID,
Name FROM qry_search_results")
intArrayCount = 0
ReDim Preserve aryTestArray(0)

' Fill the array.
With rst
intNumRec = rst.RecordCount
Do Until rst.EOF
' Fill the array row with the last name.
aryTestArray(intArrayCount) = ![Name]
' Increase the number of elements in the
array
' by one to accommodate the next record.
ReDim Preserve aryTestArray(UBound
(aryTestArray) + 1)
intArrayCount = intArrayCount + 1
.MoveNext
Loop
' Remove the remaining empty array row.
ReDim Preserve aryTestArray(UBound
(aryTestArray) - 1)
.Close
End With
db.Close
' View the array contents.
For intCounter = 0 To intArrayCount - 1
varValue = aryTestArray(intCounter)
Next intCounter
varRetVal = True
etc., etc.

Thanks for any enlightenment!

Gary Miller · Nov 21, 2003

Gail,

Wow, that is a lot of work if you are just trying to add a
second column to the list box. If the ID is not already in
the stored query add it just to the right of your names
field. Now go to your list box and change the ColumnCount to
be 2 in the properties box and then change you Column widths
to something like 1.5";1" depending on how wide you want
each to display. I think that is all you need to do if you
are referring to your query by it's name.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

Gail · Nov 22, 2003

Gary,

Thank you for your prompt reply. Yes, it has turned into
a lot of work because the list box has to change
dynamically in response to the user selecting different
sets of search criteria, resulting in a different list of
Names everytime they search. When the user double-clicks a
Name in the list, I need to pass the ID field.

I have followed all that you recommend. The result is that
the Name field is displayed in both columns. I think it
has something to do with the line of code that
says "aryTestArray(intArrayCount) = ![Name]". I cannot
figure out how to demension the array to include the
second column. I have also tried the GetRows method of the
recordset, but cannot get it to fill the list at all.

Thanks for your suggestions.

Gail

Gail said:
-----Original Message-----
Gail,

Wow, that is a lot of work if you are just trying to add a
second column to the list box. If the ID is not already in
the stored query add it just to the right of your names
field. Now go to your list box and change the ColumnCount to
be 2 in the properties box and then change you Column widths
to something like 1.5";1" depending on how wide you want
each to display. I think that is all you need to do if you
are referring to your query by it's name.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

Gail said:

I am using a callback function provided by
Microsoft Knowledge Base Article # 210442
for a list box that displays a single column list of names
from a stored query. I want to include a second column in
the list box that displays the ID number (datatype long)
for each row, making it a multi-column list box. I have
searched extensively for an example of an array that will
return a multi-column list but have not found anything
that shows specifically how to do it. Here is the part of
my code that return the Name field:

Select Case intCode
Case acLBInitialize
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT PatientID,
Name FROM qry_search_results")
intArrayCount = 0
ReDim Preserve aryTestArray(0)

' Fill the array.
With rst
intNumRec = rst.RecordCount
Do Until rst.EOF
' Fill the array row with the last name.
aryTestArray(intArrayCount) = ![Name]
' Increase the number of elements in the
array
' by one to accommodate the next record.
ReDim Preserve aryTestArray(UBound
(aryTestArray) + 1)
intArrayCount = intArrayCount + 1
.MoveNext
Loop
' Remove the remaining empty array row.
ReDim Preserve aryTestArray(UBound
(aryTestArray) - 1)
.Close
End With
db.Close
' View the array contents.
For intCounter = 0 To intArrayCount - 1
varValue = aryTestArray(intCounter)
Next intCounter
varRetVal = True
etc., etc.

Thanks for any enlightenment!

Click to expand...

.

PC Datasheet · Nov 22, 2003

Gail,

Gary is right - you're making it hard on yourself trying to do it with an array.
Just use an SQL statement as the row source of the list box and alter the
statement according to the search criteria entered by the user. Something like:

Dim SQLStr As String
Dim SelectStr As String
Dim WhereStr As String
SelectStr = "Select PatientID, PatientName From TblPatient "
Select Case SearchCriteria
Case "This"
WhereStr = "Where ...................." & Forms!MyForm!Somefiled
Case "That"
WhereStr = "Where ................." & Forms!MyForm!Somefiled
End Select
SQLStr = SelectStr & WhereStr
Me!NameOfListbox.Rowsource = SQLStr

You can now display the PatientName in the listbox and return PatientID by
setting the bound column to 1, columncount to 2 and column widths to 0;2.

By the way, do you have any need to use a Palm for collecting patient data and
then importing that data into your database? I do that kind of application.
Contact me if you are interested.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Gail said:
Gary,

Thank you for your prompt reply. Yes, it has turned into
a lot of work because the list box has to change
dynamically in response to the user selecting different
sets of search criteria, resulting in a different list of
Names everytime they search. When the user double-clicks a
Name in the list, I need to pass the ID field.

I have followed all that you recommend. The result is that
the Name field is displayed in both columns. I think it
has something to do with the line of code that
says "aryTestArray(intArrayCount) = ![Name]". I cannot
figure out how to demension the array to include the
second column. I have also tried the GetRows method of the
recordset, but cannot get it to fill the list at all.

Thanks for your suggestions.

Gail

-----Original Message-----
Gail,

Wow, that is a lot of work if you are just trying to add a
second column to the list box. If the ID is not already in
the stored query add it just to the right of your names
field. Now go to your list box and change the ColumnCount to
be 2 in the properties box and then change you Column widths
to something like 1.5";1" depending on how wide you want
each to display. I think that is all you need to do if you
are referring to your query by it's name.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

Gail said:

I am using a callback function provided by
Microsoft Knowledge Base Article # 210442
for a list box that displays a single column list of names
from a stored query. I want to include a second column in
the list box that displays the ID number (datatype long)
for each row, making it a multi-column list box. I have
searched extensively for an example of an array that will
return a multi-column list but have not found anything
that shows specifically how to do it. Here is the part of
my code that return the Name field:

Select Case intCode
Case acLBInitialize
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT PatientID,
Name FROM qry_search_results")
intArrayCount = 0
ReDim Preserve aryTestArray(0)

' Fill the array.
With rst
intNumRec = rst.RecordCount
Do Until rst.EOF
' Fill the array row with the last name.
aryTestArray(intArrayCount) = ![Name]
' Increase the number of elements in the
array
' by one to accommodate the next record.
ReDim Preserve aryTestArray(UBound
(aryTestArray) + 1)
intArrayCount = intArrayCount + 1
.MoveNext
Loop
' Remove the remaining empty array row.
ReDim Preserve aryTestArray(UBound
(aryTestArray) - 1)
.Close
End With
db.Close
' View the array contents.
For intCounter = 0 To intArrayCount - 1
varValue = aryTestArray(intCounter)
Next intCounter
varRetVal = True
etc., etc.

Thanks for any enlightenment!

Click to expand...

.

Click to expand...

TC · Nov 22, 2003

Well, the "etc., etc." at the end, is the key part!

The acLBGetValue branch (which you have not shown) is what returns the row &
column values. On each call, you must use the values of the row and col
parameters, to determine which row & column values the listbox is asking
for. Be aware that you can >not< (in general) assume that the listbox will
make the minimum number of calls required, & make those calls in any
"sensible" order. In certain cases it can make more calls than are really
required, and it can make those calls in very strange orders! So you must
code the acLBGetValue branch to work properly for >any< number or sequence
of calls.

PS:

- You could probably replace all your looping code with a single call to the
GetRows() function.

- Have you declared your array as Static? Remember that it must retain its
content over seperate calls to the callback function.

- As the other respondent said, there might be a better way of doing what
you want. I have not considered that issue.

HTH,
TC

Gail · Nov 22, 2003

Gary,

I finally figured out how to return multiple columns
programmatically in a list box. I want to include the
entire function for others who may be looking for
information on this topic:

First, I declared a User Defined Type in a Global Module
for my 2 columns:

Public Type typData
lngID As Long
strName As String
End Type

Then I followed the built-in Access User Defined RowSource
Function format to return all the information Access needs
to fill the multi-column list box. I used DAO but ADO will
also work:

'From Microsoft Knowledge Base Article - 210442 (mostly)

Public Function GetSelectedPatients(ctl As Control, varID
As Variant, _
lngRow As Long, lngCol As Long, intCode As Integer) As
Variant

Dim db As DAO.Database
Dim rst As DAO.Recordset

Dim intI As Integer
Static aFullArray() As typData 'Here's the declared Type
Static intCounter As Long
Static varValue As Variant
Static intNumRec As Integer

On Error GoTo errorhandler

Select Case intCode
Case acLBInitialize
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT Name, PatientID
FROM qry_search_results")
'Reset counters to zero
intI = 0
intCounter = 0
'Resize dynamic array
ReDim Preserve aFullArray(0)
' Fill the array.
With rst
intNumRec = .RecordCount
Do Until .EOF
' Tell Access which fields to display in the columns.
aFullArray(intCounter).lngID = ![PatientID]
aFullArray(intCounter).strName = ![Name]
' Increase the number of elements in the array
' by one to accommodate the next record.
ReDim Preserve aFullArray(UBound(aFullArray) + 1)
intCounter = intCounter + 1
.MoveNext
Loop
' Remove the remaining empty array row.
ReDim Preserve aFullArray(UBound(aFullArray) - 1)
.Close
End With
db.Close
' View the array contents.
For intCounter = 0 To intNumRec - 1
varValue = aFullArray(intCounter).lngID
Next intCounter
varRetVal = True
Case acLBOpen
'Return a unigeq ID code.
varRetVal = Timer
Case acLBGetRowCount
'Return number of rows
varRetVal = intNumRec
Case acLBGetColumnCount
'Return number of columns
varRetVal = 2
Case acLBGetColumnWidth
'Return property sheet columns widths
varRetVal = -1
Case acLBGetValue
'Return actual data
If lngCol = 0 Then
varRetVal = aFullArray(lngRow).lngID
ElseIf lngCol = 1 Then
varRetVal = aFullArray(lngRow).strName
End If
End Select

GetSelectedPatients = varRetVal

errorhandler:
Select Case Err
Case 0
Exit Function
Case Else
MsgBox Err.Number & " " & Err.Description
Exit Function
End Select

End Function

Thanks for your input and I hope this will be useful to
anyone else searching for information on this subject.

Best regards,

Gail

Gail said:
-----Original Message-----
Gail,

Wow, that is a lot of work if you are just trying to add a
second column to the list box. If the ID is not already in
the stored query add it just to the right of your names
field. Now go to your list box and change the ColumnCount to
be 2 in the properties box and then change you Column widths
to something like 1.5";1" depending on how wide you want
each to display. I think that is all you need to do if you
are referring to your query by it's name.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

Gail said:

I am using a callback function provided by
Microsoft Knowledge Base Article # 210442
for a list box that displays a single column list of names
from a stored query. I want to include a second column in
the list box that displays the ID number (datatype long)
for each row, making it a multi-column list box. I have
searched extensively for an example of an array that will
return a multi-column list but have not found anything
that shows specifically how to do it. Here is the part of
my code that return the Name field:

Select Case intCode
Case acLBInitialize
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT PatientID,
Name FROM qry_search_results")
intArrayCount = 0
ReDim Preserve aryTestArray(0)

' Fill the array.
With rst
intNumRec = rst.RecordCount
Do Until rst.EOF
' Fill the array row with the last name.
aryTestArray(intArrayCount) = ![Name]
' Increase the number of elements in the
array
' by one to accommodate the next record.
ReDim Preserve aryTestArray(UBound
(aryTestArray) + 1)
intArrayCount = intArrayCount + 1
.MoveNext
Loop
' Remove the remaining empty array row.
ReDim Preserve aryTestArray(UBound
(aryTestArray) - 1)
.Close
End With
db.Close
' View the array contents.
For intCounter = 0 To intArrayCount - 1
varValue = aryTestArray(intCounter)
Next intCounter
varRetVal = True
etc., etc.

Thanks for any enlightenment!

Click to expand...

.

Guest · Nov 22, 2003

Dear PCDatasheet,

I thoroughly exhausted the technique you suggest before
plunging into the dark matter of Arrays. The list box
would only refresh upon closing and re-opening the form,
but would not budge using any of the more familiar, and
much easier, techniques. In the meantime, I solved my own
problem and posted it in the previous thread for others
who might be interested. Take a look and tell me what you
think. It works quite well!

Oh, and my client may be interested in the Palm thing,
I'll ask him. Maybe you could tell me a bit more about how
it works so I can pass the information along to the good
doctor.

Thanks for the support. You provide a truly valuable
service to all us struggling and sometimes clueless VBA
programmers.

Gail

-----Original Message-----
Gail,

Gary is right - you're making it hard on yourself trying to do it with an array.
Just use an SQL statement as the row source of the list box and alter the
statement according to the search criteria entered by the user. Something like:

Dim SQLStr As String
Dim SelectStr As String
Dim WhereStr As String
SelectStr = "Select PatientID, PatientName From TblPatient "
Select Case SearchCriteria
Case "This"
WhereStr = "Where ...................." & Forms! MyForm!Somefiled
Case "That"
WhereStr = "Where ................." & Forms!MyForm! Somefiled
End Select
SQLStr = SelectStr & WhereStr
Me!NameOfListbox.Rowsource = SQLStr

You can now display the PatientName in the listbox and return PatientID by
setting the bound column to 1, columncount to 2 and column widths to 0;2.

By the way, do you have any need to use a Palm for collecting patient data and
then importing that data into your database? I do that kind of application.
Contact me if you are interested.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Gary,

Thank you for your prompt reply. Yes, it has turned into
a lot of work because the list box has to change
dynamically in response to the user selecting different
sets of search criteria, resulting in a different list of
Names everytime they search. When the user double- clicks a
Name in the list, I need to pass the ID field.

I have followed all that you recommend. The result is that
the Name field is displayed in both columns. I think it
has something to do with the line of code that
says "aryTestArray(intArrayCount) = ![Name]". I cannot
figure out how to demension the array to include the
second column. I have also tried the GetRows method of the
recordset, but cannot get it to fill the list at all.

Thanks for your suggestions.

Gail

-----Original Message-----
Gail,

Wow, that is a lot of work if you are just trying to add a
second column to the list box. If the ID is not already in
the stored query add it just to the right of your names
field. Now go to your list box and change the

Click to expand...

ColumnCount
to

be 2 in the properties box and then change you Column widths
to something like 1.5";1" depending on how wide you want
each to display. I think that is all you need to do if you
are referring to your query by it's name.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
message I am using a callback function provided by
Microsoft Knowledge Base Article # 210442
for a list box that displays a single column list of names
from a stored query. I want to include a second

Click to expand...

column
in

the list box that displays the ID number (datatype long)
for each row, making it a multi-column list box. I have
searched extensively for an example of an array that will
return a multi-column list but have not found anything
that shows specifically how to do it. Here is the

Click to expand...

part
of

my code that return the Name field:

Select Case intCode
Case acLBInitialize
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT PatientID,
Name FROM qry_search_results")
intArrayCount = 0
ReDim Preserve aryTestArray(0)

' Fill the array.
With rst
intNumRec = rst.RecordCount
Do Until rst.EOF
' Fill the array row with the last name.
aryTestArray(intArrayCount) = ! [Name]
' Increase the number of elements in the
array
' by one to accommodate the next record.
ReDim Preserve aryTestArray(UBound
(aryTestArray) + 1)
intArrayCount = intArrayCount + 1
.MoveNext
Loop
' Remove the remaining empty array row.
ReDim Preserve aryTestArray(UBound
(aryTestArray) - 1)
.Close
End With
db.Close
' View the array contents.
For intCounter = 0 To intArrayCount - 1
varValue = aryTestArray(intCounter)
Next intCounter
varRetVal = True
etc., etc.

Thanks for any enlightenment!

.

Click to expand...

Click to expand...

.

Guest · Nov 22, 2003

Dear TC,

Yes, you are right, I left off the very important Case
aclbGetValue, for the sake of being brief. The GetRows
method would not return any values in the list box--I
tried that before delving into the messy business of
arrays. And you are also correct about the importance of
declaring the variables as Static, otherwise the results
will never show up in the list box even if you have the
corect algorithm.

The good news is that I have since solved my problem and
have included the entire function in the thread dated Nov
21 2003 8:52 p.m. Please take a look at it if you are
interested in how I did it.

Thanks so much for your input, your support is greatly
appreciated.

Best regards,

Gail

-----Original Message-----
Well, the "etc., etc." at the end, is the key part!

The acLBGetValue branch (which you have not shown) is what returns the row &
column values. On each call, you must use the values of the row and col
parameters, to determine which row & column values the listbox is asking
for. Be aware that you can >not< (in general) assume that the listbox will
make the minimum number of calls required, & make those calls in any
"sensible" order. In certain cases it can make more calls than are really
required, and it can make those calls in very strange orders! So you must
code the acLBGetValue branch to work properly for >any< number or sequence
of calls.

PS:

- You could probably replace all your looping code with a single call to the
GetRows() function.

- Have you declared your array as Static? Remember that it must retain its
content over seperate calls to the callback function.

- As the other respondent said, there might be a better way of doing what
you want. I have not considered that issue.

HTH,
TC

I am using a callback function provided by
Microsoft Knowledge Base Article # 210442
for a list box that displays a single column list of names
from a stored query. I want to include a second column in
the list box that displays the ID number (datatype long)
for each row, making it a multi-column list box. I have
searched extensively for an example of an array that will
return a multi-column list but have not found anything
that shows specifically how to do it. Here is the part of
my code that return the Name field:

Select Case intCode
Case acLBInitialize
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT PatientID,
Name FROM qry_search_results")
intArrayCount = 0
ReDim Preserve aryTestArray(0)

' Fill the array.
With rst
intNumRec = rst.RecordCount
Do Until rst.EOF
' Fill the array row with the last name.
aryTestArray(intArrayCount) = ![Name]
' Increase the number of elements in the
array
' by one to accommodate the next record.
ReDim Preserve aryTestArray(UBound
(aryTestArray) + 1)
intArrayCount = intArrayCount + 1
.MoveNext
Loop
' Remove the remaining empty array row.
ReDim Preserve aryTestArray(UBound
(aryTestArray) - 1)
.Close
End With
db.Close
' View the array contents.
For intCounter = 0 To intArrayCount - 1
varValue = aryTestArray(intCounter)
Next intCounter
varRetVal = True
etc., etc.

Thanks for any enlightenment!

Click to expand...

.

PC Datasheet · Nov 22, 2003

Gail,

In regards to refreshing the listbox, all you would have needed to do is requery
the listbox right before searching for the list of patient names. The listbox
would have then displayed the patient names that fit the search criteria entered
by the user.

In regards to using the Palm to collect "field" data ---
Tables and interfaces to the tables are built in the Palm for collecting the
data with the Palm. A special database (Access) is created that interface
between a main Access database and the Palm. There is a synchronization process
that imports the data stored in the Palm to the main database. The main database
is designed to process that data in any of he ways Access processes data. Data
entry is on the Palm and to the extent possible is done with drop down lists
cretaed by the special database. Where drop down lists are not possible, data
entry is done on the Palm using the stylus and the built in keyboard. The
application provides for a means of collecting data with a device that fits in a
shirt pocket and a way to get the data into the main database without having to
rekey it again into the main database. The Palm needs to have the Palm OS3 or
higher. Memory requirements of the Palm is a function of the amount of data that
will be collected and stored on the Palm between synchronizations. My last Palm
project was for a team of foundation and soil inspectors who did residential and
commercial inspection services for insurance companies. The team was on the road
daily and would travel to 3 to 8 locations in a day. Data collected included the
name and address of the location, general descriptive data about the location,
10 to 15 sample loactions at each location and about 100 data points at each
sample location. Synchronization is done once per week. They are using a low
cost Palm with only 8meg of memory.

Steve
PC Datasheet

Dear PCDatasheet,

I thoroughly exhausted the technique you suggest before
plunging into the dark matter of Arrays. The list box
would only refresh upon closing and re-opening the form,
but would not budge using any of the more familiar, and
much easier, techniques. In the meantime, I solved my own
problem and posted it in the previous thread for others
who might be interested. Take a look and tell me what you
think. It works quite well!

Oh, and my client may be interested in the Palm thing,
I'll ask him. Maybe you could tell me a bit more about how
it works so I can pass the information along to the good
doctor.

Thanks for the support. You provide a truly valuable
service to all us struggling and sometimes clueless VBA
programmers.

Gail

-----Original Message-----
Gail,

Gary is right - you're making it hard on yourself trying to do it with an array.
Just use an SQL statement as the row source of the list box and alter the
statement according to the search criteria entered by the user. Something like:

Dim SQLStr As String
Dim SelectStr As String
Dim WhereStr As String
SelectStr = "Select PatientID, PatientName From TblPatient "
Select Case SearchCriteria
Case "This"
WhereStr = "Where ...................." & Forms! MyForm!Somefiled
Case "That"
WhereStr = "Where ................." & Forms!MyForm! Somefiled
End Select
SQLStr = SelectStr & WhereStr
Me!NameOfListbox.Rowsource = SQLStr

You can now display the PatientName in the listbox and return PatientID by
setting the bound column to 1, columncount to 2 and column widths to 0;2.

By the way, do you have any need to use a Palm for collecting patient data and
then importing that data into your database? I do that kind of application.
Contact me if you are interested.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Gary,

Thank you for your prompt reply. Yes, it has turned into
a lot of work because the list box has to change
dynamically in response to the user selecting different
sets of search criteria, resulting in a different list of
Names everytime they search. When the user double- clicks a
Name in the list, I need to pass the ID field.

I have followed all that you recommend. The result is that
the Name field is displayed in both columns. I think it
has something to do with the line of code that
says "aryTestArray(intArrayCount) = ![Name]". I cannot
figure out how to demension the array to include the
second column. I have also tried the GetRows method of the
recordset, but cannot get it to fill the list at all.

Thanks for your suggestions.

Gail

-----Original Message-----
Gail,

Wow, that is a lot of work if you are just trying to add a
second column to the list box. If the ID is not already in
the stored query add it just to the right of your names
field. Now go to your list box and change the ColumnCount
to
be 2 in the properties box and then change you Column
widths
to something like 1.5";1" depending on how wide you want
each to display. I think that is all you need to do if you
are referring to your query by it's name.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
message I am using a callback function provided by
Microsoft Knowledge Base Article # 210442
for a list box that displays a single column list of
names
from a stored query. I want to include a second column
in
the list box that displays the ID number (datatype long)
for each row, making it a multi-column list box. I have
searched extensively for an example of an array that
will
return a multi-column list but have not found anything
that shows specifically how to do it. Here is the part
of
my code that return the Name field:

Select Case intCode
Case acLBInitialize
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT PatientID,
Name FROM qry_search_results")
intArrayCount = 0
ReDim Preserve aryTestArray(0)

' Fill the array.
With rst
intNumRec = rst.RecordCount
Do Until rst.EOF
' Fill the array row with the last
name.
aryTestArray(intArrayCount) = ! [Name]
' Increase the number of elements in
the
array
' by one to accommodate the next
record.
ReDim Preserve aryTestArray(UBound
(aryTestArray) + 1)
intArrayCount = intArrayCount + 1
.MoveNext
Loop
' Remove the remaining empty array row.
ReDim Preserve aryTestArray(UBound
(aryTestArray) - 1)
.Close
End With
db.Close
' View the array contents.
For intCounter = 0 To intArrayCount - 1
varValue = aryTestArray(intCounter)
Next intCounter
varRetVal = True
etc., etc.

Thanks for any enlightenment!

.

Click to expand...

.

Click to expand...

Guest · Nov 22, 2003

Dear PCDatasheet,

In theory, yes. However, I tried requerying the list box
before a new search without any luck. When the user clicks
the "Search" button, the underlying query to the list box
is re-written while the form is running (the query itself
is the end result of a series of "make queries" that
changes according to the criteria the user selects). I
could not get the list box to requery while the form was
running using any of the simpler methods, including the
GetRows function of the recordset. I'm sure it can be
done, but I could not do it without closing and re-opening
the form which I do not want to do. The callback was a LOT
of work, but it does a great job and I am very satisfied
with the end result.

I think your Palm database program is very good! My doctor
client is a bit of a technophile gadget lover and may
really like the idea of being able to enter patient data
with a few simple taps of his T Mobile Palm stylus. He can
then download the stored info into the computer, totally
bypassing the tedious and labor intensive process of data
entry that brings with it the inevitable human input
errors. The only possible down side that I can foresee is
that he has lost and replaced his Palm twice in the last
year. I like your palm program very much and may find
other suitable applications for its use. I will contact
you via email for further discussion.

Thanks again for your helpful input.

Gail

-----Original Message-----
Gail,

In regards to refreshing the listbox, all you would have needed to do is requery
the listbox right before searching for the list of patient names. The listbox
would have then displayed the patient names that fit the search criteria entered
by the user.

In regards to using the Palm to collect "field" data ---
Tables and interfaces to the tables are built in the Palm for collecting the
data with the Palm. A special database (Access) is created that interface
between a main Access database and the Palm. There is a synchronization process
that imports the data stored in the Palm to the main database. The main database
is designed to process that data in any of he ways Access processes data. Data
entry is on the Palm and to the extent possible is done with drop down lists
cretaed by the special database. Where drop down lists are not possible, data
entry is done on the Palm using the stylus and the built in keyboard. The
application provides for a means of collecting data with a device that fits in a
shirt pocket and a way to get the data into the main database without having to
rekey it again into the main database. The Palm needs to have the Palm OS3 or
higher. Memory requirements of the Palm is a function of the amount of data that
will be collected and stored on the Palm between synchronizations. My last Palm
project was for a team of foundation and soil inspectors who did residential and
commercial inspection services for insurance companies. The team was on the road
daily and would travel to 3 to 8 locations in a day. Data collected included the
name and address of the location, general descriptive data about the location,
10 to 15 sample loactions at each location and about 100 data points at each
sample location. Synchronization is done once per week. They are using a low
cost Palm with only 8meg of memory.

Steve
PC Datasheet

Dear PCDatasheet,

I thoroughly exhausted the technique you suggest before
plunging into the dark matter of Arrays. The list box
would only refresh upon closing and re-opening the form,
but would not budge using any of the more familiar, and
much easier, techniques. In the meantime, I solved my own
problem and posted it in the previous thread for others
who might be interested. Take a look and tell me what you
think. It works quite well!

Oh, and my client may be interested in the Palm thing,
I'll ask him. Maybe you could tell me a bit more about how
it works so I can pass the information along to the good
doctor.

Thanks for the support. You provide a truly valuable
service to all us struggling and sometimes clueless VBA
programmers.

Gail

-----Original Message-----
Gail,

Gary is right - you're making it hard on yourself

Click to expand...

trying
to do it with an array.

Just use an SQL statement as the row source of the list box and alter the
statement according to the search criteria entered by

Click to expand...

the
user. Something like:

Dim SQLStr As String
Dim SelectStr As String
Dim WhereStr As String
SelectStr = "Select PatientID, PatientName From TblPatient "
Select Case SearchCriteria
Case "This"
WhereStr = "Where ...................." & Forms! MyForm!Somefiled
Case "That"
WhereStr = "Where ................." & Forms!

Click to expand...

MyForm!
Somefiled

End Select
SQLStr = SelectStr & WhereStr
Me!NameOfListbox.Rowsource = SQLStr

You can now display the PatientName in the listbox and return PatientID by
setting the bound column to 1, columncount to 2 and column widths to 0;2.

By the way, do you have any need to use a Palm for collecting patient data and
then importing that data into your database? I do that kind of application.
Contact me if you are interested.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Gary,

Thank you for your prompt reply. Yes, it has turned into
a lot of work because the list box has to change
dynamically in response to the user selecting different
sets of search criteria, resulting in a different

Click to expand...

list
of

Names everytime they search. When the user double- clicks a
Name in the list, I need to pass the ID field.

I have followed all that you recommend. The result is that
the Name field is displayed in both columns. I think it
has something to do with the line of code that
says "aryTestArray(intArrayCount) = ![Name]". I cannot
figure out how to demension the array to include the
second column. I have also tried the GetRows method

Click to expand...

of
the

recordset, but cannot get it to fill the list at all.

Thanks for your suggestions.

Gail

-----Original Message-----
Gail,

Wow, that is a lot of work if you are just trying to add a
second column to the list box. If the ID is not already in
the stored query add it just to the right of your names
field. Now go to your list box and change the ColumnCount
to
be 2 in the properties box and then change you Column
widths
to something like 1.5";1" depending on how wide you want
each to display. I think that is all you need to do

Click to expand...

if
you

are referring to your query by it's name.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
message [email protected]...
I am using a callback function provided by
Microsoft Knowledge Base Article # 210442
for a list box that displays a single column list of
names
from a stored query. I want to include a second column
in
the list box that displays the ID number (datatype long)
for each row, making it a multi-column list box. I have
searched extensively for an example of an array that
will
return a multi-column list but have not found anything
that shows specifically how to do it. Here is the part
of
my code that return the Name field:

Select Case intCode
Case acLBInitialize
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT PatientID,
Name FROM qry_search_results")
intArrayCount = 0
ReDim Preserve aryTestArray(0)

' Fill the array.
With rst
intNumRec = rst.RecordCount
Do Until rst.EOF
' Fill the array row with the last
name.
aryTestArray(intArrayCount) = ! [Name]
' Increase the number of elements in
the
array
' by one to accommodate the next
record.
ReDim Preserve aryTestArray (UBound
(aryTestArray) + 1)
intArrayCount = intArrayCount + 1
.MoveNext
Loop
' Remove the remaining empty array row.
ReDim Preserve aryTestArray(UBound
(aryTestArray) - 1)
.Close
End With
db.Close
' View the array contents.
For intCounter = 0 To intArrayCount - 1
varValue = aryTestArray(intCounter)
Next intCounter
varRetVal = True
etc., etc.

Thanks for any enlightenment!

.

.

Click to expand...

Click to expand...

.

PC Datasheet · Nov 22, 2003

Comment To - <<The only possible down side that I can foresee is that he has
lost and replaced his Palm twice in the last year. >>

All he would lose is the data he entered since the last synchronization and of
course the replacement cost of the Palm. Once he has a new Palm, he would just
need to register it with the special database, run synchronization and he would
be able to collect data on the new Palm.

Steve
PC Datasheet

Dear PCDatasheet,

In theory, yes. However, I tried requerying the list box
before a new search without any luck. When the user clicks
the "Search" button, the underlying query to the list box
is re-written while the form is running (the query itself
is the end result of a series of "make queries" that
changes according to the criteria the user selects). I
could not get the list box to requery while the form was
running using any of the simpler methods, including the
GetRows function of the recordset. I'm sure it can be
done, but I could not do it without closing and re-opening
the form which I do not want to do. The callback was a LOT
of work, but it does a great job and I am very satisfied
with the end result.

I think your Palm database program is very good! My doctor
client is a bit of a technophile gadget lover and may
really like the idea of being able to enter patient data
with a few simple taps of his T Mobile Palm stylus. He can
then download the stored info into the computer, totally
bypassing the tedious and labor intensive process of data
entry that brings with it the inevitable human input
errors. The only possible down side that I can foresee is
that he has lost and replaced his Palm twice in the last
year. I like your palm program very much and may find
other suitable applications for its use. I will contact
you via email for further discussion.

Thanks again for your helpful input.

Gail

-----Original Message-----
Gail,

In regards to refreshing the listbox, all you would have needed to do is requery
the listbox right before searching for the list of patient names. The listbox
would have then displayed the patient names that fit the search criteria entered
by the user.

In regards to using the Palm to collect "field" data ---
Tables and interfaces to the tables are built in the Palm for collecting the
data with the Palm. A special database (Access) is created that interface
between a main Access database and the Palm. There is a synchronization process
that imports the data stored in the Palm to the main database. The main database
is designed to process that data in any of he ways Access processes data. Data
entry is on the Palm and to the extent possible is done with drop down lists
cretaed by the special database. Where drop down lists are not possible, data
entry is done on the Palm using the stylus and the built in keyboard. The
application provides for a means of collecting data with a device that fits in a
shirt pocket and a way to get the data into the main database without having to
rekey it again into the main database. The Palm needs to have the Palm OS3 or
higher. Memory requirements of the Palm is a function of the amount of data that
will be collected and stored on the Palm between synchronizations. My last Palm
project was for a team of foundation and soil inspectors who did residential and
commercial inspection services for insurance companies. The team was on the road
daily and would travel to 3 to 8 locations in a day. Data collected included the
name and address of the location, general descriptive data about the location,
10 to 15 sample loactions at each location and about 100 data points at each
sample location. Synchronization is done once per week. They are using a low
cost Palm with only 8meg of memory.

Steve
PC Datasheet

Dear PCDatasheet,

I thoroughly exhausted the technique you suggest before
plunging into the dark matter of Arrays. The list box
would only refresh upon closing and re-opening the form,
but would not budge using any of the more familiar, and
much easier, techniques. In the meantime, I solved my own
problem and posted it in the previous thread for others
who might be interested. Take a look and tell me what you
think. It works quite well!

Oh, and my client may be interested in the Palm thing,
I'll ask him. Maybe you could tell me a bit more about how
it works so I can pass the information along to the good
doctor.

Thanks for the support. You provide a truly valuable
service to all us struggling and sometimes clueless VBA
programmers.

Gail

-----Original Message-----
Gail,

Gary is right - you're making it hard on yourself trying
to do it with an array.
Just use an SQL statement as the row source of the list
box and alter the
statement according to the search criteria entered by the
user. Something like:

Dim SQLStr As String
Dim SelectStr As String
Dim WhereStr As String
SelectStr = "Select PatientID, PatientName From
TblPatient "
Select Case SearchCriteria
Case "This"
WhereStr = "Where ...................." & Forms!
MyForm!Somefiled
Case "That"
WhereStr = "Where ................." & Forms! MyForm!
Somefiled
End Select
SQLStr = SelectStr & WhereStr
Me!NameOfListbox.Rowsource = SQLStr

You can now display the PatientName in the listbox and
return PatientID by
setting the bound column to 1, columncount to 2 and
column widths to 0;2.

By the way, do you have any need to use a Palm for
collecting patient data and
then importing that data into your database? I do that
kind of application.
Contact me if you are interested.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
www.pcdatasheet.com

message
Gary,

Thank you for your prompt reply. Yes, it has turned into
a lot of work because the list box has to change
dynamically in response to the user selecting different
sets of search criteria, resulting in a different list
of
Names everytime they search. When the user double-
clicks a
Name in the list, I need to pass the ID field.

I have followed all that you recommend. The result is
that
the Name field is displayed in both columns. I think it
has something to do with the line of code that
says "aryTestArray(intArrayCount) = ![Name]". I cannot
figure out how to demension the array to include the
second column. I have also tried the GetRows method of
the
recordset, but cannot get it to fill the list at all.

Thanks for your suggestions.

Gail

-----Original Message-----
Gail,

Wow, that is a lot of work if you are just trying to
add a
second column to the list box. If the ID is not
already in
the stored query add it just to the right of your names
field. Now go to your list box and change the
ColumnCount
to
be 2 in the properties box and then change you Column
widths
to something like 1.5";1" depending on how wide you
want
each to display. I think that is all you need to do if
you
are referring to your query by it's name.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
message [email protected]...
I am using a callback function provided by
Microsoft Knowledge Base Article # 210442
for a list box that displays a single column list of
names
from a stored query. I want to include a second
column
in
the list box that displays the ID number (datatype
long)
for each row, making it a multi-column list box. I
have
searched extensively for an example of an array that
will
return a multi-column list but have not found
anything
that shows specifically how to do it. Here is the
part
of
my code that return the Name field:

Select Case intCode
Case acLBInitialize
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT
PatientID,
Name FROM qry_search_results")
intArrayCount = 0
ReDim Preserve aryTestArray(0)

' Fill the array.
With rst
intNumRec = rst.RecordCount
Do Until rst.EOF
' Fill the array row with the last
name.
aryTestArray(intArrayCount) = !
[Name]
' Increase the number of elements in
the
array
' by one to accommodate the next
record.
ReDim Preserve aryTestArray (UBound
(aryTestArray) + 1)
intArrayCount = intArrayCount + 1
.MoveNext
Loop
' Remove the remaining empty array row.
ReDim Preserve aryTestArray(UBound
(aryTestArray) - 1)
.Close
End With
db.Close
' View the array contents.
For intCounter = 0 To intArrayCount - 1
varValue = aryTestArray(intCounter)
Next intCounter
varRetVal = True
etc., etc.

Thanks for any enlightenment!

.

.

Click to expand...

.

Click to expand...

TC · Nov 23, 2003

Ok, well done!

My suggestion to use GetRows() was not intended to get the data directly
into the listbox. It was intended to get the data into the static array.
GetRows avoids the need for the manual loop that you coded, to fill the
array.

Cheers,
TC

Dear TC,

Yes, you are right, I left off the very important Case
aclbGetValue, for the sake of being brief. The GetRows
method would not return any values in the list box--I
tried that before delving into the messy business of
arrays. And you are also correct about the importance of
declaring the variables as Static, otherwise the results
will never show up in the list box even if you have the
corect algorithm.

The good news is that I have since solved my problem and
have included the entire function in the thread dated Nov
21 2003 8:52 p.m. Please take a look at it if you are
interested in how I did it.

Thanks so much for your input, your support is greatly
appreciated.

Best regards,

Gail

-----Original Message-----
Well, the "etc., etc." at the end, is the key part!

The acLBGetValue branch (which you have not shown) is what returns the row &
column values. On each call, you must use the values of the row and col
parameters, to determine which row & column values the listbox is asking
for. Be aware that you can >not< (in general) assume that the listbox will
make the minimum number of calls required, & make those calls in any
"sensible" order. In certain cases it can make more calls than are really
required, and it can make those calls in very strange orders! So you must
code the acLBGetValue branch to work properly for >any< number or sequence
of calls.

PS:

- You could probably replace all your looping code with a single call to the
GetRows() function.

- Have you declared your array as Static? Remember that it must retain its
content over seperate calls to the callback function.

- As the other respondent said, there might be a better way of doing what
you want. I have not considered that issue.

HTH,
TC

I am using a callback function provided by
Microsoft Knowledge Base Article # 210442
for a list box that displays a single column list of names
from a stored query. I want to include a second column in
the list box that displays the ID number (datatype long)
for each row, making it a multi-column list box. I have
searched extensively for an example of an array that will
return a multi-column list but have not found anything
that shows specifically how to do it. Here is the part of
my code that return the Name field:

Select Case intCode
Case acLBInitialize
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT PatientID,
Name FROM qry_search_results")
intArrayCount = 0
ReDim Preserve aryTestArray(0)

' Fill the array.
With rst
intNumRec = rst.RecordCount
Do Until rst.EOF
' Fill the array row with the last name.
aryTestArray(intArrayCount) = ![Name]
' Increase the number of elements in the
array
' by one to accommodate the next record.
ReDim Preserve aryTestArray(UBound
(aryTestArray) + 1)
intArrayCount = intArrayCount + 1
.MoveNext
Loop
' Remove the remaining empty array row.
ReDim Preserve aryTestArray(UBound
(aryTestArray) - 1)
.Close
End With
db.Close
' View the array contents.
For intCounter = 0 To intArrayCount - 1
varValue = aryTestArray(intCounter)
Next intCounter
varRetVal = True
etc., etc.

Thanks for any enlightenment!

Click to expand...

.

Click to expand...

Guest · Dec 1, 2003

TC,

I know GetRows() will work and it would probably be easier
to use than the loop, however I played with the GetRows
function for quite some time without any success (not
because of the function but because of my lack of
knowledge). You know how it goes--you use the first thing
you find that works well. Now that I've gotten the loop to
work, I'm sure I could get the GetRows function to work.

Thanks very much for your help!

Gail

-----Original Message-----
Ok, well done!

My suggestion to use GetRows() was not intended to get the data directly
into the listbox. It was intended to get the data into the static array.
GetRows avoids the need for the manual loop that you coded, to fill the
array.

Cheers,
TC

Dear TC,

Yes, you are right, I left off the very important Case
aclbGetValue, for the sake of being brief. The GetRows
method would not return any values in the list box--I
tried that before delving into the messy business of
arrays. And you are also correct about the importance of
declaring the variables as Static, otherwise the results
will never show up in the list box even if you have the
corect algorithm.

The good news is that I have since solved my problem and
have included the entire function in the thread dated Nov
21 2003 8:52 p.m. Please take a look at it if you are
interested in how I did it.

Thanks so much for your input, your support is greatly
appreciated.

Best regards,

Gail

-----Original Message-----
Well, the "etc., etc." at the end, is the key part!

The acLBGetValue branch (which you have not shown) is what returns the row &
column values. On each call, you must use the values of the row and col
parameters, to determine which row & column values the listbox is asking
for. Be aware that you can >not< (in general) assume

Click to expand...

that
the listbox will

make the minimum number of calls required, & make those calls in any
"sensible" order. In certain cases it can make more

Click to expand...

calls
than are really

required, and it can make those calls in very strange orders! So you must
code the acLBGetValue branch to work properly for >any< number or sequence
of calls.

PS:

- You could probably replace all your looping code

Click to expand...

with a
single call to the

GetRows() function.

- Have you declared your array as Static? Remember that it must retain its
content over seperate calls to the callback function.

- As the other respondent said, there might be a better way of doing what
you want. I have not considered that issue.

HTH,
TC

I am using a callback function provided by
Microsoft Knowledge Base Article # 210442
for a list box that displays a single column list of names
from a stored query. I want to include a second

Click to expand...

column
in

the list box that displays the ID number (datatype long)
for each row, making it a multi-column list box. I have
searched extensively for an example of an array that will
return a multi-column list but have not found anything
that shows specifically how to do it. Here is the

Click to expand...

part
of

my code that return the Name field:

Select Case intCode
Case acLBInitialize
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT PatientID,
Name FROM qry_search_results")
intArrayCount = 0
ReDim Preserve aryTestArray(0)

' Fill the array.
With rst
intNumRec = rst.RecordCount
Do Until rst.EOF
' Fill the array row with the last name.
aryTestArray(intArrayCount) = ! [Name]
' Increase the number of elements in the
array
' by one to accommodate the next record.
ReDim Preserve aryTestArray(UBound
(aryTestArray) + 1)
intArrayCount = intArrayCount + 1
.MoveNext
Loop
' Remove the remaining empty array row.
ReDim Preserve aryTestArray(UBound
(aryTestArray) - 1)
.Close
End With
db.Close
' View the array contents.
For intCounter = 0 To intArrayCount - 1
varValue = aryTestArray(intCounter)
Next intCounter
varRetVal = True
etc., etc.

Thanks for any enlightenment!

.

Click to expand...

Click to expand...

.

TC · Dec 2, 2003

Well done. However, if something does not work, it always pays off, in my
experience, to spend the time to find out >why< it does not work. This is
the best way to increase one's technical expertise in any software product,
IMO.

Cheers,
TC

TC,

I know GetRows() will work and it would probably be easier
to use than the loop, however I played with the GetRows
function for quite some time without any success (not
because of the function but because of my lack of
knowledge). You know how it goes--you use the first thing
you find that works well. Now that I've gotten the loop to
work, I'm sure I could get the GetRows function to work.

Thanks very much for your help!

Gail

-----Original Message-----
Ok, well done!

My suggestion to use GetRows() was not intended to get the data directly
into the listbox. It was intended to get the data into the static array.
GetRows avoids the need for the manual loop that you coded, to fill the
array.

Cheers,
TC

Dear TC,

Yes, you are right, I left off the very important Case
aclbGetValue, for the sake of being brief. The GetRows
method would not return any values in the list box--I
tried that before delving into the messy business of
arrays. And you are also correct about the importance of
declaring the variables as Static, otherwise the results
will never show up in the list box even if you have the
corect algorithm.

The good news is that I have since solved my problem and
have included the entire function in the thread dated Nov
21 2003 8:52 p.m. Please take a look at it if you are
interested in how I did it.

Thanks so much for your input, your support is greatly
appreciated.

Best regards,

Gail
-----Original Message-----
Well, the "etc., etc." at the end, is the key part!

The acLBGetValue branch (which you have not shown) is
what returns the row &
column values. On each call, you must use the values of
the row and col
parameters, to determine which row & column values the
listbox is asking
for. Be aware that you can >not< (in general) assume that
the listbox will
make the minimum number of calls required, & make those
calls in any
"sensible" order. In certain cases it can make more calls
than are really
required, and it can make those calls in very strange
orders! So you must
code the acLBGetValue branch to work properly for >any<
number or sequence
of calls.

PS:

- You could probably replace all your looping code with a
single call to the
GetRows() function.

- Have you declared your array as Static? Remember that
it must retain its
content over seperate calls to the callback function.

- As the other respondent said, there might be a better
way of doing what
you want. I have not considered that issue.

HTH,
TC

message
I am using a callback function provided by
Microsoft Knowledge Base Article # 210442
for a list box that displays a single column list of
names
from a stored query. I want to include a second column
in
the list box that displays the ID number (datatype long)
for each row, making it a multi-column list box. I have
searched extensively for an example of an array that
will
return a multi-column list but have not found anything
that shows specifically how to do it. Here is the part
of
my code that return the Name field:

Select Case intCode
Case acLBInitialize
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT PatientID,
Name FROM qry_search_results")
intArrayCount = 0
ReDim Preserve aryTestArray(0)

' Fill the array.
With rst
intNumRec = rst.RecordCount
Do Until rst.EOF
' Fill the array row with the last
name.
aryTestArray(intArrayCount) = ! [Name]
' Increase the number of elements in
the
array
' by one to accommodate the next
record.
ReDim Preserve aryTestArray(UBound
(aryTestArray) + 1)
intArrayCount = intArrayCount + 1
.MoveNext
Loop
' Remove the remaining empty array row.
ReDim Preserve aryTestArray(UBound
(aryTestArray) - 1)
.Close
End With
db.Close
' View the array contents.
For intCounter = 0 To intArrayCount - 1
varValue = aryTestArray(intCounter)
Next intCounter
varRetVal = True
etc., etc.

Thanks for any enlightenment!

.

Click to expand...

.

Click to expand...

Gary Miller · Dec 2, 2003

Great encouragement and advice!!!

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

TC said:
Well done. However, if something does not work, it always pays off, in my
experience, to spend the time to find out >why< it does not work. This is
the best way to increase one's technical expertise in any software product,
IMO.

Cheers,
TC

TC,

I know GetRows() will work and it would probably be easier
to use than the loop, however I played with the GetRows
function for quite some time without any success (not
because of the function but because of my lack of
knowledge). You know how it goes--you use the first thing
you find that works well. Now that I've gotten the loop to
work, I'm sure I could get the GetRows function to work.

Thanks very much for your help!

Gail

-----Original Message-----
Ok, well done!

My suggestion to use GetRows() was not intended to get the data directly
into the listbox. It was intended to get the data into the static array.
GetRows avoids the need for the manual loop that you coded, to fill the
array.

Cheers,
TC

Dear TC,

Yes, you are right, I left off the very important Case
aclbGetValue, for the sake of being brief. The GetRows
method would not return any values in the list box--I
tried that before delving into the messy business of
arrays. And you are also correct about the importance of
declaring the variables as Static, otherwise the results
will never show up in the list box even if you have the
corect algorithm.

The good news is that I have since solved my problem and
have included the entire function in the thread dated Nov
21 2003 8:52 p.m. Please take a look at it if you are
interested in how I did it.

Thanks so much for your input, your support is greatly
appreciated.

Best regards,

Gail
-----Original Message-----
Well, the "etc., etc." at the end, is the key part!

The acLBGetValue branch (which you have not shown) is
what returns the row &
column values. On each call, you must use the values of
the row and col
parameters, to determine which row & column values the
listbox is asking
for. Be aware that you can >not< (in general) assume that
the listbox will
make the minimum number of calls required, & make those
calls in any
"sensible" order. In certain cases it can make more calls
than are really
required, and it can make those calls in very strange
orders! So you must
code the acLBGetValue branch to work properly for any<
number or sequence
of calls.

PS:

- You could probably replace all your looping code with a
single call to the
GetRows() function.

- Have you declared your array as Static? Remember that
it must retain its
content over seperate calls to the callback function.

- As the other respondent said, there might be a better
way of doing what
you want. I have not considered that issue.

HTH,
TC

message
I am using a callback function provided by
Microsoft Knowledge Base Article # 210442
for a list box that displays a single column list of
names
from a stored query. I want to include a second column
in
the list box that displays the ID number (datatype long)
for each row, making it a multi-column list box. I have
searched extensively for an example of an array that
will
return a multi-column list but have not found anything
that shows specifically how to do it. Here is the part
of
my code that return the Name field:

Select Case intCode
Case acLBInitialize
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT PatientID,
Name FROM qry_search_results")
intArrayCount = 0
ReDim Preserve aryTestArray(0)

' Fill the array.
With rst
intNumRec = rst.RecordCount
Do Until rst.EOF
' Fill the array row with the last
name.
aryTestArray(intArrayCount) = ! [Name]
' Increase the number of elements in
the
array
' by one to accommodate the next
record.
ReDim Preserve aryTestArray(UBound
(aryTestArray) + 1)
intArrayCount = intArrayCount + 1
.MoveNext
Loop
' Remove the remaining empty array row.
ReDim Preserve aryTestArray(UBound
(aryTestArray) - 1)
.Close
End With
db.Close
' View the array contents.
For intCounter = 0 To intArrayCount - 1
varValue = aryTestArray(intCounter)
Next intCounter
varRetVal = True
etc., etc.

Thanks for any enlightenment!

.

.

Click to expand...

Click to expand...

cafe · Dec 2, 2003

Thanks Gary.

One of the biggest mistakes that people make, IMO, is to find that something
does not work, then just re-jig it at random until it does work. (But that
is not a go at you, Gail.) This leads to no increase in product knowledge. I
prefer to chase down every bug, without exception, no matter how easy it
would be to work around, until I find out what is happening.

99.9% of the time, this leads to learning a new fact about the product, or
finding what mistake I've made. 1 time in 1000, it may lead to an actual bug
in the product. Whatever the outcome, I can avoid that problem in future, &
my product knowledge increases accordingly.

I worked for many years as a professional software developer. I found very
few colleagues who took this approach. As a result, many of my colleagues
had a surprisingly low level of product knowledge, & tended to make the same
coding mistakes over & over again. They could work for several years in a
product, & seem to know not much more about it at the end of those years,
than they did at the beginning!

Cheers,
TC

Gary Miller said:
Great encouragement and advice!!!

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

TC said:

Well done. However, if something does not work, it always pays off, in my
experience, to spend the time to find out >why< it does not work. This is
the best way to increase one's technical expertise in any software product,
IMO.

Cheers,
TC

TC,

I know GetRows() will work and it would probably be easier
to use than the loop, however I played with the GetRows
function for quite some time without any success (not
because of the function but because of my lack of
knowledge). You know how it goes--you use the first thing
you find that works well. Now that I've gotten the loop to
work, I'm sure I could get the GetRows function to work.

Thanks very much for your help!

Gail

-----Original Message-----
Ok, well done!

My suggestion to use GetRows() was not intended to get
the data directly
into the listbox. It was intended to get the data into
the static array.
GetRows avoids the need for the manual loop that you
coded, to fill the
array.

Cheers,
TC

Dear TC,

Yes, you are right, I left off the very important Case
aclbGetValue, for the sake of being brief. The GetRows
method would not return any values in the list box--I
tried that before delving into the messy business of
arrays. And you are also correct about the importance of
declaring the variables as Static, otherwise the results
will never show up in the list box even if you have the
corect algorithm.

The good news is that I have since solved my problem and
have included the entire function in the thread dated
Nov
21 2003 8:52 p.m. Please take a look at it if you are
interested in how I did it.

Thanks so much for your input, your support is greatly
appreciated.

Best regards,

Gail
-----Original Message-----
Well, the "etc., etc." at the end, is the key part!

The acLBGetValue branch (which you have not shown) is
what returns the row &
column values. On each call, you must use the values of
the row and col
parameters, to determine which row & column values the
listbox is asking
for. Be aware that you can >not< (in general) assume
that
the listbox will
make the minimum number of calls required, & make those
calls in any
"sensible" order. In certain cases it can make more
calls
than are really
required, and it can make those calls in very strange
orders! So you must
code the acLBGetValue branch to work properly for any<
number or sequence
of calls.

PS:

- You could probably replace all your looping code
with a
single call to the
GetRows() function.

- Have you declared your array as Static? Remember that
it must retain its
content over seperate calls to the callback function.

- As the other respondent said, there might be a better
way of doing what
you want. I have not considered that issue.

HTH,
TC

message
I am using a callback function provided by
Microsoft Knowledge Base Article # 210442
for a list box that displays a single column list of
names
from a stored query. I want to include a second
column
in
the list box that displays the ID number (datatype
long)
for each row, making it a multi-column list box. I
have
searched extensively for an example of an array that
will
return a multi-column list but have not found
anything
that shows specifically how to do it. Here is the
part
of
my code that return the Name field:

Select Case intCode
Case acLBInitialize
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT
PatientID,
Name FROM qry_search_results")
intArrayCount = 0
ReDim Preserve aryTestArray(0)

' Fill the array.
With rst
intNumRec = rst.RecordCount
Do Until rst.EOF
' Fill the array row with the last
name.
aryTestArray(intArrayCount) = !
[Name]
' Increase the number of elements in
the
array
' by one to accommodate the next
record.
ReDim Preserve aryTestArray(UBound
(aryTestArray) + 1)
intArrayCount = intArrayCount + 1
.MoveNext
Loop
' Remove the remaining empty array row.
ReDim Preserve aryTestArray(UBound
(aryTestArray) - 1)
.Close
End With
db.Close
' View the array contents.
For intCounter = 0 To intArrayCount - 1
varValue = aryTestArray(intCounter)
Next intCounter
varRetVal = True
etc., etc.

Thanks for any enlightenment!

.

.

Click to expand...

Click to expand...

Gail · Dec 3, 2003

TC,

Sage advice! As for technical expertise, it is a far flung
star for me. I will figure out how to make GetRows() work
because I think it is probably a better solution than the
loop, and once figured out can be used for future unbound
list box functions.

Thanks for the encouragement.

Gail

TC · Dec 3, 2003

Gail said:
TC,

Sage advice! As for technical expertise, it is a far flung
star for me. I will figure out how to make GetRows() work
because I think it is probably a better solution than the
loop, and once figured out can be used for future unbound
list box functions.

Gail, that is it >precisely<. You take longer to work it out >now< - but
that saves you time repeatedly, for ever afterwards, whenever you need to
use it again.

Cheers!
TC
(off for the day)

Gail · Dec 4, 2003

TC,
I am also occassionally guilty of the stab-in-the-dark
technique of programming. It stems from frustration and
lack of understanding. However in my own defense, I must
say that I ALWAYS go back and find out why something
worked or didn't, if there is a better way of achieving my
goal, streamlining my code, etc. My programming education
has been spotty and unorthodox but my interest in creating
well designed programs is genuine.

I discovered another built-in ADO function, GetString(),
which is similar to GetRows() but returns the recordset by
declaring only 3 parameters. It returns everything in
strings, though.

Gail

TC said:
-----Original Message-----
Thanks Gary.

One of the biggest mistakes that people make, IMO, is to find that something
does not work, then just re-jig it at random until it does work. (But that
is not a go at you, Gail.) This leads to no increase in product knowledge. I
prefer to chase down every bug, without exception, no matter how easy it
would be to work around, until I find out what is happening.

99.9% of the time, this leads to learning a new fact about the product, or
finding what mistake I've made. 1 time in 1000, it may lead to an actual bug
in the product. Whatever the outcome, I can avoid that problem in future, &
my product knowledge increases accordingly.

I worked for many years as a professional software developer. I found very
few colleagues who took this approach. As a result, many of my colleagues
had a surprisingly low level of product knowledge, & tended to make the same
coding mistakes over & over again. They could work for several years in a
product, & seem to know not much more about it at the end of those years,
than they did at the beginning!

Cheers,
TC

Great encouragement and advice!!!

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

TC said:

Well done. However, if something does not work, it

Click to expand...

always
pays off, in my

experience, to spend the time to find out >why< it

Click to expand...

does
not work. This is

the best way to increase one's technical expertise in

Click to expand...

any
software product,

IMO.

Cheers,
TC

TC,

I know GetRows() will work and it would probably be easier
to use than the loop, however I played with the GetRows
function for quite some time without any success (not
because of the function but because of my lack of
knowledge). You know how it goes--you use the first thing
you find that works well. Now that I've gotten the

Click to expand...

loop
to

work, I'm sure I could get the GetRows function to work.

Thanks very much for your help!

Gail

-----Original Message-----
Ok, well done!

My suggestion to use GetRows() was not intended to get
the data directly
into the listbox. It was intended to get the data into
the static array.
GetRows avoids the need for the manual loop that you
coded, to fill the
array.

Cheers,
TC

Dear TC,

Yes, you are right, I left off the very important Case
aclbGetValue, for the sake of being brief. The GetRows
method would not return any values in the list box--I
tried that before delving into the messy business of
arrays. And you are also correct about the

Click to expand...

importance
of

declaring the variables as Static, otherwise the results
will never show up in the list box even if you

Click to expand...

have
the

corect algorithm.

The good news is that I have since solved my

Click to expand...

problem
and

have included the entire function in the thread dated
Nov
21 2003 8:52 p.m. Please take a look at it if you are
interested in how I did it.

Thanks so much for your input, your support is greatly
appreciated.

Best regards,

Gail
-----Original Message-----
Well, the "etc., etc." at the end, is the key part!

The acLBGetValue branch (which you have not

Click to expand...

shown)
is

what returns the row &
column values. On each call, you must use the

Click to expand...

values
of

the row and col
parameters, to determine which row & column

Click to expand...

values
the

listbox is asking
for. Be aware that you can >not< (in general) assume
that
the listbox will
make the minimum number of calls required, &

Click to expand...

make
those

calls in any
"sensible" order. In certain cases it can make more
calls
than are really
required, and it can make those calls in very strange
orders! So you must
code the acLBGetValue branch to work properly for
any<
number or sequence
of calls.

PS:

- You could probably replace all your looping code
with a
single call to the
GetRows() function.

- Have you declared your array as Static?

Click to expand...

Remember
that

it must retain its
content over seperate calls to the callback function.

- As the other respondent said, there might be a better
way of doing what
you want. I have not considered that issue.

HTH,
TC

"Gail" <[email protected]>

Click to expand...

wrote
in

message
I am using a callback function provided by
Microsoft Knowledge Base Article # 210442
for a list box that displays a single column

Click to expand...

list
of

names
from a stored query. I want to include a second
column
in
the list box that displays the ID number (datatype
long)
for each row, making it a multi-column list box. I
have
searched extensively for an example of an

Click to expand...

array
that

will
return a multi-column list but have not found
anything
that shows specifically how to do it. Here is the
part
of
my code that return the Name field:

Select Case intCode
Case acLBInitialize
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT
PatientID,
Name FROM qry_search_results")
intArrayCount = 0
ReDim Preserve aryTestArray(0)

' Fill the array.
With rst
intNumRec = rst.RecordCount
Do Until rst.EOF
' Fill the array row with the last
name.
aryTestArray(intArrayCount) = !
[Name]
' Increase the number of

Click to expand...

elements
in

the
array
' by one to accommodate the next
record.
ReDim Preserve aryTestArray(UBound
(aryTestArray) + 1)
intArrayCount = intArrayCount + 1
.MoveNext
Loop
' Remove the remaining empty array row.
ReDim Preserve aryTestArray(UBound
(aryTestArray) - 1)
.Close
End With
db.Close
' View the array contents.
For intCounter = 0 To intArrayCount - 1
varValue = aryTestArray (intCounter)
Next intCounter
varRetVal = True
etc., etc.

Thanks for any enlightenment!

.

.

Click to expand...

Click to expand...

.

TC · Dec 5, 2003

Gail, if you always go back & find the cause, then you are way better off
than many/most people. This is the key technique (IMO) for growing your
knowledge of any software product. And by "you", I mean, anyone
(generically) - you, me, whoever.

Cheers

TC

Gail said:
TC,
I am also occassionally guilty of the stab-in-the-dark
technique of programming. It stems from frustration and
lack of understanding. However in my own defense, I must
say that I ALWAYS go back and find out why something
worked or didn't, if there is a better way of achieving my
goal, streamlining my code, etc. My programming education
has been spotty and unorthodox but my interest in creating
well designed programs is genuine.

I discovered another built-in ADO function, GetString(),
which is similar to GetRows() but returns the recordset by
declaring only 3 parameters. It returns everything in
strings, though.

Gail

-----Original Message-----
Thanks Gary.

One of the biggest mistakes that people make, IMO, is to find that something
does not work, then just re-jig it at random until it does work. (But that
is not a go at you, Gail.) This leads to no increase in product knowledge. I
prefer to chase down every bug, without exception, no matter how easy it
would be to work around, until I find out what is happening.

99.9% of the time, this leads to learning a new fact about the product, or
finding what mistake I've made. 1 time in 1000, it may lead to an actual bug
in the product. Whatever the outcome, I can avoid that problem in future, &
my product knowledge increases accordingly.

I worked for many years as a professional software developer. I found very
few colleagues who took this approach. As a result, many of my colleagues
had a surprisingly low level of product knowledge, & tended to make the same
coding mistakes over & over again. They could work for several years in a
product, & seem to know not much more about it at the end of those years,
than they did at the beginning!

Cheers,
TC

Great encouragement and advice!!!

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Well done. However, if something does not work, it always
pays off, in my
experience, to spend the time to find out >why< it does
not work. This is
the best way to increase one's technical expertise in any
software product,
IMO.

Cheers,
TC

TC,

I know GetRows() will work and it would probably be
easier
to use than the loop, however I played with the GetRows
function for quite some time without any success (not
because of the function but because of my lack of
knowledge). You know how it goes--you use the first
thing
you find that works well. Now that I've gotten the loop
to
work, I'm sure I could get the GetRows function to work.

Thanks very much for your help!

Gail

-----Original Message-----
Ok, well done!

My suggestion to use GetRows() was not intended to get
the data directly
into the listbox. It was intended to get the data into
the static array.
GetRows avoids the need for the manual loop that you
coded, to fill the
array.

Cheers,
TC

Dear TC,

Yes, you are right, I left off the very important
Case
aclbGetValue, for the sake of being brief. The
GetRows
method would not return any values in the list box--I
tried that before delving into the messy business of
arrays. And you are also correct about the importance
of
declaring the variables as Static, otherwise the
results
will never show up in the list box even if you have
the
corect algorithm.

The good news is that I have since solved my problem
and
have included the entire function in the thread dated
Nov
21 2003 8:52 p.m. Please take a look at it if you are
interested in how I did it.

Thanks so much for your input, your support is
greatly
appreciated.

Best regards,

Gail
-----Original Message-----
Well, the "etc., etc." at the end, is the key part!

The acLBGetValue branch (which you have not shown)
is
what returns the row &
column values. On each call, you must use the values
of
the row and col
parameters, to determine which row & column values
the
listbox is asking
for. Be aware that you can >not< (in general) assume
that
the listbox will
make the minimum number of calls required, & make
those
calls in any
"sensible" order. In certain cases it can make more
calls
than are really
required, and it can make those calls in very
strange
orders! So you must
code the acLBGetValue branch to work properly for
any<
number or sequence
of calls.

PS:

- You could probably replace all your looping code
with a
single call to the
GetRows() function.

- Have you declared your array as Static? Remember
that
it must retain its
content over seperate calls to the callback
function.

- As the other respondent said, there might be a
better
way of doing what
you want. I have not considered that issue.

HTH,
TC

in
message
I am using a callback function provided by
Microsoft Knowledge Base Article # 210442
for a list box that displays a single column list
of
names
from a stored query. I want to include a second
column
in
the list box that displays the ID number (datatype
long)
for each row, making it a multi-column list box. I
have
searched extensively for an example of an array
that
will
return a multi-column list but have not found
anything
that shows specifically how to do it. Here is the
part
of
my code that return the Name field:

Select Case intCode
Case acLBInitialize
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT
PatientID,
Name FROM qry_search_results")
intArrayCount = 0
ReDim Preserve aryTestArray(0)

' Fill the array.
With rst
intNumRec = rst.RecordCount
Do Until rst.EOF
' Fill the array row with the
last
name.
aryTestArray(intArrayCount) = !
[Name]
' Increase the number of elements
in
the
array
' by one to accommodate the next
record.
ReDim Preserve
aryTestArray(UBound
(aryTestArray) + 1)
intArrayCount = intArrayCount + 1
.MoveNext
Loop
' Remove the remaining empty array row.
ReDim Preserve aryTestArray(UBound
(aryTestArray) - 1)
.Close
End With
db.Close
' View the array contents.
For intCounter = 0 To intArrayCount - 1
varValue = aryTestArray (intCounter)
Next intCounter
varRetVal = True
etc., etc.

Thanks for any enlightenment!

.

.

Click to expand...

.

Click to expand...

<subscript out of range>	6	Nov 20, 2003
Problem with Array	2	Nov 18, 2003
Unable to display values in 2nd list box from 1st list box selection	2	Jan 22, 2017
Populate multi-column combobox via callback function	2	Oct 31, 2005
Convert list to UPPER, lower & Proper cases.	21	Apr 6, 2014
callback function for filenames into listbox	3	Aug 14, 2008
Search an array of sheets for an array of numbers & return count of numbers	11	May 13, 2014
Callback function not working	6	Aug 20, 2006

Callback for MultiColumn List Box

Gail

Gary Miller

Gail

PC Datasheet

TC

Gail

Guest

Guest

PC Datasheet

Guest

PC Datasheet

TC

Guest

TC

Gary Miller

cafe

Gail

TC

Gail

TC

Ask a Question

Similar Threads