Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2014

    Unanswered: sql query arrays

    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.


  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    Quote Originally Posted by drewdrew View Post
    What I want to achieve is for a NEXT/PREVIOUS button to function with those same array values
    Which NEXT/PREVIOUS button? Do you want to create them and have some code associated to their Click event, or do they already exist?
    Have a nice day!

  3. #3
    Join Date
    Apr 2014
    Provided Answers: 23
    May I say that you are using the database incorrectly. You are looping thru an array using code and DAO objects and this must be very slow.

    Microsoft Access is it's own database object and all of this can be accomplished with a single query without any code. (no DAO, no looping code)
    Access is built to operate via queries.
    Join the selected items in the list and append to the new table.

    But if this is working for you...go for it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts