Store array values into a text box (1 Viewer)

Sac

Member
Local time
Today, 01:50
Joined
Feb 28, 2020
Messages
30
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(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
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:50
Joined
Oct 29, 2018
Messages
13,163
Hi. Check out the Join() function.
 

Isaac

Lifelong Learner
Local time
Yesterday, 22:50
Joined
Mar 14, 2017
Messages
2,693
Please use code tags, after also properly indenting code....so that it is readable.
Code:
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

Member
Local time
Today, 01:50
Joined
Feb 28, 2020
Messages
30
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

I’m here to help
Staff member
Local time
Yesterday, 22:50
Joined
Oct 29, 2018
Messages
13,163
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.
 

Users who are viewing this thread

Top Bottom