Question Reading multiline column values into an array

[sylscic]

Hi,
I would really appreciate if someone can help me here. I am working on setting up a Document Control System and have a table, called List_of_SOP where I have, amongst others, the following fields:
- Document Title
- Document Number (unique identifier)
- Responsible
- DL (where this is a multiline lookup column where one ticks the names of the people to whom this document needs to be distributed)

I want to be able to fill an array with the checked values of the DL field, given a particular Document Number. Can someone help me please on how to go around this? So far, the code is somewhat on these lines:

__________________________________________________________________

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim db As DAO.Database
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String

Dim docTitleItems(50) As String
Dim respPerson(50) As String
Dim distList(50) As String
Dim dlitems(50) As String
Set db = CurrentDb

For intI = LBound(strSOPArr) To UBound(strSOPArr)
strSQL1 = "SELECT [Document Title] FROM
[List_of_SOP] WHERE [Document Number] = " & intSOPArr(intI)
strSQL2 = "SELECT [Responsible] FROM
[List_of_SOP] WHERE [Document Number] = " & intSOPArr(intI)
strSQL3 = "SELECT [DL] FROM
[List_of_SOP] WHERE [Document Number] = " & intSOPArr(intI)
Set rs1 = db.OpenRecordset(strSQL1)
Set rs2 = db.OpenRecordset(strSQL2)
Set rs3 = db.OpenRecordset(strSQL3)

docTitleItems(intI) = rs1.Fields("Document Title")
respPerson(intI) = rs2.Fields("Responsible")
'distList(intI) = rs3.Fields("DL")

rs1.MoveNext
rs2.MoveNext
rs3.MoveNext

Next
__________________________________________________________________
Thanks a lot

 

[apr pillai]

check the modified code:

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim db As DAO.Database
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String

Dim docTitleItems() As String
Dim respPerson() As String
Dim distList() As String
Dim dlitems() As String
Dim j As Integer

Set db = CurrentDb

strSQL1 = "SELECT [Document Title] FROM [List_of_SOP] WHERE [Document Number] = " & intSOPArr(intI)
strSQL2 = "SELECT [Responsible] FROM [List_of_SOP] WHERE [Document Number] = " & intSOPArr(intI)
strSQL3 = "SELECT [DL] FROM [List_of_SOP] WHERE [Document Number] = " & intSOPArr(intI)
Set rs1 = db.OpenRecordset(strSQL1)
Set rs2 = db.OpenRecordset(strSQL2)
Set rs3 = db.OpenRecordset(strSQL3)

'scan for the number of checked items
j = 0
Do While Not rs3.EOF
   If rs3.Fields("DL") = True Then
      j = j + 1
   End If
Loop

'Redimension the Array for the correct number of checked items
ReDim docTitleItems(j) As String
ReDim respPerson(j) As String
ReDim distList(j) As String
ReDim dlitems(j) As String

'reset the record pointer to the first record
rs3.MoveFirst
'Scan and load the items into array.
j = 0
Do While Not rs3.EOF
   'test DL field's Check status
   If rs3.Fields("DL") = True Then 'if checked item
       j = j + 1
       docTitleItems(j) = rs1.Fields("Document Title")
       respPerson(j) = rs2.Fields("Responsible")
       distList(j) = rs3.Fields("DL")

       rs1.MoveNext
       rs2.MoveNext
       rs3.MoveNext
   End If
Loop
rs1.Close
rs2.Close
rs3.Close

Set rs1 = Nothing
Set rs2 = Nothing
Set rs3 = Nothing

 

[Pat Hartman]

There is no reason to run three queries. Use one.

strSQL1 = "SELECT [Document Title], [Responsible], [DL] FROM [List_of_SOP] WHERE [Document Number] = " & intSOPArr(intI)

What is the purpose of filling an array? The recordset is itself an array and can be used to control a loop.

 

[sylscic]

Hi,

Thanks a lot for your help. However, when running the database, it is giving me a runtime error in the line marked in red.

Do While Not rs3.EOF
If rs3.Fields("DL") = True Then
j = j + 1
End If
Loop

Could you indicate what could be the problem? It was giving me the same error before I made the changes kindly suggested by your goodself.

Thanks,

 

[Pat Hartman]

If you changed the query, you now only need a single recordset.

 

[sylscic]

can you explain how please?

 

[Pat Hartman]

Dim rs1 As DAO.Recordset
Dim db As DAO.Database
Dim strSQL1 As String

Dim docTitleItems(50) As String
Dim respPerson(50) As String
Dim distList(50) As String
Dim dlitems(50) As String
Set db = CurrentDb

For intI = LBound(strSOPArr) To UBound(strSOPArr)
    strSQL1 = "SELECT [Document Title], [Responsible], [DL] FROM [List_of_SOP] WHERE [Document Number] = " & intSOPArr(intI)
    Set rs1 = db.OpenRecordset(strSQL1)

    docTitleItems(intI) = rs1.Fields("Document Title")
    respPerson(intI) = rs1.Fields("Responsible")
    distList(intI) = rs1.Fields("DL")

    rs1.MoveNext

Next

 

[Pat Hartman]

I should mention that this is extremely inefficient. Recordsets are arrays themselves so loading something into an array is unnecessary. You just process the recordset directly. Also, you are running a separate query for every record in the array. That could be really slow depending on how many elements are in the array. You should probably rethink the entire process and use a query that joins the two sets of data to do whatever you are trying to do.

 

[sylscic]

Thanks to all for your help.
I have managed to access the data in the multivalue lookup column, however, not through the SQL query I desire. Infact, the Print output is for all checked items for all records in my table. Can someone help me how to refer only to the particular record I need (intSOPArr(intI))? My code is now as follows:

Dim recordNumber As Integer
recordNumber = intSOPArr(intI)

'To link to Form_List_of_SOP_form
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim db As DAO.Database
Dim strSQL1 As String

Dim docTitleItems(50) As String
Dim respPerson(50) As String
Dim distList(50) As String
Dim dlitems(50, 50) As String
Dim j As Integer

Set db = CurrentDb
j = 0
For intI = LBound(strSOPArr) To UBound(strSOPArr)
strSQL1 = "SELECT [Document Title], [Responsible] FROM
[List_of_SOP] WHERE [Document Number] = " & intSOPArr(intI)
Set rs1 = db.OpenRecordset(strSQL1)
Set rs2 = db.OpenRecordset("List_of_SOP")
docTitleItems(intI) = rs1.Fields("Document Title")
respPerson(intI) = rs1.Fields("Responsible")
MsgBox (docTitleItems(intI))
MsgBox (respPerson(intI))
rs2.MoveFirst

Do Until rs2.EOF
Set rs3 = rs2.Fields("DL").Value
Do Until rs3.EOF
rs3.MoveFirst
Do Until rs3.EOF
MsgBox (rs3!Value.Value)
rs3.MoveNext
Loop
Loop
rs2.MoveNext
Loop
rs1.MoveNext

 

[Pat Hartman]

You probably should have mentioned up front that you were using a multi-value field. The solution you have is also less than optimum since you are reading one recordset sequentially and the other randomly. Using a query that sorts the two recordsets so you can read them sequentially in tandem would be better. But, as long as your recordset isn't large, this method will work.

The best solution is to get rid of the multi-value fields. They are an abomination and cause nothing but trouble. Create your own many-side table and use a subform to display the multiple records. Using standard practices will work much better for you.