Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select Case (A2K)

    I have a delima and was hoping someone could offer a solution.

    I have the following:
    Table: tblQuestions (36 total), pkQuestionID
    Table: tblReponseList (126 Total), fkQuestionID
    Form: frmSurvey
    Form: OnCurrent Event
    Oncurrent Event:
    <pre>Dim sQuestion As String

    sQuestion = Me.QstnLvl1

    Select Case sQuestion
    Case 1
    lbl1.Caption = "Excellent"
    lbl2.Caption = "Fair"
    lbl3.Caption = "Good"
    lbl4.Caption = "Poor"
    lbl5.Caption = "Very Good"
    lbl6.Visible = False
    o6.Visible = False
    lbl6.Caption = ""
    Case 2
    lbl1.Caption = "About the same as a year ago"
    lbl2.Caption = "Much better than a year ago"
    lbl3.Caption = "Much worse than a year ago"
    lbl4.Caption = "Somewhat better than a year ago"
    lbl5.Caption = "Somewhat worse than a year ago"
    lbl6.Visible = False
    o6.Visible = False
    lbl6.Caption = ""
    </pre>

    FrmSurvey: Option box
    Option Box: AfterUpdate Event
    AfterUpdate Event:
    <pre>Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblResponses", dbOpenDynaset)

    With rst
    .AddNew
    ![RspnsID] = Me.tResponseID
    ![QstnID] = QstnLvl1
    ![Rspns] = Me.fmeResponseList
    .Update
    End With

    rst.Close
    </pre>



    My delima is:
    The way I'm setting this up would require 36 Case Statements and that just doesn't seem to be the best approach.

    I thought about the possibility of using a "FOR" or "WITH" statement, but not sure that that's the best approach either.

    Could someone please provide some suggestions for this?

    Thanks,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Select Case (A2K)

    I presume that you have 36 controls called QstnLvl1 thru QstnLvl36. Are these to be addressed by 36 case statements in the OnCurrent event or the AfterUpdate event of the Option box?

    What do you want the OnCurrent event to do with these 36 controls?

    Don't really know what you are trying to do.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Case (A2K)

    The best way would be to store the captions in tblQuestions, and to use locked and disabled text boxes instead of labels. The tex boxes would be bound to the caption fields.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •