sql query arrays

drewdrewdrew

Registered User.
Local time
Today, 05:54
Joined
Jul 3, 2014
Messages
35
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
 
Welcome to the forum (as a poster) :)

If you want to know the position a value is in the array you could do one of two things:

1. Loop through the array and find the position of that item, save the index in a variable and use this as your start position.

OR

2. Use a Dictionary instead of an array:
http://support.microsoft.com/kb/187234
 
thanks for the reply,

the above code I have made is just on the limits of what I know in access.
When you mentioned:

"Loop through the array and find the position of that item, save the index in a variable and use this as your start position"

This is getting a bit above what I know what to do. :-(

With the loop:

Where would I put it?
How to I get the index to save as a variable?
What code would I put against a command button to allow next/previous?

Thanks once again.
 
Did you write the code you posted?

For your first two question, you need a variable that is initialised to 0 and increments by 1 each time you loop through the array. For each cycle check if the value you're looking for matches with the value in the current array index. If it does, save it in a variable and get out of the loop.
 
hi again,

yes I did... I have only started working with arrays.

thanks for your responses. I am just trying to find an easy answer to my questions... lol

I understand loops, but because of just moving into arrays I am having a hard time to find a solution to use both :banghead:

I will try getting the loop increment working. Should this loop through the question_ids or create another looping counter?

eg

dim loop as integer
loop = 0
"array loop in current code"
loop = loop + 1
"array loop end"

if this is the case how to I attach that loop number against the current loop record from the array?

sorry it may sound noobish, but I am moving into a big area of learning atm... and yes I did write the code with lots of research.

drew
 
So does the number of items in the array match the number of records on your form?
And where's the array coming from?

Just want to understand the whole idea behind this.
 
the array comes from a form that passes the full array in the format [number,number,number,number, etc]

then from that array the array is broken up with:

Dim strArrayValue As Variant
Dim strArray() As String
strArrayValue = Me.q_array_list.Value
strArray = Split(strArrayValue, ",")

then each array item is then matched against the recordset. That value is matched against a question_id in the recordset.

With the returned question_id the form displays the record I want.

This whole process works. I just cant get the next/previous buttons working against the array for I cant identify what the current array item is and how to cycle to the next/previous.

I can do it with +1 cycling, but that just loops the recordset +1, not the array.

thanks
 
Don't worry I understand all that. So why are you using a recordset?
 
The recordset extracts the information from the q_question_list table based on question_id from the array.

Basically the form is a questionnaire.

Does this help?
 
Ok. I'm just trying to see what the easiest way for you is, hence, the questioning ;)

So why don't you use the query as the Row Source of your listbox?
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'm also struggling to understand the above. If you selected 6 why should it move to 18? I thought the record would move to 6?
 
I have just done the query this way so I have full control of it in vba. I thought that would be the better way.

IRT "I'm also struggling to understand the above. If you selected 6 why should it move to 18? I thought the record would move to 6? "

This example was to show each question_id in the array. Each item in the array could match a different value.. So if the array passed is [2,3,6,18,45] the "next" button would navigate to question_id=18 and the "prev" button would be 3 if I was on question_id=6

does this explain my messed up mind?
 
Got it. So let's trace a bit further back. Why are the numbers passed as a string? Is this how it's setup in the field?
 
well that is just the way it was passed. The array is from a previous form that gets array of numbers from another table.

so further back there is a Test Card. Each TC has an array in the table. This array is the question_id number. So then it gets passed to my OP.
 
If these numbers are singletons in the first, you didn't need to form a string from them. We can come back to this later.

I think your main objective here is to sync your listbox with the main form? Sounds about right?
 
The array of numbers is merely to group them to each Testcard.

The listbox is functioning correctly. Each item in the listbox is working from the array list, and pulling the correct config_id from the table.

So the form gets the correct question_id, the listbox gets the correct information, and when I select an item from the listbox and execute a button associated with the listbox it works.

NEXT/PREV button is my goal.

Sorry it seems we are going in circles.
 
You are going about it the wrong way that's why I need to understand what aspects that need looking into. If you had told me that you want to sync your listbox with the form when a next/previous button is pressed I would have explained how to go about it.

To move between records, you use the DoCmd.GoToRecord method. Look that up. However, that's just a minor detail.

What should happen when you select an item in your listbox? Nothing or should it also move to the corresponding record selected?
 
Hi again.

The listbox is just a standalone element that I put in place because I could not get the next/prev working. The goal is to use the listbox as a secondary navigation, where a user can select a question directly. Like I mentioned before the listbox as it running works and I have a command button associated with it.

Based on that would I need to run another procedure/function to get the next/prev values for the form?

I have tried the docmd.gotorecord, but I can only get it to work literally, as in select the next record in the table, not the next record in the array list.

So to summarise. The listbox is fine. I need the array list working "with" the currently selected record and populate the next and prev buttons accordingly.

Can this be achieved do u reckon?

Thanks once again for ur brain space on the matter
 
Easily done! To recap, the listbox is independent of the form (i.e. the form display all the records from its Record Source) but the listbox, when an item is selected, should make the current record of the form match up with the item selected?

In this case you don't need GoToRecord. You need to be finding the record, not moving back and forth. And since you understand a thing or two about Recordsets, let's work with that:
Code:
    Dim rst As DAO.Recordset

    ' Get a copy of the form's record source
    Set rst = Me.RecordsetClone
        
    With rst
        ' Find the record
        .FindFirst "[[COLOR="Blue"]FieldName[/COLOR]] = " & Me.[COLOR="blue"]lstBoxName[/COLOR]
        
        ' If there's a match set the bookmark of the form to that of the one we've just searched for
        If .NoMatch = False Then
            Me.Bookmark = .Bookmark
        End If
    End With
    
    ' Clean up
    Set rst = Nothing
Changes in blue and you'll need to add a reference to the DAO library (if the code doesn't compile).

Put this code in the listbox's After Update event.

Get that working and I'll tell you what to do with your navigation buttons.
 
the field name is [question_id] and is an integer... getting runtime error 3464 - data type mismatch. The new line reads

.FindFirst "[question_id] = " & Me.question_list

on hover over the question_id is being passed... eg "332" is being visible in the VBA debug window.
 
That's because your array (strArray) is of type String. Change that to Integer or Long.
 

Users who are viewing this thread

Back
Top Bottom