Questionaire

[LPurvis]

Shingles huh? Yoinks.
That sounds about as fun as my workload.

Well I'm past my 3 posts per day I'm allowing myself - but I had to intercept you on this.
You definitely don't want to be making hard copied copies of the function.

When the next button is clicked you'd call the function directly passing the page.
Just as I mentioned earlier:

Me.SubformName.Form.RecordSource = "SELECT * FROM tblQs WHERE QNo In (" & fGetFromArr(intPage) & ")"

Cheers.

 

[NigelShaw]

Hi Leigh

Yeah, i thought shingles was something you got in the 1800's along with the plague!! how wrong was i?

i feel privileged that you exceeded the posting limit

so,

the code you provided would call the array variables 1 to 4 based on page 1 and call 5 to 8 based on page 2 and so forth?

that would work just fine then

i'll wait until tomorrow for the next exciting episode


cheers mate

Nigel

 

[LPurvis]

Conceptually, at a bare minimum...

Dim mintPage As Integer 'Module level variable
 
Private Sub Form_Load()
    RandomQuestions
    mintPage = 1
    AssignSource
End If
 
Private Sub cmdNext_Click()
    If mintPage < 8 Then
        mintPage = mintPage + 1
    End If
    AssignSource
End Sub
 
Private Sub cmdPrev_Click()
    If mintPage > 1 Then
        mintPage = mintPage - 1
    End If
    AssignSource
End Sub
 
Private Sub AssignSource()
    Me.SubformName.Form.RecordSource = "SELECT * FROM tblQs " & _
            "WHERE QNo In (" & fGetFromArr(mintPage) & ")"
End Sub

Cheers

 

[NigelShaw]

Hi

thanks for all of your help, it's really appreciated. I'm venturing into this tonight and my brother did tell me if a change that I have to look into. The questions would be filtered into 3 groups.

1. All questions
2. Electrical only questions
3. Mechanical only questions

this means the random number storage in the array will only work on question set one as this will have all numbers from 1 to 700 but the other sets will fail as they have a less number set. So I need to create an array based on numbers in a recordset created in a query that filter the questions. This would ensure the random numbers are selected based only within the numberset of the returned query. After the array collection, the process is exactly the same so it is just the gathering of the random numbers that I need to look into.

I'll post back my results later fingers crossed


Thanks


Nigel

 

[NigelShaw]

Hi Leigh

sorry to try and pick your brains again. I have placed in your code and have a query. Your code sets the form recordsource as the SQL string but how do u get the form objects to show the results. I assume I have the form unbound thus text boxes are also unbound or am I wrong? Do I set the form source to the table and change it when it loads?

I'm a little unsure here

many thanks mate

nigel

 

[LPurvis]

Hi Nigel

(You've caught me with very limited time today... might have more tomorrow and then onwards...)

I'm not actually sure what you mean.
The form's source is set. What form objects showing results are you talking about?
Are the questionnaire records selected not displaying?
Do you have that form set up, with the controls required to bind to the results?

Cheers.

 

[NigelShaw]

Conceptually, at a bare minimum...

Dim mintPage As Integer 'Module level variable
 
Private Sub Form_Load()
    RandomQuestions
    mintPage = 1
    AssignSource
End If
 
Private Sub cmdNext_Click()
    If mintPage < 8 Then
        mintPage = mintPage + 1
    End If
    AssignSource
End Sub
 
Private Sub cmdPrev_Click()
    If mintPage > 1 Then
        mintPage = mintPage - 1
    End If
    AssignSource
End Sub
 
Private Sub AssignSource()
    Me.SubformName.Form.RecordSource = "SELECT * FROM tblQs " & _
            "WHERE QNo In (" & fGetFromArr(mintPage) & ")"
End Sub

Cheers

Hi Leigh

the code you supplied here sets the form recordsource when it loads to the SQL string in AssignSource.

Do I set the forms recordsource to the table anyway and let this code reset it it? Currently my form is unbound so the textboxes in the form are not bound to anything either so I can't show the questions based on the query.

In order to see the questions on the form, the textboxes have to receive the data.

That's where I'm stuck

cheers

Nidge

 

[LPurvis]

Oh right. Yes, bind the form to the table - set up the controls to be bound to the appropriate fields. Remove the form's source again and let the code assign it from then on.

 

[NigelShaw]

Conceptually, at a bare minimum...

Dim mintPage As Integer 'Module level variable
 
Private Sub Form_Load()
    RandomQuestions
    mintPage = 1
    AssignSource
End If
 
Private Sub cmdNext_Click()
    If mintPage < 8 Then
        mintPage = mintPage + 1
    End If
    AssignSource
End Sub
 
Private Sub cmdPrev_Click()
    If mintPage > 1 Then
        mintPage = mintPage - 1
    End If
    AssignSource
End Sub
 
Private Sub AssignSource()
    Me.SubformName.Form.RecordSource = "SELECT * FROM tblQs " & _
            "WHERE QNo In (" & fGetFromArr(mintPage) & ")"
End Sub

Cheers

Hi Leigh

the code you supplied here sets the form recordsource when it loads to the SQL string in AssignSource.

Do I set the forms recordsource to the table anyway and let this code reset it it? Currently my form is unbound so the textboxes in the form are not bound to anything either so I can't show the questions based on the query.

In order to see the questions on the form, the textboxes have to receive the data.

That's where I'm stuck

cheers

Nidge

 

[LPurvis]

Haven't I already replied to that one?

 

[NigelShaw]

Sorry mate

my gimmicky iPhone is being a gimp and returned the page unsent!!

I'll get it removed

Nidge

 

[NigelShaw]

Hi Leigh

got it all working so thanks for your help. I changed it a little to show only 1 record instead of 4.

The only main thing I need to change now is the collection of numbers in the array. If I have to filter the questions, I need to randomize the available question numbers in the filter only.

Regs

Nidge

 

[LPurvis]

Glad you're up and running.
Not sure I follow the latest requirement though. (Well, in fact I am sure that I don't follow it ;-)

 

[NigelShaw]

Hi Leigh

what I mean is ( and I'll try and explain it right )

currently I am creating a random array using number 1 to the recordcount so if I had 700 records, I get 30 random numbers between 1 and 700. But as I need to filter down fir a different selection, some numbers in the array might not be the same as the filtered Qno's so, I need to create my random array by looping through the filtered numbers instead. For example-

general filter 1 to 700 records
electrical filter 1 to 200 records all at random positions in the table

with the electrical filter, I have a query with 200 records so I'd need to select random records from this query and save the results to the array so to guarantee the displayed question is related to the filter. Does that make any sense?


Nigel

 

[LPurvis]

I'm still not exactly clear on the position no.
I don't see what the problem is dropping to 200 instead of 700.

You persist the chosen questions to the array already. Whether there was originally 700 or 200 no?

 

[NigelShaw]

Hi Leigh

the problem is this

general filter is all of the questions from 1 to 700 so I will always get a result however when it's filtered to electrical where there are only 200 questions, I have 500 possibilities of a random number being selected that is NOT in the electrical filter so I don't get a result. The electrical questions are placed in the full set of questions so almost guarenteed that I won't get a full set of 30 questions. This is because all 700 questions are numbered 1 to 700 numerically but filtered to electrical, they could be
34,88,476,500,502,600,623 etc etc thus not in an order I can get random numbers to set correctly

my thought then is to collect the 200 questions in the query and randomly select 30 question numbers only available in the query so then I can guarantee that I would have a full set of questions and no random number is selected outside the electrical query

thanks

nigel

 

[LPurvis]

So when you're after 200 questions - they're numbered 1-200?
They're not some random 200 amongst the 700? :-s

Doesn't this just come down to the RandomQuestions procedure.
Instead of having the 700 as a fixed amplitude of the random value you can pass the value you want.

Public Function RandomQuestions(Optional pintMax As Integer = 700)
    Dim i As Integer
    Dim j As Integer
    Dim IntRes As Integer

    For i = 1 To 30
        IntArr(i) = Int((pintMax * Rnd) + 1)
    Next i
    For j = 1 To 30
        'loop through the IntArr(i) here
        IntRes = IntArr(j)
    Next j
    If IntRes = IntArr(i) Then
        MsgBox "duplicate"

    End If


End Function

Cheers.

 

[NigelShaw]

Hi Leigh

other way round. They are 200 questions randomly numbered between 1 and 700. I then need to select 30 random question out of the 200

cheers

nigel

 

[LPurvis]

Yuk.
(This numbering system has been a concern since the start - especially now given this).
You can't relying on it in this instance.

You either have to constantly check the 200 questions each time you select a random value to see if it's present, or use a random generator on each row to put the 200 rows into a random order and then just select the Top 30 of those.

 

[NigelShaw]

Hi Leigh

that could work well. I could rnd the query and select the top 30, iterate through the 30 to save the numbers into the array using a loop and use the same process fir the other queries.

I'll check it out later tonight


Many thanks

nigel