Select Case or If Then?

[Kurt]

I'm trying to use a cumbersome If...Then statement in the
AfterUpdate event of a combo box (called Rspns). It's not
making any noticable effect and I assume a Select Case
statement would be better anyhow. I just don't know how
to write it. Any ideas? Here's what I have now:

'If user selects "Other" for the Rspns with a
'QstnID of 19, enable and unlock the Rspns with a
'QstnID of 20. On the other hand, if the user
'selects anything but "Other" for this question,
'don't enable the Rspns with a QstnID of 20.

If Me![QstnID].Value = 19 Then
If Me![Rspns].Value = "Other" Then
If Me![QstnID].Value = 20 Then
Me![Rspns].Enabled = True
End If
ElseIf Me![Rspns].Value <> "Other" Then
If Me![QstnID].Value = 20 Then
Me![Rspns].Enabled = False
End If
End If
End If

Thanks. - Kurt

 

[Dirk Goldgar]

I'm trying to use a cumbersome If...Then statement in the
AfterUpdate event of a combo box (called Rspns). It's not
making any noticable effect and I assume a Select Case
statement would be better anyhow. I just don't know how
to write it. Any ideas? Here's what I have now:

'If user selects "Other" for the Rspns with a
'QstnID of 19, enable and unlock the Rspns with a
'QstnID of 20. On the other hand, if the user
'selects anything but "Other" for this question,
'don't enable the Rspns with a QstnID of 20.

If Me![QstnID].Value = 19 Then
If Me![Rspns].Value = "Other" Then
If Me![QstnID].Value = 20 Then
Me![Rspns].Enabled = True
End If
ElseIf Me![Rspns].Value <> "Other" Then
If Me![QstnID].Value = 20 Then
Me![Rspns].Enabled = False
End If
End If
End If

Thanks. - Kurt

I don't follow your logic. How can the same control have a value of
*both* 19 and 20? Could you back up a bit and describe what you're
doing?

 

[Kurt]

I don't follow your logic. How can the same

control have a value of *both* 19 and 20? Could
you back up a bit and describe what you're doing?

Sure.

I've created a data entry form for a survey using a table
and form structure similar to that used in Duane
Hookom's "At Your Survey" database
(http://www.rogersaccesslibrary.com/OtherLibraries.asp).

In brief, when a user selects a RspnsID on the main form
(frmSurveyResponses), the matching record/controls appear
in the subform (sfrmResponses). Likewise, if a user
selects a new record on the main form, a blank data entry
form appears in the subform. The forms are linked by
RspnsID.

These are some of the controls in the detail section of
the subform:

1. QstnText - the survey question
2. Rspns - a combo box with potential answers or blank,
depending on the value in the row source.
3. QstnID - not visible
4. LmtLst - not visible (Y/N, text box)
5. QstnMask - not visible (text box)

The main form is based on:
**************************
SELECT DISTINCTROW tblSrvRspns.RspnsID FROM tblSrvRspns;

The subform is based on:
************************
SELECT DISTINCTROW tblResponses.*, tblQuestions.QstnText,
tblQuestions.LmtLst, tblQuestions.QstnMask
FROM tblResponses RIGHT JOIN tblQuestions ON
tblResponses.QstnID=tblQuestions.QstnID

The Row Source for Rspns is:
****************************
SELECT tblResponsesList.Rspns FROM tblResponsesList WHERE
((tblResponsesList.QstnID=Forms!frmSurveyResponses!
sfrmResponses.Form!QstnID));


Hope this helps make things clearer.

Kurt

-----Original Message-----

I'm trying to use a cumbersome If...Then statement in the
AfterUpdate event of a combo box (called Rspns). It's not
making any noticable effect and I assume a Select Case
statement would be better anyhow. I just don't know how
to write it. Any ideas? Here's what I have now:

'If user selects "Other" for the Rspns with a
'QstnID of 19, enable and unlock the Rspns with a
'QstnID of 20. On the other hand, if the user
'selects anything but "Other" for this question,
'don't enable the Rspns with a QstnID of 20.

If Me![QstnID].Value = 19 Then
If Me![Rspns].Value = "Other" Then
If Me![QstnID].Value = 20 Then
Me![Rspns].Enabled = True
End If
ElseIf Me![Rspns].Value <> "Other" Then
If Me![QstnID].Value = 20 Then
Me![Rspns].Enabled = False
End If
End If
End If

Thanks. - Kurt

I don't follow your logic. How can the same control have a value of
*both* 19 and 20? Could you back up a bit and describe what you're
doing?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[Dirk Goldgar]

I don't follow your logic. How can the same
control have a value of *both* 19 and 20? Could
you back up a bit and describe what you're doing?

Sure.

I've created a data entry form for a survey using a table
and form structure similar to that used in Duane
Hookom's "At Your Survey" database
(http://www.rogersaccesslibrary.com/OtherLibraries.asp).

In brief, when a user selects a RspnsID on the main form
(frmSurveyResponses), the matching record/controls appear
in the subform (sfrmResponses). Likewise, if a user
selects a new record on the main form, a blank data entry
form appears in the subform. The forms are linked by
RspnsID.

These are some of the controls in the detail section of
the subform:

1. QstnText - the survey question
2. Rspns - a combo box with potential answers or blank,
depending on the value in the row source.
3. QstnID - not visible
4. LmtLst - not visible (Y/N, text box)
5. QstnMask - not visible (text box)

The main form is based on:
**************************
SELECT DISTINCTROW tblSrvRspns.RspnsID FROM tblSrvRspns;

The subform is based on:
************************
SELECT DISTINCTROW tblResponses.*, tblQuestions.QstnText,
tblQuestions.LmtLst, tblQuestions.QstnMask
FROM tblResponses RIGHT JOIN tblQuestions ON
tblResponses.QstnID=tblQuestions.QstnID

The Row Source for Rspns is:
****************************
SELECT tblResponsesList.Rspns FROM tblResponsesList WHERE
((tblResponsesList.QstnID=Forms!frmSurveyResponses!
sfrmResponses.Form!QstnID));


Hope this helps make things clearer.

Kurt

-----Original Message-----

I'm trying to use a cumbersome If...Then statement in the
AfterUpdate event of a combo box (called Rspns). It's not
making any noticable effect and I assume a Select Case
statement would be better anyhow. I just don't know how
to write it. Any ideas? Here's what I have now:

'If user selects "Other" for the Rspns with a
'QstnID of 19, enable and unlock the Rspns with a
'QstnID of 20. On the other hand, if the user
'selects anything but "Other" for this question,
'don't enable the Rspns with a QstnID of 20.

If Me![QstnID].Value = 19 Then
If Me![Rspns].Value = "Other" Then
If Me![QstnID].Value = 20 Then
Me![Rspns].Enabled = True
End If
ElseIf Me![Rspns].Value <> "Other" Then
If Me![QstnID].Value = 20 Then
Me![Rspns].Enabled = False
End If
End If
End If

Thanks. - Kurt

I don't follow your logic. How can the same control have a value of
*both* 19 and 20? Could you back up a bit and describe what you're
doing?

Okay, I get the general picture, but I'm puzzled by this bit of the
logic you posted:

If Me![QstnID].Value = 19 Then
If Me![Rspns].Value = "Other" Then
If Me![QstnID].Value = 20 Then

First you check to see of QstnID = 19, then if it is you check to see if
Rspns = "Other", and then if it is you check if QstnID = 20. This makes
no sense, since it's not possible for QstnID to equal both 19 and 20 at
the same time.

 

[Kurt]

Okay, I get the general picture, but I'm puzzled

by this bit of the logic you posted:

If Me![QstnID].Value = 19 Then
If Me![Rspns].Value = "Other" Then
If Me![QstnID].Value = 20 Then

First you check to see of QstnID = 19, then if it is
you check to see if Rspns = "Other", and then if it
is you check if QstnID = 20. This makes no sense,
since it's not possible for QstnID to equal both 19
and 20 at the same time.

Just to make sure we're on the same page . . .

When the subform is populated, about 20 survey questions are listed (each is a value of QstnText from tblQuestions). Next to each question are a few hidden fields/controls. One of them is QstnID, which shows the question's corresponding QsntID (also from tblQuestions).

So, in design view, the subform has one row of controls in the detail section, including (* = hidden):

QstnID* QstnText Rspns LmtLst* QstnMask*

When the subform is populated, it looks something like (* = hidden):

18* "Do you like soup?" [combo box]

19* "Favorite color?" [combo box of possible answers;
LmtLst = Yes]
Red
Green
Blue
Other
20* "Specify color" [combo box; no prefilled
answers; LmtLst = No]

. . .

So, what I'm trying to say - and the syntax is obviously wrong - is, when the user selects "Other" for the Rspns to the question with an ID of 19 ("Favorite color?"), ENABLE the Rspns control for the question with a corresponding ID 20 (the "Specific color" question).

It's as if I need a WHERE statement of sorts. Crudely: If "Other" then enable Rspns WHERE QstnID = 20, otherwise, don't enable.

Does this make sense?

Thank you. - Kurt

-----Original Message-----

I don't follow your logic. How can the same
control have a value of *both* 19 and 20? Could
you back up a bit and describe what you're doing?

Sure.

I've created a data entry form for a survey using a table
and form structure similar to that used in Duane
Hookom's "At Your Survey" database
(http://www.rogersaccesslibrary.com/OtherLibraries.asp).

In brief, when a user selects a RspnsID on the main form
(frmSurveyResponses), the matching record/controls appear
in the subform (sfrmResponses). Likewise, if a user
selects a new record on the main form, a blank data entry
form appears in the subform. The forms are linked by
RspnsID.

These are some of the controls in the detail section of
the subform:

1. QstnText - the survey question
2. Rspns - a combo box with potential answers or blank,
depending on the value in the row source.
3. QstnID - not visible
4. LmtLst - not visible (Y/N, text box)
5. QstnMask - not visible (text box)

The main form is based on:
**************************
SELECT DISTINCTROW tblSrvRspns.RspnsID FROM tblSrvRspns;

The subform is based on:
************************
SELECT DISTINCTROW tblResponses.*, tblQuestions.QstnText,
tblQuestions.LmtLst, tblQuestions.QstnMask
FROM tblResponses RIGHT JOIN tblQuestions ON
tblResponses.QstnID=tblQuestions.QstnID

The Row Source for Rspns is:
****************************
SELECT tblResponsesList.Rspns FROM tblResponsesList WHERE
((tblResponsesList.QstnID=Forms!frmSurveyResponses!
sfrmResponses.Form!QstnID));


Hope this helps make things clearer.

Kurt

-----Original Message-----
I'm trying to use a cumbersome If...Then statement in the
AfterUpdate event of a combo box (called Rspns). It's not
making any noticable effect and I assume a Select Case
statement would be better anyhow. I just don't know how
to write it. Any ideas? Here's what I have now:

'If user selects "Other" for the Rspns with a
'QstnID of 19, enable and unlock the Rspns with a
'QstnID of 20. On the other hand, if the user
'selects anything but "Other" for this question,
'don't enable the Rspns with a QstnID of 20.

If Me![QstnID].Value = 19 Then
If Me![Rspns].Value = "Other" Then
If Me![QstnID].Value = 20 Then
Me![Rspns].Enabled = True
End If
ElseIf Me![Rspns].Value <> "Other" Then
If Me![QstnID].Value = 20 Then
Me![Rspns].Enabled = False
End If
End If
End If

Thanks. - Kurt

I don't follow your logic. How can the same control have a value of
*both* 19 and 20? Could you back up a bit and describe what you're
doing?

Okay, I get the general picture, but I'm puzzled by this bit of the
logic you posted:

If Me![QstnID].Value = 19 Then
If Me![Rspns].Value = "Other" Then
If Me![QstnID].Value = 20 Then

First you check to see of QstnID = 19, then if it is you check to see if
Rspns = "Other", and then if it is you check if QstnID = 20. This makes
no sense, since it's not possible for QstnID to equal both 19 and 20 at
the same time.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[Dirk Goldgar]

Okay, I get the general picture, but I'm puzzled
by this bit of the logic you posted:

If Me![QstnID].Value = 19 Then
If Me![Rspns].Value = "Other" Then
If Me![QstnID].Value = 20 Then

First you check to see of QstnID = 19, then if it is
you check to see if Rspns = "Other", and then if it
is you check if QstnID = 20. This makes no sense,
since it's not possible for QstnID to equal both 19
and 20 at the same time.

Just to make sure we're on the same page . . .

When the subform is populated, about 20 survey questions are listed
(each is a value of QstnText from tblQuestions). Next to each
question are a few hidden fields/controls. One of them is QstnID,
which shows the question's corresponding QsntID (also from
tblQuestions).

So, in design view, the subform has one row of controls in the detail
section, including (* = hidden):

QstnID* QstnText Rspns LmtLst* QstnMask*

When the subform is populated, it looks something like (* = hidden):

18* "Do you like soup?" [combo box]

19* "Favorite color?" [combo box of possible answers;
LmtLst = Yes]
Red
Green
Blue
Other
20* "Specify color" [combo box; no prefilled
answers; LmtLst = No]

. . .

So, what I'm trying to say - and the syntax is obviously wrong - is,
when the user selects "Other" for the Rspns to the question with an
ID of 19 ("Favorite color?"), ENABLE the Rspns control for the
question with a corresponding ID 20 (the "Specific color" question).

It's as if I need a WHERE statement of sorts. Crudely: If "Other"
then enable Rspns WHERE QstnID = 20, otherwise, don't enable.

Does this make sense?

I believe so, but it's trickier than you may have thought. However, I
believe you can do it with conditional formatting and a helper function
that the conditional formatting expression can call to determine whether
the Respns control should be enabled or disabled for a given question.
Try this:

Add the following function to the General section of the subform's code
module:

'----- start of function code -----
Function fncResponseDisabled(QuestionID As Long) As Boolean

Select Case QuestionID

Case 20
fncResponseDisabled = True
With Me.RecordsetClone
.FindFirst "QstnID = " & QuestionID
If Not .NoMatch Then
If !Rspns = "Other" Then
fncResponseDisabled = False
End If
End If
End With

' other cases could be added for other questions.

End Select

End Function
'----- end of function code -----

Also add this event procedure for the subform's AfterUpdate event:

'----- start of code for AfterUpdate event -----
Private Sub Form_AfterUpdate()

Me.Recalc

End Sub

'----- end of code for AfterUpdate event -----

Finally, add this conditional formatting expression (Format ->
Conditional Formatting...) for the Rspns combo box:

Expression is: fncResponseDisabled([QstnID] (disabled)

That is to say, when fncResponseDisabled([QstnID]) is True, the Rspns
control should have its Enabled property set to False.

I haven't tested this out, but I think it should work.

 

[Mike Painter]

I'm trying to use a cumbersome If...Then statement in the
AfterUpdate event of a combo box (called Rspns). It's not
making any noticable effect and I assume a Select Case
statement would be better anyhow. I just don't know how
to write it. Any ideas? Here's what I have now:

'If user selects "Other" for the Rspns with a
'QstnID of 19, enable and unlock the Rspns with a
'QstnID of 20. On the other hand, if the user
'selects anything but "Other" for this question,
'don't enable the Rspns with a QstnID of 20.

If Me![QstnID].Value = 19 Then
If Me![Rspns].Value = "Other" Then
If Me![QstnID].Value = 20 Then
Me![Rspns].Enabled = True
End If
ElseIf Me![Rspns].Value <> "Other" Then
If Me![QstnID].Value = 20 Then
Me![Rspns].Enabled = False
End If
End If
End If

I'm not sure where you execute this but
If Me![QstnID].Value = 19 is false (it's not equal to 19) then the whole
expression is skipped.
If true then
If Me![QstnID].Value = 20 Then
Me![Rspns].Enabled = True
End If
MUST be false and is skipped
Else If Me![Rspns].Value <> "Other" is not needed since it's either other
or not.

The same problem as stated above happens after the elseif because the value
at this point CAN'T be 20.

 

[Kurt]

However, I believe you can do it with conditional

formatting and a helper function that the conditional
formatting expression can call to determine whether
the Respns control should be enabled or disabled for
a given question.

. . .

Wow! Great work. The function and conditional formatting
is *almost* doing the trick. Some problems, though.

I'll stick with this example so we have a visual:

19* "Favorite color?" [combo box of possible answers;
LmtLst = Yes]
Red
Green
Blue
Other
20* "Specify color" [combo box; no prefilled
answers; LmtLst = No]

Applying your suggestions results in the following:

1. The Rspns control for QstnID 20 ("Specify Color") is
not enabled (i.e., it's grayed out), regardless of the
value chosen in the previous question (QstnID 19).

- In the Select Case syntax you suggested, I see the
reference to "Other" (a possible response for QstnID 19),
but I don't see any direct reference to QstnID 19 itself.
Doesn't it need to point to QstnID 19 first, and then
evaluate for "Other"?

2. Tabbing through the controls on the subform (i.e., the
Rspns boxes for each question) is slowed down a bit; I
have to tab twice to move on to the next control. (I
believe the Me.Recalc is contributing to this. It's
making the subform a little . . . "jumpy." when I moved
from control to control.)

Any ideas? So close . . . so, so close. Thank you.

Kurt

-----Original Message-----

Okay, I get the general picture, but I'm puzzled
by this bit of the logic you posted:

If Me![QstnID].Value = 19 Then
If Me![Rspns].Value = "Other" Then
If Me![QstnID].Value = 20 Then

First you check to see of QstnID = 19, then if it is
you check to see if Rspns = "Other", and then if it
is you check if QstnID = 20. This makes no sense,
since it's not possible for QstnID to equal both 19
and 20 at the same time.

Just to make sure we're on the same page . . .

When the subform is populated, about 20 survey questions are listed
(each is a value of QstnText from tblQuestions). Next to each
question are a few hidden fields/controls. One of them is QstnID,
which shows the question's corresponding QsntID (also from
tblQuestions).

So, in design view, the subform has one row of controls in the detail
section, including (* = hidden):

QstnID* QstnText Rspns LmtLst* QstnMask*

When the subform is populated, it looks something like (* = hidden):

18* "Do you like soup?" [combo box]

19* "Favorite color?" [combo box of possible answers;
LmtLst = Yes]
Red
Green
Blue
Other
20* "Specify color" [combo box; no prefilled
answers; LmtLst = No]

. . .

So, what I'm trying to say - and the syntax is obviously wrong - is,
when the user selects "Other" for the Rspns to the question with an
ID of 19 ("Favorite color?"), ENABLE the Rspns control for the
question with a corresponding ID 20 (the "Specific color" question).

It's as if I need a WHERE statement of sorts. Crudely: If "Other"
then enable Rspns WHERE QstnID = 20, otherwise, don't enable.

Does this make sense?

I believe so, but it's trickier than you may have thought. However, I
believe you can do it with conditional formatting and a helper function
that the conditional formatting expression can call to determine whether
the Respns control should be enabled or disabled for a given question.
Try this:

Add the following function to the General section of the subform's code
module:

'----- start of function code -----
Function fncResponseDisabled(QuestionID As Long) As Boolean

Select Case QuestionID

Case 20
fncResponseDisabled = True
With Me.RecordsetClone
.FindFirst "QstnID = " & QuestionID
If Not .NoMatch Then
If !Rspns = "Other" Then
fncResponseDisabled = False
End If
End If
End With

' other cases could be added for other questions.

End Select

End Function
'----- end of function code -----

Also add this event procedure for the subform's AfterUpdate event:

'----- start of code for AfterUpdate event -----
Private Sub Form_AfterUpdate()

Me.Recalc

End Sub

'----- end of code for AfterUpdate event -----

Finally, add this conditional formatting expression (Format ->
Conditional Formatting...) for the Rspns combo box:

Expression is: fncResponseDisabled ([QstnID] (disabled)

That is to say, when fncResponseDisabled([QstnID]) is True, the Rspns
control should have its Enabled property set to False.

I haven't tested this out, but I think it should work.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[Dirk Goldgar]

Wow! Great work.

Well, almost.

The function and conditional formatting
is *almost* doing the trick. Some problems, though.

I'll stick with this example so we have a visual:

19* "Favorite color?" [combo box of possible answers;
LmtLst = Yes]
Red
Green
Blue
Other
20* "Specify color" [combo box; no prefilled
answers; LmtLst = No]

Applying your suggestions results in the following:

1. The Rspns control for QstnID 20 ("Specify Color") is
not enabled (i.e., it's grayed out), regardless of the
value chosen in the previous question (QstnID 19).

- In the Select Case syntax you suggested, I see the
reference to "Other" (a possible response for QstnID 19),
but I don't see any direct reference to QstnID 19 itself.
Doesn't it need to point to QstnID 19 first, and then
evaluate for "Other"?

Arrgh! Yes, I made a mistake. This line:

should be:

.FindFirst "QstnID = 19"

Please pardon my "think-o".

2. Tabbing through the controls on the subform (i.e., the
Rspns boxes for each question) is slowed down a bit; I
have to tab twice to move on to the next control. (I
believe the Me.Recalc is contributing to this. It's
making the subform a little . . . "jumpy." when I moved
from control to control.)

I'll have to think about that. Let's first get the thing functional,
and then see if we can tune it. You're probably right about the recalc
causing the problem, but I don't think the formatting is going to be
adjusted properly without it.

 

[Kurt]

This line:

.FindFirst "QstnID = " & QuestionID
should be:

.FindFirst "QstnID = 19"

I made this change but it doesn't seem to make any
noticable impact. The Rspns control for QstnID 20
("Specify Color") remains not enabled, regardless of the
value chosen in the previous question (QstnID 19).

QstnID is an Autonumber field (if it matters).

I also tried to use a different unique identifier for
QstnID 19: RspnsType (number field). In other words:

.FindFirst "RspnsType = 3"

But the same problem remains.

Any more ideas?

Kurt

-----Original Message-----

Wow! Great work.

Well, almost.

The function and conditional formatting
is *almost* doing the trick. Some problems, though.

I'll stick with this example so we have a visual:

19* "Favorite color?" [combo box of possible answers;
LmtLst = Yes]
Red
Green
Blue
Other
20* "Specify color" [combo box; no prefilled
answers; LmtLst = No]

Applying your suggestions results in the following:

1. The Rspns control for QstnID 20 ("Specify Color") is
not enabled (i.e., it's grayed out), regardless of the
value chosen in the previous question (QstnID 19).

- In the Select Case syntax you suggested, I see the
reference to "Other" (a possible response for QstnID 19),
but I don't see any direct reference to QstnID 19 itself.
Doesn't it need to point to QstnID 19 first, and then
evaluate for "Other"?

Arrgh! Yes, I made a mistake. This line:

should be:

.FindFirst "QstnID = 19"

Please pardon my "think-o".

2. Tabbing through the controls on the subform (i.e., the
Rspns boxes for each question) is slowed down a bit; I
have to tab twice to move on to the next control. (I
believe the Me.Recalc is contributing to this. It's
making the subform a little . . . "jumpy." when I moved
from control to control.)

I'll have to think about that. Let's first get the thing functional,
and then see if we can tune it. You're probably right about the recalc
causing the problem, but I don't think the formatting is going to be
adjusted properly without it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[Dirk Goldgar]

.FindFirst "QstnID = 19"

I made this change but it doesn't seem to make any
noticable impact. The Rspns control for QstnID 20
("Specify Color") remains not enabled, regardless of the
value chosen in the previous question (QstnID 19).

QstnID is an Autonumber field (if it matters).

I also tried to use a different unique identifier for
QstnID 19: RspnsType (number field). In other words:

.FindFirst "RspnsType = 3"

But the same problem remains.

Any more ideas?

Huh. I'm not sure what's wrong. I think in principle it should work --
a much-simplified test case worked for me -- but I can't afford the time
to set up all the tables to debug it. If you'd like, you may send me a
cut-down copy of your database, containing only the elements necessary
to demonstrate the problem, compacted and then zipped to less than 1MB
in size (preferably much smaller), and I'll have a look at it, time
permitting. You can send it to the address derived by removing NO SPAM
from the reply address of this message.