Questionaire

[NigelShaw]

Hi everyone

I'm on a new small project for my brother helping him do a questionaire for his work

I can see most of the things being straight forward enough except for one. He has to have only 4 questions per page and a total of 30 random questions. Using a continuous form, how would I show only 4 records at a time?

I already have the random selection of the questions and did consider selecting only 4 at a time however that does run the risk of selecting the same question more than once.


Thanks

nigel

 

[LPurvis]

Hi

It would depend entirely upon how you've gone about selecting a random record as to whether you could possibly pull the same one out twice.
It's generally just about querying for the four records you need upon which your form is bound.
If you were using a random generator to select a set of primary key values then you'd need to make sure that the values produced were unique.
If you were creating a calculated column of random values, then selecting the Top 4 of those would be unique and relatively random (about as random as you get anyway).

Cheers.

 

[NigelShaw]

Hi

I have around 700 possible questions so I generate 30 random unique numbers within the question range.

I guess I could take 4 and display on the form leaving 26 remaining numbers then after next is clicked, take another 4 random numbers and so forth.

I set up 30 public variables to hold the random numbers so I clear these as the numbers are used until they're all gone.

I'll report back when I achieve my goal


Thanks

nigel

 

[LPurvis]

If you order the records randomly, select the top 30 and then progress through those (still in that random order) four at a time then that should be all you need.
Of course randomly ordering all 700 rows comes at a price.

If you continue to generate the PK values you're going to choose (in a random fashion) but persist that list for both selection and ordering of those 30 then that list can be used to methodically progress through.
Either way, it's really about persisting the random values that determine selection.

Cheers.

 

[NigelShaw]

Hi Lee

I am currently creating random numbers between 1 and record count ( 700ish ), checking them against the already selected numbers to prevent duplicate numbers and the showing the question related to the selected number. I'm not using PK numbers just in case questions become out of date and the being deleted or excluded so I have gone with a question number in a field.

A query will create the question list which will now be based on the top 4, then re select etc etc


Nigel

 

[LPurvis]

Right, not using PK numbers but there is some uniquely identifying "Question Number" type field which you're using to identify and select.
As long as that list you create is persisted (albeit temporarily until the next lot) then navigating through them, four at a time, should be quite controllabe.

Cheers.

 

[NigelShaw]

Hi

I do have a small coding issue. I am doing a for next to get the random numbers and I need to store the number in a public variable. I have numbered the int1 to int30. Hot can I loop through the variables without actually listing all variables in the code so for example

every random number made is added to int1 then a new one to int2 and so forth. My code for making the random number is

for i = 1 to 30
'my randoming code here
'loop through and add the number here
next i

Regs

nigel

 

[LPurvis]

From the sounds of it (30 distinct variables?) you should be using an array instead.
This would both reduce the use of so many (unecessary) variables and facilitate the iterative assignment of values.

Dim intArr(1 to 30) As Integer 'Persisted at module level or public...
 
Dim i as Integer   
 
For i = 1 to 30
    'my randoming code here
    intArr(i) = fSomeRandomAssign
Next

Cheers.

 

[NigelShaw]

Hi Lee

thanks for your reply.

Can I make the array public and as the separate variables would be removed to make way for the array, can you call the independent int number separately?

For example,
use your code to make the array then call up IntArr22 and get it's stored value?


Cheers mate

Nidge

 

[LPurvis]

You'd call it as
intArr(22)
but yes and yes.

(Array can be public unless it's in a class module, but even then there are ways around that).

Cheers.

 

[NigelShaw]

Hello mate

sorry to sound stupid, some things I just have never used before... So, to make it public I would simply use-

Public IntArr(1 to 30) As Integer

cheers for your help

nigel

 

[LPurvis]

No worries.
Yep, that's the appropriate declaration (assuming this is a standard - not class - module).

It doesn't need to be public if you use a function to retrieve the value.
The advantage of doing that is that the function can be called from a query too if required, whereas the array can't be. (The expression service can only resolve functions, not VBA objects

Cheers.

 

[NigelShaw]

Hi Lee

one more question if i may

the example you provided works great but to try and remove any duplicate numbers, i would need to loop through the IntArr every time ( at least thats what i think ) so every random number generated and saved to IntArr(i) should be checked against any previous numbers already stored.

i thought

Public Function RandomQuestions()
    Dim i As Integer
    Dim j As Integer
    Dim IntRes As Integer

    For i = 1 To 30
        IntArr(i) = Int((700 * 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

but it doesnt work and i know it is something to do with the placement of the next j but i cant seem to get it.


regs

Nigel

 

[LPurvis]

Hi

Yep you're right. You'd need to iterate through the loop within each assignment.
Using the current method the easiest fix is the, often not preferred, label GoTo.
(A separate procedure which checks for a match would probably be neatest but more for you to implement).

Public Function RandomQuestions()
 
    Dim i As Integer
    Dim j As Integer
    Dim IntRes As Integer
    
    For i = 1 To 30
BeginAssign:
        IntRes = Int((700 * Rnd) + 1)
        For j = 1 To i - 1
            If IntRes = intArr(j) Then
                GoTo BeginAssign
            End If
        Next j
        intArr(i) = IntRes
    Next i
    
End Function

The alternative with a dedicated checking function...

Public Function RandomQuestions()
 
    Dim i As Integer
    Dim IntRes As Integer
    
    For i = 1 To 30
        Do
            IntRes = Int((700 * Rnd) + 1)
        Loop Until Not fArrHasMatch(intArr, IntRes)
        intArr(i) = IntRes
    Next i
    
End Function
Private Function fArrHasMatch(pvarArr1() As Integer, pintMatch As Integer) As Boolean
    
    Dim intI As Integer
    
    For intI = LBound(pvarArr1) To UBound(pvarArr1)
        If pvarArr1(intI) = pintMatch Then
            fArrHasMatch = True
            Exit For
        End If
    Next
    
End Function

You could also use a collection or even recordset. They're all memory structures.

Cheers.

 

[LPurvis]

By the way, you'd want a method to empty the array's values too.
Either separately or called from the RandomQuestions procedure to empty before attempting to fill it.

 

[NigelShaw]

Hi Leigh ( sorry for the previous misspelled name )

I had thought about emptying them after as I usually prefer to do that. My thought was to empty after the questions had been answered so to keep the random list during the questionaire.

How would I query using the function? I tried earlier to split the lust of 30 into groups of 4 but then had a mind blank and total lapse!!

I have the list of 30 random numbers so now need to have my continuous form show only 4 records per query until the 30 are used. I would cancel out the numbers via a matching Boolean so want to query the array and collect 4 numbers only if the Booleans are true.

Thanks so far mate

Nidge

 

[LPurvis]

Hi, no worries.

Yes that's why I'd only be emptying the array upon refilling it.
(i.e. the only time you empty it, is when you're about to reselect a new random set).

That way they're definitely always hanging around.
How you then select them depends upon many things.
Do you want to show the records in some numerical order? Or in the order they were (randomly) selected?
Navigating through the rows needs to be handled by you and depends upon how you want to display then in your form.
You'll need to take some control over the process - buttons which navigate through the set of questions. You're then just selecting the "next four" or "previous four".

Querying can be done in various ways.
I'd be tempted to assing the form's source at runtime, building up a query in code based on the four values you select from the array.
e.g.
Me.SubformName.Form.RecordSource = "SELECT * FROM tblQs WHERE QNo In (" & fGetFromArr(intPage) & ")"

Your fGetFromArr function would just select four comma delimited numbers from the array based upon the "page" passed.
For example if page 2 is passed then you'd want to select the four numbers starting on element 5. (intPage - 1) * 2 + 1

Is that making any sense?

Cheers

 

[NigelShaw]

Hi Leigh

kind of makes sense. The order of selection doesn't matter as the questions are completely random anyway so there isn't any filtering yet though once I have this figured out, there will be other very similar queries based on specific types but then I'll cross that bridge another day.

I was thinking of the lines of selecting 1 to 4 then 5 to 8 and so forth just in case a user has to go back. As the user clicks on an answer, the record is updated so if they do go back, the previous answers are shown in readiness.

Can you add the function part to the visual query builder and apply in code? Not to worry if not as any difficult query I will get working with the wizard until I'm happy with it then get the SQL and apply the function after so to speak.

My only thing now is to get the array in groups of 4. Would the best way to this be

IntArr(1 to 4)
IntArr(5 to 8)

etc and have the back button preset to go to previous? Better still, a function that moves in blocks of 4 just in case a user wants to go back more than one page..

Suddenly gets all techy. Well, that's what you get for helping your brother out I guess


Cheers

Nidge

 

[LPurvis]

Well, all it really needs is just that function I described in my previous post.
Which would be something like:

Function fGetFromArr(intPage As Integer) As String
On Error Resume Next
    Dim intI As Integer
    
    For intI = 1 To 4
        fGetFromArr = fGetFromArr & "," & intArr((intPage - 1) * 4 + intI)
    Next
    fGetFromArr = Mid(fGetFromArr, 2)
    
End Function

The question is, how do you want to navigate from set of 4 to set of 4 (i.e. page to page)?

 

[NigelShaw]

Hi Leigh,

i guess i would navigate through with virtual pages. i.e. create a single variable that is increased or decreased per page selection-

Public intPage As Integer

every time the next page button is clicked, intPage = intPage + 1
every time the back page button is clicked, intPage = intPage - 1

then i'd probably reproduce your supplied code into 8 functions each with their own 1 to 4, 5 to 8 etc

then i'd do a select case

Select Case intPage
Case 1
fGetFromArr1
Case 2
fGetFromArr2
Case 3
fGetFromArr3
Case 4
fGetFromArr4
Case 5
fGetFromArr5
Case 6
fGetFromArr6
Case 7
fGetFromArr7
Case 8
fGetFromArr8
End Select

call the select case in the back button & next button to initiate the intPage which would be public.

i'll fiddle around with i tonight

many thanks

Nigel ( who currently has shingles!!! )