Store array values into a text box

[Sac]

Hi All,

I am trying to store an array values into a textbox. I tryed several ways but I was not successfull.
Below is my code:
Any help will be much appreciated.

Private Sub Form_Load()
Dim strOpenArgs() As String

If Not IsNull(Me.OpenArgs) Then
strOpenArgs = Split(Me.OpenArgs, ";")
Me.txtProjectID = strOpenArgs(0)
Else
Me.txtProjectID = "txtProjectID"
End If

Dim db1 As DAO.Database
Dim rst1 As DAO.Recordset
Dim strSQL1 As String

Set db1 = CurrentDb()
strSQL1 = "SELECT DISTINCT CODE, GROUP, INSPECTIONID, PROJECTID, DESCRIPTION, PERCENTAGE, DISTANCE FROM InspectionObsSummary1 WHERE PROJECTID = " & Me.txtProjectID.Value
Set rst1 = db1.OpenRecordset(strSQL1)
'Debug.Print strSQL1
Dim structDefects(10) As Variant
Dim operDefects(10) As Variant
Dim x As Integer
Dim y As Integer
Dim y1 As Integer
Dim x1 As Integer
Dim z As Integer
x = 0
y = 0
y1 = 0
x1 = 0

InspID = rst1!INSPECTIONID.Value



Do While Not rst1.EOF
Select Case rst1!Group.Value

Case 2
operDefects = rst1!Description.Value & ", "
y = y + 1

' txtOpeDefects = rst1!Group.Value
Case 3

structDefects(x) = rst1!Description.Value & ", "
x = x + 1

End Select
rst1.MoveNext

Loop
rst1.Close
'For Each element In operDefects
'fruitnames = fruitnames & Item & Chr(10)
' Next
'txtOpeDefects = fruitnames

For y1 = 0 To 6
' Me.txtOpeDefects.Value = operDefects(y1)
Me.txtOpeDefects.Value = operDefects(y1)
Next y1
For x1 = 0 To 6
' Me.txtOpeDefects.Value = operDefects(y1)
Me.txtStructDefects.Value = structDefects(x1)
Next x1

End Sub

 

[theDBguy]

Hi. Check out the Join() function.

 

[Isaac]

Please use code tags, after also properly indenting code....so that it is readable.

Private Sub Form_Load()
Dim strOpenArgs() As String

If Not IsNull(Me.OpenArgs) Then
    strOpenArgs = Split(Me.OpenArgs, ";")
    Me.txtProjectID = strOpenArgs(0)
Else
    Me.txtProjectID = "txtProjectID"
End If

Dim db1 As DAO.Database
Dim rst1 As DAO.Recordset
Dim strSQL1 As String

Set db1 = CurrentDb()
strSQL1 = "SELECT DISTINCT CODE, GROUP, INSPECTIONID, PROJECTID, DESCRIPTION, PERCENTAGE, DISTANCE FROM InspectionObsSummary1 WHERE PROJECTID = " & Me.txtProjectID.Value
Set rst1 = db1.OpenRecordset(strSQL1)
'Debug.Print strSQL1
Dim structDefects(10) As Variant
Dim operDefects(10) As Variant
Dim x As Integer
Dim y As Integer
Dim y1 As Integer
Dim x1 As Integer
Dim z As Integer
x = 0
y = 0
y1 = 0
x1 = 0

InspID = rst1!INSPECTIONID.Value

Do While Not rst1.EOF
    Select Case rst1!Group.Value
   
    Case 2
    operDefects(y) = rst1!Description.Value & ", "
    y = y + 1
   
    ' txtOpeDefects = rst1!Group.Value
    Case 3
   
    structDefects(x) = rst1!Description.Value & ", "
    x = x + 1
   
    End Select
    rst1.MoveNext

Loop
rst1.Close
'For Each element In operDefects
'fruitnames = fruitnames & Item & Chr(10)
' Next
'txtOpeDefects = fruitnames

For y1 = 0 To 6
    ' Me.txtOpeDefects.Value = operDefects(y1)
    Me.txtOpeDefects.Value = operDefects(y1)
Next y1
For x1 = 0 To 6
    ' Me.txtOpeDefects.Value = operDefects(y1)
    Me.txtStructDefects.Value = structDefects(x1)
Next x1

End Sub

 

[Sac]

Hi. Check out the Join() function.

theDBguy Thank you, I will look into it!!

SAC

 

[theDBguy]

theDBguy Thank you, I will look into it!!

SAC

Please let us know how it goes. Good luck!

 

[Sac]

Hi All,

I used the concatRelated() function to store all field values that contained the same key (INSPECTION) in the table called structuralDefects. Eg.
SELECT DISTINCT structuralDefects.INSPECTION, ConcatRelated("DESCRIPTION","structuralDefects","structuralDefects.INSPECTION = " & [INSPECTION]) AS STRUCTDEFECTS
FROM structuralDefects
GROUP BY structuralDefects.INSPECTION;

Then I added the new field in the DB query that the form reads the values from!! Thus, I did not use VBA code at all.
Thank you for the hint!!

 

[theDBguy]

Hi All,

I used the concatRelated() function to store all field values that contained the same key (INSPECTION) in the table called structuralDefects. Eg.
SELECT DISTINCT structuralDefects.INSPECTION, ConcatRelated("DESCRIPTION","structuralDefects","structuralDefects.INSPECTION = " & [INSPECTION]) AS STRUCTDEFECTS
FROM structuralDefects
GROUP BY structuralDefects.INSPECTION;

Then I added the new field in the DB query that the form reads the values from!! Thus, I did not use VBA code at all.
Thank you for the hint!!

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[Sac]

Thanks theDBug!!