Semicolon Generated List

[Breezwell]

Hi everyone,

Here is a little something I have been trying to implement, however I am not sure I am on the right path.

To start, I have a query of product numbers such as the following:

196334
194778
196225
....

What I would like to do is create a semicolon separated list of these numbers such as:

196334;194778;196225

These numbers, I am assuming, would need to be pulled from my query and stored in an array so that I could present this list in, say, a MsgBox text field, so the user could select the entire list and paste the list into another text control.

I am not sure how to pull this data from my query and put the product codes into the array to achieve the desired result. I am thinking that once I have this array, I can use strconv() to create the actual string representation?

Thanks for any pointers or suggestions.

[FishVal]

Hello, Breezwell.

Take a look at the following howtos
Producing a List from Multiple Records
Retrieving Data From a DAO Recordset Using GetRows()
Retrieving Data From an ADO Recordset Using GetRows()

Regards,
Fish

[Breezwell]

FishVal,

Thanks for the reply.

The link you sent referring to ADO is great and I managed to get to the point of generating the output in the Immediate window with the following code:

Private Sub Command2_Click()

Dim rstNames As ADODB.Recordset
Dim varCodes As Variant

Set rstNames = New ADODB.Recordset
With rstNames
.Source = "[names]"
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With

varCodes = rstNames.GetRows(rstNames.RecordCount, , "ID")
rstNames.MoveFirst

For varrows = 0 To UBound(varCodes, 1)
For varCols = 0 To UBound(varCodes, 2)
Debug.Print varCodes(varrows, varCols) & ";"
Next
Next
End Sub

I get the following output in the Immediate window:

1;
2;
3;
4;

This is exactly what I need. Problem is, I can't figure out how to get this output into a variable that can be displayed in a forms text box. Again, I am thinking arrays are the way to go, but all the examples I have seen simply dump the output to the Immediate Window.

Thanks again for the help so far.

[FishVal]

Array is certainly way to go.
And you have already one - varCodes.
Now you could use Join() function to merge the array elements with delimiter.

Kind regards,
Fish

[Breezwell]

Well, I tried some simple array-to-string conversions with the join() method and it seemed to work just fine. So, I tried the following in my code and recieved Runtime Error 3219: Operation is not allowed in this context. The error is showing up on the line:

varCodes = rstGroupCodes.GetRows(rstGroupCodes.RecordCount, , "GroupCode")

This is bizzare as this is the same code that worked previously.



' Generate list of group codes in semicolon delimited list
' ************************************************** *************
Private Sub btnGetGroupCodes_Click()

Dim varCodes As Variant ' holds array returned by getrows method
Dim varList As Variant ' holds array string generated from join method
Dim rstGroupCodes As ADODB.Recordset ' recordset object variable

Set rstGroupCodes = New ADODB.Recordset

' set recordset properties
With rstGroupCodes
.Source = "qryBucketSearchSelling"
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With

' generate array of group codes from recordset with getrows()
varCodes = rstGroupCodes.GetRows(rstGroupCodes.RecordCount, , "GroupCode")


' create semicolon delimited list of group codes from array varCodes
varList = Join(varCodes, " ; ")

' clean up connection
rstGroupCodes.Close
Set rstGroupCodes = Nothing

End Sub


Now, maybe I am trying to be a little too simplistic with my code, but it would seem that if Join() worked on a simple array, it should work on the varCodes arrray?

Thanks

[FishVal]

Well, I tried some simple array-to-string conversions with the join() method and it seemed to work just fine. So, I tried the following in my code and recieved Runtime Error 3219: Operation is not allowed in this context. The error is showing up on the line:

varCodes = rstGroupCodes.GetRows(rstGroupCodes.RecordCount, , "GroupCode")

I guess RecordCount may be not available at this point - recordset opening is not a solid synchronous operation. To enforce it to "count records" perform

rstGroupCodes.MoveLast
rstGroupCodes.MoveFirst
 

before using RecordCount property.

Now, maybe I am trying to be a little too simplistic with my code, but it would seem that if Join() worked on a simple array, it should work on the varCodes arrray?

Well...
It doesn't seem to work with 2-dimensional array.
You could

• Copy 2D array column to 1D array.
• Use Recordset.GetString method instead of Recordset.GetRows.

Regards,
Fish

[ADezii]

Well, I tried some simple array-to-string conversions with the join() method and it seemed to work just fine. So, I tried the following in my code and recieved Runtime Error 3219: Operation is not allowed in this context. The error is showing up on the line:

varCodes = rstGroupCodes.GetRows(rstGroupCodes.RecordCount, , "GroupCode")

This is bizzare as this is the same code that worked previously.



' Generate list of group codes in semicolon delimited list
' ************************************************** *************
Private Sub btnGetGroupCodes_Click()

Dim varCodes As Variant ' holds array returned by getrows method
Dim varList As Variant ' holds array string generated from join method
Dim rstGroupCodes As ADODB.Recordset ' recordset object variable

Set rstGroupCodes = New ADODB.Recordset

' set recordset properties
With rstGroupCodes
.Source = "qryBucketSearchSelling"
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With

' generate array of group codes from recordset with getrows()
varCodes = rstGroupCodes.GetRows(rstGroupCodes.RecordCount, , "GroupCode")


' create semicolon delimited list of group codes from array varCodes
varList = Join(varCodes, " ; ")

' clean up connection
rstGroupCodes.Close
Set rstGroupCodes = Nothing

End Sub


Now, maybe I am trying to be a little too simplistic with my code, but it would seem that if Join() worked on a simple array, it should work on the varCodes arrray?

Thanks

Now, maybe I am trying to be a little too simplistic with my code, but it would seem that if Join() worked on a simple array, it should work on the varCodes arrray?

FishVal gave you the correct answer, it only works with Single Dimension Arrays.

[Breezwell]

Okay, thanks guys for the feedback.

I will keep trying with some other array ideas. If I can find the time, and get this to work, I will post my success.

Thanks again.