Results 1 to 12 of 12
  1. #1
    tmd63 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Oct 2016
    Location
    UK
    Posts
    16

    Question How to Populate an Option field from a Table

    I have a form which has a tables of questions linked to it. I also have an options group called Answers placed on a form.


    There are 4 options and they are called Answer1, Answer2, Answer3 and Answer4. All the questions in the tblQuestions table have been linked to a table called tblAnswers where there are 4 answers per question ONLY. I need the form to use the cboQuestionID combo box to select a question and after a question has been selected, the coding behind the form should populate the Answer fields from a qruey of tblAnswer filtered on cboQuestionID.
    I have a query (qryAnswers) that selects 4 fields "Question, Answers, True and ID". ID is a autonumber for the individual answers, question is a long number ID for the Questions (the same as cboQuestionID is a long), Answers is free text and True is yes/no for the true answer.
    Code:
    Private Sub cboQuestionID_AfterUpdate()
        Dim lngQuestionID As Long
        Dim rstDynaset As DAO.Recordset
        Dim dbDatabase As DAO.Database
        
        Me.lngQuestionID = Me.cboQuestionID.Value
        Set dbDatabase = CurrentDb
        Set rstDynaset = dbDatabase.OpenRecordset("SELECT qryAnswers
        
    End Sub
    Any help on the rstDynaset recordset greatly appreciated.

    P.S. the form answers that need populating are lblAnswer1, lblAnswer2, lblAnswer3 and lblAnswer4.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    perhaps something like

    Code:
    Private Sub cboQuestionID_AfterUpdate()
        'Dim lngQuestionID As Long
        Dim rstDynaset As DAO.Recordset
        Dim dbDatabase As DAO.Database
        dim I as integer
    
        'Me.lngQuestionID = Me.cboQuestionID.Value
        Set dbDatabase = CurrentDb
        Set rstDynaset = dbDatabase.OpenRecordset("SELECT TOP 4 qryAnswers WHERE Question=" & cboQuestionID, dbopensnapshot)
        I=1 
        while not rstDynaset.eof
            me("lblAnswer" & I).caption=rstDynaset!qryAnswers
            I=I+1
        wend
        rstDynaset.close
        set rstDynaset=nothing
    End Sub

  3. #3
    tmd63 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Oct 2016
    Location
    UK
    Posts
    16
    I tried that and got a Run-time error '3075'
    Syntax error (missing operator) in query expression 'qryAnswers WHERE Question=41'
    Code:
        Set rstDynaset = dbDatabase.OpenRecordset("SELECT TOP 4 qryAnswers WHERE Question=" & cboQuestionID, dbopensnapshot)
    I also had a problem with:
    Code:
     me("lblAnswer" & I).caption=rstDynaset!qryAnswers
    As MeAnswer1.caption is not a true valid expression.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    missing operator, usually implies that cboQuestionID is null - debug print the sql before trying to run it

    dim sqlstr as string
    sqlstr="SELECT TOP 4 qryAnswers WHERE Question=" & cboQuestionID
    debug.print sqlstr
    Set rstDynaset = dbDatabase.OpenRecordset(sqlstr, dbopensnapshot)

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    ah - sorry - messed the sql

    modify

    Set rstDynaset = dbDatabase.OpenRecordset("SELECT TOP 4 Answers FROM qryAnswers WHERE Question=" & cboQuestionID, dbopensnapshot)
    I=1
    while not rstDynaset.eof
    me("lblAnswer" & I).caption=rstDynaset!Answers

  6. #6
    tmd63 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Oct 2016
    Location
    UK
    Posts
    16
    Thanks Ajax,
    I changed the dynaset part to:
    Set rstDynaset = dbDatabase.OpenRecordset("SELECT qryAnswers.* FROM qryAnswers WHERE tblQuestions.ID=" & cboQuestionID & ";", dbOpenSnapshot)
    Because the cboQuestionID.value is an ID number reference for the Question.
    The test though is not running to EOF. The local expressions, after setting a break point, appears to show only 1 record in the rstDynaset but the EOF flag is not being set and the records are attempting to go to 5 records where there are only 4 per question. Any ideas why?
    Should the Dynaset be pushed to the end of the recordset first and back to get a length setting?

  7. #7
    tmd63 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Oct 2016
    Location
    UK
    Posts
    16
    I think I have it.
    There is no rstDynaset.MoveNext after I=I+1
    so the recordset is not moving and EOF is not being set.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    clearly I was having a bad day

  9. #9
    tmd63 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Oct 2016
    Location
    UK
    Posts
    16
    I got it.
    There was a missed rstDynaset.MoveNext after the line I=I+1
    It works now. Thanks

  10. #10
    tmd63 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Oct 2016
    Location
    UK
    Posts
    16
    For those that need to see the full results. Here is full code so far for the combo dropdown box. Many thanks to Ajax for the assist.
    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

  11. #11
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    suggest you Set dbDatabase = Nothing as well.
    For the heckofit, I don't think you need to set the recordset to nothing then recreate it. Not that it really matters I suppose, but I would be inclined to
    Code:
    rstDynaset.Close
        Set rstDynaset = dbDatabase.OpenRecordset("SELECT qryQuestions.* FROM qryQuestions WHERE ID=" & cboQuestionID & ";", dbOpenSnapshot)
    rsdynaset is an interesting name choice for a snapshot type of recordset
    Last edited by Micron; 10-28-2016 at 11:24 AM. Reason: spellin

  12. #12
    tmd63 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Oct 2016
    Location
    UK
    Posts
    16
    Quote Originally Posted by Micron View Post
    rsdynaset is an interesting name choice for a snapshot type of recordset
    Yes sorry, I had copied part of the code from a dynamic recordset. I was being a bit lazy (not that is really mattered for such a short piece of code). Maybe I should have called it rsSnapset.
    I like to close any recordset in case Micklesoft leaves random data behind when reopening the same name for a second recordset, just to be safe.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. how to populate field of one table into other
    By Serhioromano in forum Access
    Replies: 1
    Last Post: 06-21-2016, 06:18 AM
  2. Replies: 2
    Last Post: 01-07-2015, 05:47 PM
  3. Unbound Box to populate field in Table
    By quicova in forum Forms
    Replies: 13
    Last Post: 09-13-2013, 09:24 AM
  4. Replies: 3
    Last Post: 05-06-2013, 03:03 PM
  5. Replies: 1
    Last Post: 06-11-2012, 02:34 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums