sql query arrays

[drewdrew]

Hi all,

Long time follower, first time poster.

I have a drama finding information that will help me with arrays and looping.

I have a sql query based on an array of question_ids from a previous form. So basically I will pass the array eg 3,4,5,6,7 etc to a SQL query. This query will return the information from a table based on the array question_id value, as follows.

'-------------------------------------------------------
' Load the questions values based on Testcard ID
'-------------------------------------------------------
Dim db4 As DAO.Database
Set db4 = CurrentDb
Dim rs4 As DAO.Recordset
Dim i, j, k As Integer
Dim strArrayValue As Variant
Dim strArray() As String
strArrayValue = Me.q_array_list.Value


strArray = Split(strArrayValue, ",")

For j = 1 To Me.question_list.ListCount
Me.question_list.RemoveItem 0
Next j

For i = LBound(strArray) To UBound(strArray)
Set rs4 = db4.OpenRecordset("SELECT q_question_list.question_id, q_question_list.config_id FROM q_question_list WHERE (((q_question_list.question_id) In ('" & strArray(i) & "')));")

With Me.question_list
.AddItem Item:=rs4!question_ID & ";" & rs4!config_ID
End With
Next i
rs4.Close 'Close the recordset
Set rs4 = Nothing 'Clean up
End If
' Select first record to avoid NULL selection
Me.question_list = Me.question_list.ItemData(0)

Now that works fine for the Me.question_list.Additem .. It loads all the questions in a combo box line by line.

What I want to achieve is for a NEXT/PREVIOUS button to function with those same array values.

So if my array is 4,5,6,18 and I selected question 6, I want on that form the NEXT button to goto the next question in the array being 18.

I have trawled and trawled the forums, but I cannot get my head around it..... everything like I said works fine if there is a looped additem.... but for the life of me I cannot get the next button to be the next array value.

any help would be greatly appreciated.

Drew

[atuhacek]

You could use the doCmd.goToRecord to navigate.

http://codevba.com/msaccess/docmd_go...m#.U7K4zqMpDFo

[hapm]

First of all I would suggest you to use the record source property of the list to bind your query to the list view. Will not help you in your current problem but IMHO it's cleaner.
Now to your problem: use ItemsSelected property to get the currently selected item. Use Selected property to set the next or previous item as selected and remove the selection on the current item.

[drewdrew]

hi hapm,

thanks for your reply. I am struggling to adapt your suggestion for the ItemsSelected / Selected property. I have never used them and cannot see anything online that would help me in building this option to my code.

Any chance of showing me what/where to put this code?

Thanks once again.