Add row number to randomly selected records

[Martynwheeler]

Hi,

I have a database of questions. I would like to display 5 randomly selected questions from a category on a form to display to the user (in fact this would be displayed on the whiteboard to a class). Here is the sql that I am using:

SELECT TOP 5 tblQuestion.questionID, tblQuestion.questionText, tblQuestion.questionAnswer, tblQuestion.ShowAnswer
FROM tblQuestion
WHERE (((tblQuestion.CategoryID)<=5))
ORDER BY Rnd([questionID]);

I would like each question to be displayed in a textbox on the form. However, I would like to be able to use a checkbox next to the question to toggle the answer into the same textbox.

If I use the above query on a continuous form I can't toggle the answer of individual questions (the toggle acts on all of the questions and displays all of them as answers).

My thought was if I could assign a number 1, 2, 3, 4, 5 to each of the 5 questions in the query, I could then use a crosstab to produce a defined set of textboxes with associated checkboxes.

I cannot figure out how to produce a row number for the query? I tried the approach on this thread (https://599cd.com/tips/access/140703-row-number/) but it did not work.

I have attached the tables and queries that I have made so far.

thanks

Martyn

 

[June7]

This might require writing randomly selected records to a temp table.

CROSSTAB query is not editable so if the idea would be for user to toggle check boxes in CROSSTAB, that won't work.

Review https://www.access-programmers.co.u...sing-unbound-check-boxes.323395/#post-1902906

 

[Martynwheeler]

Thanks for the link. I was thinking that the cross tab would be just the question and answers. No worries. I think it might be simpler to do it all in vba. I could populate an array with the query results and process everything in the form.

 

[ebs17]

You can solve this using conditional formatting.
By default, the font color in the answer field is the same as the background color and is set to black as a condition.

The checkbox cannot be used directly for this purpose.
However, you can create an additional field XX (Long) in the table and pass it on to the form as a bound field via the query. The field can be made invisible.
The checkbox controls the content of field XX.

Private Sub ShowAnswer_AfterUpdate()
    If Me.ShowAnswer Then
        Me.XX = 1
    Else
        Me.XX = Null
    End If
End Sub

You can now apply conditional formatting to questionAnswer via Expression is XX=1 =>black.

 

[June7]

Sounds like this is a single user db and modifying value in table should not be an issue.

I originally defaulted to thinking this is a split db with multiple users.

 

[Martynwheeler]

You can solve this using conditional formatting.
By default, the font color in the answer field is the same as the background color and is set to black as a condition.

The checkbox cannot be used directly for this purpose.
However, you can create an additional field XX (Long) in the table and pass it on to the form as a bound field via the query. The field can be made invisible.
The checkbox controls the content of field XX.

Private Sub ShowAnswer_AfterUpdate()
    If Me.ShowAnswer Then
        Me.XX = 1
    Else
        Me.XX = Null
    End If
End Sub

You can now apply conditional formatting to questionAnswer via Expression is XX=1 =>black.

I was hoping to use the check box to control the source of the textbox. Unchecked it should show the question and checked it should show the answer.

 

[June7]

As you can see, multiple ways to approach.

If you go with yes/no field in table (and I see it is already there), can have expression in textbox that shows either question or answer:

=IIf([ShowAnswer], [questionAnswer], [questionText])

No VBA needed to change the display.

However, might want some code to reset all records back to ShowAnswer false when form closes.

 

[Martynwheeler]

As you can see, multiple ways to approach.

If you go with yes/no field in table (and I see it is already there), can have expression in textbox that shows either question or answer:

=IIf([ShowAnswer], [questionAnswer], [questionText])

No VBA needed to change the display.

However, might want some code to reset all records back to ShowAnswer false when form closes.

I will give it a try

 

[arnelgp]

you can use Paint event in Continuous form.
see ContinuousForm form on your db

 

[Martynwheeler]

you can use Paint event in Continuous form.
see ContinuousForm form on your db

thanks for this, was trying to avoid temp tables but I may have to bite the bullet

 

[GPGeorge]

thanks for this, was trying to avoid temp tables but I may have to bite the bullet

Check out the approach of using a "Side accdb" for the temp tables. I've seen samples here, and I have one on my website as a download.

 

[LarryE]

Hi,

I have a database of questions. I would like to display 5 randomly selected questions from a category on a form to display to the user (in fact this would be displayed on the whiteboard to a class). Here is the sql that I am using:

SELECT TOP 5 tblQuestion.questionID, tblQuestion.questionText, tblQuestion.questionAnswer, tblQuestion.ShowAnswer
FROM tblQuestion
WHERE (((tblQuestion.CategoryID)<=5))
ORDER BY Rnd([questionID]);

I would like each question to be displayed in a textbox on the form. However, I would like to be able to use a checkbox next to the question to toggle the answer into the same textbox.

If I use the above query on a continuous form I can't toggle the answer of individual questions (the toggle acts on all of the questions and displays all of them as answers).

My thought was if I could assign a number 1, 2, 3, 4, 5 to each of the 5 questions in the query, I could then use a crosstab to produce a defined set of textboxes with associated checkboxes.

I cannot figure out how to produce a row number for the query? I tried the approach on this thread (https://599cd.com/tips/access/140703-row-number/) but it did not work.

I have attached the tables and queries that I have made so far.

thanks

Martyn

Use a separate table for the answers linked to the questions table. Then, you can query only those answers where the the checkbox control in the questions table is True. You will need two forms though. One for questions and the other for answers. Then you can use continious forms for both questions and answers. The questions and answers forms can be subforms of the categories form if it is a single form.

 

[Pat Hartman]

You don't need a row number for the query.

Assign sequential numbers as the PK for the questions. No gaps so you can't use an autonumber. Then use an array. Generate 5 random numbers between 1 and x where x = the highest question number and put them in the array. Then create an In() clause from the array and use that to select the questons.

No temp table needed. Don't forget you need to see the Rand function first before generating the random numbers

 

[Martynwheeler]

Here is my finished design. I went with a solution from (https://bytes.com/topic/net/insights/594455-row-numbers-alternate-colors-continuous-form) to save any temp tables etc. It all seems to work. Any comments or obvious design flaws would be useful.

Thanks again for the useful forum,

Martyn

 

[LarryE]

If you open the FrmView in the attached, it will generate 5 random questions from the questions table each time it is opened. Then you can select each answer to be displayed as needed. Look at the VBA code in the forms On Load Event to see how those are generated. Of course you can also put that code into a Public Function and generate new questions on the form by calling the Public Function. You could add a "Generate New Questions" command button on the form to call the Public Function.

 

[Martynwheeler]

Thanks Larry, I will take a look

 

[arnelgp]

Any comments or obvious design flaws would be useful.

consider putting attribution to Leban's code.

 

[LarryE]

Martyn:
Were you going to link the Categories table to your Subjects table so each Subject could have multiple Categories and each Category could have multiple questions?