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!

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" <(E-Mail Removed)> wrote in
message news:024001c3b073$329b44f0$(E-Mail Removed)...
> 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,

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" <(E-Mail Removed)> wrote in
>message news:024001c3b073$329b44f0$(E-Mail Removed)...
>> 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!
>>
>
>
>.
>

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 Removed)
www.pcdatasheet.com

"Gail" <(E-Mail Removed)> wrote in message
news:03dd01c3b08d$c8f850e0$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
> >message news:024001c3b073$329b44f0$(E-Mail Removed)...
> >> 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!
> >>
> >
> >
> >.
> >

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" <(E-Mail Removed)> wrote in message
news:024001c3b073$329b44f0$(E-Mail Removed)...
> 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,

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






>-----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" <(E-Mail Removed)> wrote in
>message news:024001c3b073$329b44f0$(E-Mail Removed)...
>> 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!
>>
>
>
>.
>

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 Removed)
> www.pcdatasheet.com
>
>"Gail" <(E-Mail Removed)> wrote in
message
>news:03dd01c3b08d$c8f850e0$(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in
>> >message news:024001c3b073$329b44f0$(E-Mail Removed)...
>> >> 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!
>> >>
>> >
>> >
>> >.
>> >
>
>
>.
>

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
>
>
>"Gail" <(E-Mail Removed)> wrote in
message
>news:024001c3b073$329b44f0$(E-Mail Removed)...
>> 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!
>>
>
>
>.
>

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


<(E-Mail Removed)> wrote in message
news:055501c3b0b7$3f5b2ef0$(E-Mail Removed)...
> 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 Removed)
> > www.pcdatasheet.com
> >
> >"Gail" <(E-Mail Removed)> wrote in
> message
> >news:03dd01c3b08d$c8f850e0$(E-Mail Removed)...
> >> 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" <(E-Mail Removed)> wrote in
> >> >message news:024001c3b073$329b44f0$(E-Mail Removed)...
> >> >> 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!
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

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
>
>
><(E-Mail Removed)> wrote in message
>news:055501c3b0b7$3f5b2ef0$(E-Mail Removed)...
>> 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 Removed)
>> > www.pcdatasheet.com
>> >
>> >"Gail" <(E-Mail Removed)> wrote in
>> message
>> >news:03dd01c3b08d$c8f850e0$(E-Mail Removed)...
>> >> 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" <(E-Mail Removed)> wrote
in
>> >> >message news:024001c3b073$329b44f0
$(E-Mail Removed)...
>> >> >> 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!
>> >> >>
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>
>.
>