Solved update the labels of an option group based on a recordset (1 Viewer)

tmd63 · Oct 27, 2016

I have a form with a combo box where users can select a question. each question has a question ID and this filters the Answers table to 4 answers.
But I need these 4 answers text field to populate the forms option group buttons labels and Me("Answer" & i).caption where i loops from 1 to 4 does not work.
Any suggestions?

tmd63 · Oct 27, 2016

This field will not be the only fields to populate, I also want the yes/no true field and clause text fields. These will not me visible but when a user selects an answer their selected will go red and then the true answer will go green and make the clause become visible (if the user selects the correct answer, the answer will go green overwriting the red)

sneuberg · Oct 27, 2016

tmd63 said:
I have a form with a combo box where users can select a question. each question has a question ID and this filters the Answers table to 4 answers.
But I need these 4 answers text field to populate the forms option group buttons labels and Me("Answer" & i).caption where i loops from 1 to 4 does not work.
Any suggestions?

I suggest making deleting the labels and replacing them with the textboxes. I believe you can format the textboxes so no one will know the different, i.e., make the border invisible, change the font name, size, etc.

sneuberg · Oct 27, 2016

tmd63 said:
This field will not be the only fields to populate, I also want the yes/no true field and clause text fiels. These will not me visible but when a user selects an answer thier selected will go red and then the true answer will go green and make the clause become visible (if the user selects the correct answer, the answer will go green overwriting the red)

I'd try using conditional formatting to do this.

tmd63 · Oct 27, 2016

I suggest making deleting the labels and replacing them with the textboxes. I believe you can format the textboxes so no one will know the different, i.e., make the border invisible, change the font name, size, etc.

That sounds like a sound idea but I dont have a subform for the option group to be able to display all 4 records for the 4 answers (each answer is a single record in tblAnswers).
So I need to programmatically load each of the 4 records and populate fields (either option group labels or text boxes) on the same form.

sneuberg · Oct 27, 2016

I've attached a database where in the on current event of the FrmAskQuestion form you will find the following code:

Code:

Private Sub Form_Current()

Dim i As Long
For i = 1 To 4
    Me("Answer" & i).Caption = Nz(DLookup("[Answer]", "[Answers]", "[QuestionID] =  " & Me.QuestionID & " And [QuestionOrder] = " & i))
Next i

End Sub

Which demonstrates that the expression

Code:

Me("Answer" & i).Caption

can work. Maybe you can see in this database what you are doing differently. If you upload your database I'll see if I can figure out what's wrong.

CJ_London · Oct 27, 2016

basically same question cross posted here

http://www.accessforums.net/showthread.php?t=62605

tmd63 · Oct 28, 2016

Hi, Yes. Thank for trying the database, I have Access 2003 and it is not so easy to open the 2013 accdb files. MDB files would be better in 2003 format or 2000.
But I got the basics sorted and I have included the code here for future references.

Code:

Private Sub cboQuestionID_AfterUpdate()     
Dim rstDynaset As DAO.Recordset     
Dim dbDatabase As DAO.Database     
Dim I As Integer      
Set dbDatabase = CurrentDb     
Set rstDynaset = dbDatabase.OpenRecordset("SELECT qryAnswers.* FROM qryAnswers WHERE tblQuestions.ID=" & cboQuestionID & ";", dbOpenSnapshot)     
I = 1     
While Not rstDynaset.EOF         
Me("lblAnswer" & I).Caption = rstDynaset!Answers         
rstDynaset.MoveNext         
I = I + 1     
Wend     
rstDynaset.Close     
Set rstDynaset = Nothing     
Set rstDynaset = dbDatabase.OpenRecordset("SELECT qryQuestions.* FROM qryQuestions WHERE ID=" & cboQuestionID & ";", dbOpenSnapshot)     
Me.Question.Value = rstDynaset!Question     
rstDynaset.Close     
Set rstDynaset = Nothing     
If Len(Me.BLOBDesc) > 0 Then         
cmdShow_Click     
Else         
Me.imgPic.Picture = ""     
End If      
End Sub

tmd63 · Nov 24, 2023

Sorry for the delay, but I had completed my practise database. This is an Access 2007-2016 database with many tricks that others may find useful.

Gasman · Nov 24, 2023

Hey, what is 7 years between members?

Solved update the labels of an option group based on a recordset (1 Viewer)

tmd63

Hobbyist relational database creator

tmd63

Hobbyist relational database creator

sneuberg

AWF VIP

sneuberg

AWF VIP

tmd63

Hobbyist relational database creator

sneuberg

AWF VIP

Attachments

CJ_London

Super Moderator

tmd63

Hobbyist relational database creator

tmd63

Hobbyist relational database creator

Attachments

Gasman

Enthusiastic Amateur

Similar threads

Users who are viewing this thread