Semicolon Generated List  | Member | | Join Date: Sep 2008
Posts: 33
| | |
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.
|  | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: Semicolon Generated List
| | Member | | Join Date: Sep 2008
Posts: 33
| | | re: Semicolon Generated List
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.
| | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: Semicolon Generated List
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
| | Member | | Join Date: Sep 2008
Posts: 33
| | | re: Semicolon Generated List
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
| | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: Semicolon Generated List
Quote:
Originally Posted by 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") 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. Quote:
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
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: Semicolon Generated List
Quote:
Originally Posted by 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 Quote:
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.
| | Member | | Join Date: Sep 2008
Posts: 33
| | | re: Semicolon Generated List
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.
|  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,471 network members.
|
