sql query arrays

[vbaInet]

Filtering a form whilst it's opening can be done in the OpenForm command itself. You don't need to pass values all over the shop. To give you flavour of how it's done:

DoCmd.OpenForm "[COLOR="blue"]FormName[/COLOR]"[COLOR="Red"], , ,[/COLOR] "[[COLOR="blue"]Field1[/COLOR]] = " & Me.txtBox1 & " AND [[COLOR="blue"]Field2[/COLOR]] LIKE *" & Me.txtBox2 & "* AND [[COLOR="Blue"][COLOR="blue"]Field3[/COLOR][/COLOR]] = [COLOR="red"]'[/COLOR]" & Me.txtBox3 & "[COLOR="Red"]'[/COLOR]"

 

[drewdrewdrew]

in order to accommodate for my mess of a form, applying just the filter to it wont work in the first instance (however not discarding). In how the form works I pass the question_id from the question_list (array combobox) to a temp form which merely opens/closes .... passing those integer back to the original form to load that question........ kinda like going on the outside of a sports field instead of crossing is from side to side.. lol

can this work on a command button ?

 Sub CommandButton_OnClick()
 Dim rst As DAO.Recordset
Dim strQuestionID As Integer
Set rst = Me.RecordsetClone
 With rst
    ' Find the record
    strQuestionID = Me.question_list + 1
    .FindFirst "[question_id] = " & str1 & ""
   ' Open the temp form with the question_id
   DoCmd.Close
   DoCmd.OpenForm "frm_question_interface_tmp", , , , , , str1 
  
 If .NoMatch = False Then
        Me.Bookmark = .Bookmark
   End If
End With
Set rst = Nothing
 End Sub

Thanks

 

[vbaInet]

in order to accommodate for my mess of a form, applying just the filter to it wont work in the first instance (however not discarding).

Let us do the thinking for you. You tell us what the problem is and I'll tell you whether it's feasible or not. If you find limitations in the suggested approach, give us more context and justify why you think it won't work.

So why do you filtering the form down won't work? Wasn't that what you asked in the post just prior to my suggestion?

And what's the reasoning behind opening and closing a temp form? What does the temp form contain?

 

[drewdrewdrew]

more than putting more bandaids... I am rebuilding the dataflow to adapt all that we have discussed... and a few things I have learnt since starting the project.

I will fire them questions soon.

 

[vbaInet]

Ok, so remember to first tell us what you're trying to achieve, followed by your ideas. It's important we know the objective as the first instance.

Will wait for your questions.

 

[drewdrewdrew]

So finally got a chance to get back into it... I have the code below working on the next button.

 Private Sub btnNextQuestion_Click()
 Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
 Dim str6 As Integer ' Question ID from cboQuestionList
Dim str7 As Integer ' Test Card ID
Dim str8 As String ' Question array
Dim str9 As String ' Testcard Name
Dim str10 As String ' MOE Name
 str6 = Me.cboQuestionList + 1
str7 = Me.txtTestcardID.Value
str8 = Me.txtQarray.Value
str9 = Me.txtTestcardName.Value
str10 = Me.txtQuestionMOE.Value
  With rst
    .FindFirst "[question_id] = " & str6 & ""
   ' Open the temp form with the question_id
   DoCmd.Close
   DoCmd.OpenForm "frm_question_interface_tmp", , , , , , str6 & "|" & str7 & "|" & str8 & "|" & str9 & "|" & str10
  
 If .NoMatch = False Then
       'Me.Bookmark = .Bookmark
   End If
End With
Set rst = Nothing
 End Sub

So the next button skips to the next item in the array... however when the last questionid in the array is selected the next button just goes to the next record in the table.... I need when the last question in the selected that the nextbutton is disabled.

 

[vbaInet]

It's hard to keep up drew because we deal with so many people on a daily basis. I may sometimes forget about the advice I've given if it's been a long time I addressed an issue, and I'm forced to skim through the thread again to be up-to-date.

What's with all the strX variables anyway? You should be skipping through the combo box (or listbox as I can't remember which) and through that you'll know when you're at the bottom of the list.

 

[drewdrewdrew]

hey mate.... all good, I understand you offer plenty of advice across many topics.

the combobox - Me.cboQuestionList is populated from an array list, passed from a previous form. I have this working fine.

with this form, if you remember I have a gumpy way of passing the Me.cboQuestionList result. Instead of a filter on that form I pass the Me.cboQuestionList.value to another form that opens up, accepts the variables that are str6-10 and then reopen the original form on the selected question.

This works once again.

SO with the next button code I pasted, that is what it is doing.

SO when you press the nextquestion_click() button it cycles through the array eg...

2,3,5,78,67....

when it gets to 67 for example it goes to 68, which is just the next record in the table.... not the next array value.

does this explain my web?

 

[vbaInet]

We try to keep up

I do remember and I also remember mentioning that you shouldn't cycle through the array. I said you should cycle through the combo box. What ever is in the array is already in the combo box so there's no point in continuously re-building the array and cycling through it.

I'll just make an example and send.

 

[vbaInet]

See attached!