Dis/En-able combo text box and resetting a form

[TJEngel via AccessMonster.com]

Sorry for the long-windedness! I am a coding amateur.

I am having a few issues with a combo box and a "Reset" command button.

I have a combo box called "PrimaryIssue", and option 6 in the box is "Other".
I have been trying to set it up so that a text box is only enabled if the
user selects the "Other" option. I have been having limited success with the
following code (which I believe I found on this site somewhere):

Private Sub PrimaryIssue_AfterUpdate()
Dim varItm As Variant
For Each varItm In PrimaryIssue.ItemsSelected
If PrimaryIssue.ItemData(varItm) = 6 Then
Me.Text71.Enabled = True
Else
Me.Text71.Enabled = False
Me.Text71.Value = ""
End If
Next varItm
End Sub

Issues I have with this: 1) When a new form opens, with nothing in the combo
box selected, the "Other" text box is enabled, allowing a user to enter text
and submit it even when no combo box option is selected. 2) If the user
selects ONLY option 6, and then deselects option 6, without selecting another
combo box option, the text box remains enabled and text can be entered.
------------
Also having an issue with a "Reset" button to clear all fields in the form.
I have been using this code to reset the combo box:

Private Sub Command46_Click()
Dim n As Integer
For n = 0 To Me.PrimaryIssue.ListCount - 1
Me.PrimaryIssue.Selected(n) = False
Next n

This is followed by the remaining items on the form being set to .Value = "".
Issues I have with this: 1) After pressing "Reset," the "Other" text box
becomes enabled again, even though there are no combo box options selected.
2) The cursor seems to automatically jump to the combo box. How can I get
the cursor to jump to the first item on the form?

Thanks for any help. The due date on this is coming down to the wire! Hope
I have articulated the problem properly.

-TJ

 

[Marshall Barton]

I am having a few issues with a combo box and a "Reset" command button.

I have a combo box called "PrimaryIssue", and option 6 in the box is "Other".
I have been trying to set it up so that a text box is only enabled if the
user selects the "Other" option. I have been having limited success with the
following code (which I believe I found on this site somewhere):

Private Sub PrimaryIssue_AfterUpdate()
Dim varItm As Variant
For Each varItm In PrimaryIssue.ItemsSelected
If PrimaryIssue.ItemData(varItm) = 6 Then
Me.Text71.Enabled = True
Else
Me.Text71.Enabled = False
Me.Text71.Value = ""
End If
Next varItm
End Sub

Issues I have with this:
1) When a new form opens, with nothing in the combo
box selected, the "Other" text box is enabled

If noitems are selected, the code within the loop will not
be executed regardless of the If/Else. You can check the
ItemsSelected collection's Count property to see if it's 0:

If PrimaryIssue.ItemsSelected > 0 Then
For Each varItm In PrimaryIssue.ItemsSelected
If PrimaryIssue.ItemData(varItm) = 6 Then
Me.Text71.Enabled = True
Else
Me.Text71.Enabled = False
Me.Text71.Value = "" 'Null would probably be better
End If
Else
Me.Text71.Enabled = False
Me.Text71.Value = "" 'Null would probably be better
End If

Also, that code will only be run when a user changes the
selection list so it is not used when the form is opened.
To deal with this issue you should move that code to a
function within the form and call the function from both the
PrimaryIssue AfterUpdate and the Form's Load events.

2) If the user
selects ONLY option 6, and then deselects option 6, without selecting another
combo box option, the text box remains enabled and text can be entered.

The above check fo no items selacted should take care of
this.

Also having an issue with a "Reset" button to clear all fields in the form.
I have been using this code to reset the combo box:

Private Sub Command46_Click()
Dim n As Integer
For n = 0 To Me.PrimaryIssue.ListCount - 1
Me.PrimaryIssue.Selected(n) = False
Next n

This is followed by the remaining items on the form being set to .Value = "".
Issues I have with this:
1) After pressing "Reset," the "Other" text box
becomes enabled again, even though there are no combo box options selected.

Presumably your saying combo box here is a brain fault.

I think calling the function with your corrected code should
be all you need here.

Again, I believe that clearing the text box controls to Null
is better than setting them to ""

2) The cursor seems to automatically jump to the combo box. How can I get
the cursor to jump to the first item on the form?

Me.firsttextbox.SetFocus

 

[TJEngel via AccessMonster.com]

Hi Marshall. Thanks a bunch for the suggestions! I am trying to implement a
few of them, but am having trouble. I am unfamiliar with functions and their
roles in a form. How does one "move that code to a function within the form
and call the function"???

I am having a few issues with a combo box and a "Reset" command button.

[quoted text clipped - 18 lines]

1) When a new form opens, with nothing in the combo
box selected, the "Other" text box is enabled

If noitems are selected, the code within the loop will not
be executed regardless of the If/Else. You can check the
ItemsSelected collection's Count property to see if it's 0:

If PrimaryIssue.ItemsSelected > 0 Then
For Each varItm In PrimaryIssue.ItemsSelected
If PrimaryIssue.ItemData(varItm) = 6 Then
Me.Text71.Enabled = True
Else
Me.Text71.Enabled = False
Me.Text71.Value = "" 'Null would probably be better
End If
Else
Me.Text71.Enabled = False
Me.Text71.Value = "" 'Null would probably be better
End If

Also, that code will only be run when a user changes the
selection list so it is not used when the form is opened.
To deal with this issue you should move that code to a
function within the form and call the function from both the
PrimaryIssue AfterUpdate and the Form's Load events.

2) If the user
selects ONLY option 6, and then deselects option 6, without selecting another
combo box option, the text box remains enabled and text can be entered.

The above check fo no items selacted should take care of
this.

Also having an issue with a "Reset" button to clear all fields in the form.
I have been using this code to reset the combo box:

[quoted text clipped - 9 lines]

1) After pressing "Reset," the "Other" text box
becomes enabled again, even though there are no combo box options selected.

Presumably your saying combo box here is a brain fault.

I think calling the function with your corrected code should
be all you need here.

Again, I believe that clearing the text box controls to Null
is better than setting them to ""

2) The cursor seems to automatically jump to the combo box. How can I get
the cursor to jump to the first item on the form?

Me.firsttextbox.SetFocus

 

[Marshall Barton]

Hi Marshall. Thanks a bunch for the suggestions! I am trying to implement a
few of them, but am having trouble. I am unfamiliar with functions and their
roles in a form. How does one "move that code to a function within the form
and call the function"???

Actually a Sub is probably more appropriate than a
Function. Display the form's module and after any End ...
line, add

Private Sub SetEnabled()
' Cut/Paste the code here
End Sub

Then the AfterUpdate event would eimply be:
SetEnabled
with the same line in the form's Load event and in the reset
button's Click event.
--
Marsh
MVP [MS Access]

I am having a few issues with a combo box and a "Reset" command button.

[quoted text clipped - 18 lines]

1) When a new form opens, with nothing in the combo
box selected, the "Other" text box is enabled

If noitems are selected, the code within the loop will not
be executed regardless of the If/Else. You can check the
ItemsSelected collection's Count property to see if it's 0:

If PrimaryIssue.ItemsSelected > 0 Then
For Each varItm In PrimaryIssue.ItemsSelected
If PrimaryIssue.ItemData(varItm) = 6 Then
Me.Text71.Enabled = True
Else
Me.Text71.Enabled = False
Me.Text71.Value = "" 'Null would probably be better
End If
Else
Me.Text71.Enabled = False
Me.Text71.Value = "" 'Null would probably be better
End If

Also, that code will only be run when a user changes the
selection list so it is not used when the form is opened.
To deal with this issue you should move that code to a
function within the form and call the function from both the
PrimaryIssue AfterUpdate and the Form's Load events.

2) If the user
selects ONLY option 6, and then deselects option 6, without selecting another
combo box option, the text box remains enabled and text can be entered.

The above check fo no items selacted should take care of
this.

Also having an issue with a "Reset" button to clear all fields in the form.
I have been using this code to reset the combo box:

[quoted text clipped - 9 lines]

1) After pressing "Reset," the "Other" text box
becomes enabled again, even though there are no combo box options selected.

Presumably your saying combo box here is a brain fault.

I think calling the function with your corrected code should
be all you need here.

Again, I believe that clearing the text box controls to Null
is better than setting them to ""

2) The cursor seems to automatically jump to the combo box. How can I get
the cursor to jump to the first item on the form?

Me.firsttextbox.SetFocus

 

[TJEngel via AccessMonster.com]

Hmm having other issues.

I have this in my form's code:

Private Sub SetEnabled()
If PrimaryIssue.ItemsSelected > 0 Then
For Each varItm In PrimaryIssue.ItemsSelected
If PrimaryIssue.ItemData(varItm) = 6 Then
Me.Text71.Enabled = True
Else
Me.Text71.Enabled = False
Me.Text71.Value = Null
End If
End Sub

I place "SetEnabled" after the PrimaryIssue_AfterUpdate(), ResetButton_Click()
and Form Load events. When I try to reset or open the form, I get a "Compile
error: Argument not optional" on the .ItemsSelected section of the If
statement.

Any ideas? Does it matter if I have other text boxes on the form enable and
disable depending on other list box option selections?

Hi Marshall. Thanks a bunch for the suggestions! I am trying to implement a
few of them, but am having trouble. I am unfamiliar with functions and their
roles in a form. How does one "move that code to a function within the form
and call the function"???

Actually a Sub is probably more appropriate than a
Function. Display the form's module and after any End ...
line, add

Private Sub SetEnabled()
' Cut/Paste the code here
End Sub

Then the AfterUpdate event would eimply be:
SetEnabled
with the same line in the form's Load event and in the reset
button's Click event.

I am having a few issues with a combo box and a "Reset" command button.
[quoted text clipped - 50 lines]

Me.firsttextbox.SetFocus

 

[Marshall Barton]

I have this in my form's code:

Private Sub SetEnabled()
If PrimaryIssue.ItemsSelected > 0 Then
For Each varItm In PrimaryIssue.ItemsSelected
If PrimaryIssue.ItemData(varItm) = 6 Then
Me.Text71.Enabled = True
Else
Me.Text71.Enabled = False
Me.Text71.Value = Null
End If
End Sub

You seem to be missing the declaration statement
Dim varItm As Variant
in the procedure. If it is no longer used in the
AfterUpdate event, move it to the new procedure. If it is
still used, copy it.

The fact thet you did not get a compile error for the
missing declaration statement, tells me you are not using
OPTION EXPLICIT at the top of every module. Make sure you
have that statement in **every** module and turn on the VBA
- Options - Tools - Require Variable Declaration option so
it will be automatically added to new modules.

Actually, I think you should have gotten a compile error for
a missing argument. If you did not compile your project,
get in the habit of using the Debug - Compile menu item
after you have made any code changes and before trying to do
any testing. Along those same lines, you also need to
develop the habit of never editing a form/report module
unless the form/report is in design view to avoid a good
chance of corrupting the module.

I place "SetEnabled" after the PrimaryIssue_AfterUpdate(), ResetButton_Click()
and Form Load events. When I try to reset or open the form, I get a "Compile
error: Argument not optional" on the .ItemsSelected section of the If
statement.

When you do compile your project, what line is highlighted
with the missing argument error?

Any ideas? Does it matter if I have other text boxes on the form enable and
disable depending on other list box option selections?

Not at this point, but I don't what that code is or whether
it works or not so can't be sure.

 

[TJEngel via AccessMonster.com]

Those are great practice tips, I will definitely get in the habit of doing
those. Thanks a bunch, will probably save me a lot of headaches from here on
out.

So far, I have only added "Option Explicit" to the top of my form, without
making any other changes. When I Debug - Compile, I am getting the same
"Compile error: Argument not optional" The error only highlights ".
ItemsSelected" in the "If PrimaryIssue.ItemsSelected > 0 Then" statement.

So I'm not getting that compile error for a missing argument. I tried Debug -
Compile after adding "Dim varItm As Variant" to the "SetEnabled" Sub, but get
the same compile error.

Starting to a feel a bit lost, and not completely sure what the original code
I posted here means (i.e., Dim varItm As Variant, etc.). Is this any closer
to what I should have? I still get the same compile error mentioned above,
but perhaps I am just missing something really simple?

Private Sub SetEnabled()
Dim varItm As Variant
If PrimaryIssue.ItemsSelected > 0 Then
For Each varItm In PrimaryIssue.ItemsSelected
If PrimaryIssue.ItemData(varItm) = 6 Then
Me.Text71.Enabled = True
Else
Me.Text71.Enabled = False
Me.Text71.Value = Null
End If

Private Sub PrimaryIssue_AfterUpdate()
SetEnabled
End Sub

Thanks.
End Sub

I have this in my form's code:

[quoted text clipped - 8 lines]

End If
End Sub

You seem to be missing the declaration statement
Dim varItm As Variant
in the procedure. If it is no longer used in the
AfterUpdate event, move it to the new procedure. If it is
still used, copy it.

The fact thet you did not get a compile error for the
missing declaration statement, tells me you are not using
OPTION EXPLICIT at the top of every module. Make sure you
have that statement in **every** module and turn on the VBA
- Options - Tools - Require Variable Declaration option so
it will be automatically added to new modules.

Actually, I think you should have gotten a compile error for
a missing argument. If you did not compile your project,
get in the habit of using the Debug - Compile menu item
after you have made any code changes and before trying to do
any testing. Along those same lines, you also need to
develop the habit of never editing a form/report module
unless the form/report is in design view to avoid a good
chance of corrupting the module.

I place "SetEnabled" after the PrimaryIssue_AfterUpdate(), ResetButton_Click()
and Form Load events. When I try to reset or open the form, I get a "Compile
error: Argument not optional" on the .ItemsSelected section of the If
statement.

When you do compile your project, what line is highlighted
with the missing argument error?

Any ideas? Does it matter if I have other text boxes on the form enable and
disable depending on other list box option selections?

Not at this point, but I don't what that code is or whether
it works or not so can't be sure.

Hi Marshall. Thanks a bunch for the suggestions! I am trying to implement a
few of them, but am having trouble. I am unfamiliar with functions and their [quoted text clipped - 16 lines]

Me.firsttextbox.SetFocus

 

[Marshall Barton]

Those are great practice tips, I will definitely get in the habit of doing
those. Thanks a bunch, will probably save me a lot of headaches from here on
out.

So far, I have only added "Option Explicit" to the top of my form, without
making any other changes. When I Debug - Compile, I am getting the same
"Compile error: Argument not optional" The error only highlights ".
ItemsSelected" in the "If PrimaryIssue.ItemsSelected > 0 Then" statement.

My fault. The line was supposed to be:

If PrimaryIssue.ItemsSelected.Count > 0 Then

So I'm not getting that compile error for a missing argument. I tried Debug -
Compile after adding "Dim varItm As Variant" to the "SetEnabled" Sub, but get
the same compile error.

That's probably because the compiler stops when it runs into
the first error, you have to fix the compile errors one at a
time and compile again to find the next error.

Starting to a feel a bit lost, and not completely sure what the original code
I posted here means (i.e., Dim varItm As Variant, etc.). Is this any closer
to what I should have? I still get the same compile error mentioned above,
but perhaps I am just missing something really simple?

Private Sub SetEnabled()
Dim varItm As Variant
If PrimaryIssue.ItemsSelected > 0 Then
For Each varItm In PrimaryIssue.ItemsSelected
If PrimaryIssue.ItemData(varItm) = 6 Then
Me.Text71.Enabled = True
Else
Me.Text71.Enabled = False
Me.Text71.Value = Null
End If

Private Sub PrimaryIssue_AfterUpdate()
SetEnabled
End Sub

Just to make your code easier to read/understand, you should
use more consistent indenting. this will help make it
obvious where there are missing End If, Next and End Sub
statements. A very common style is to indent another level
at each program flow (For. If, etc) type of statement:

Private Sub SetEnabled()
Dim varItm As Variant
If PrimaryIssue.ItemsSelected.Count > 0 Then
For Each varItm In PrimaryIssue.ItemsSelected
If PrimaryIssue.ItemData(varItm) = 6 Then
Me.Text71.Enabled = True
Else
Me.Text71.Enabled = False
Me.Text71.Value = Null
End If
Next varitm
End If
End Sub

Private Sub PrimaryIssue_AfterUpdate()
SetEnabled
End Sub

Compare this to what you posted and you should see several
places where you would have gotten more compile errors.

Another habit you should get into is renaming the controls
that you use in control source expressions, VBA code and
query criteria to something meaningful. Beyond the
inference that it's a text box, there is just no information
value in a name like Text71. Maybe something like
txtOtherDetail would be more useful.

 

[TJEngel via AccessMonster.com]

Marshall,

Thank you so much for all of your help and input on this problem! I am
sincerely grateful. I tried out some of the code, and just couldn't resolve
the issue. I am just failing to see/understand something vital. The project
was due yesterday, so I just gave the client what I had. No biggie, I was
volunteering my time. They were real happy.

Again, thank you.

Cheers, TJ

Those are great practice tips, I will definitely get in the habit of doing
those. Thanks a bunch, will probably save me a lot of headaches from here on

[quoted text clipped - 4 lines]

"Compile error: Argument not optional" The error only highlights ".
ItemsSelected" in the "If PrimaryIssue.ItemsSelected > 0 Then" statement.

My fault. The line was supposed to be:

If PrimaryIssue.ItemsSelected.Count > 0 Then

So I'm not getting that compile error for a missing argument. I tried Debug -
Compile after adding "Dim varItm As Variant" to the "SetEnabled" Sub, but get
the same compile error.

That's probably because the compiler stops when it runs into
the first error, you have to fix the compile errors one at a
time and compile again to find the next error.

Starting to a feel a bit lost, and not completely sure what the original code
I posted here means (i.e., Dim varItm As Variant, etc.). Is this any closer

[quoted text clipped - 15 lines]

SetEnabled
End Sub

Just to make your code easier to read/understand, you should
use more consistent indenting. this will help make it
obvious where there are missing End If, Next and End Sub
statements. A very common style is to indent another level
at each program flow (For. If, etc) type of statement:

Private Sub SetEnabled()
Dim varItm As Variant
If PrimaryIssue.ItemsSelected.Count > 0 Then
For Each varItm In PrimaryIssue.ItemsSelected
If PrimaryIssue.ItemData(varItm) = 6 Then
Me.Text71.Enabled = True
Else
Me.Text71.Enabled = False
Me.Text71.Value = Null
End If
Next varitm
End If
End Sub

Private Sub PrimaryIssue_AfterUpdate()
SetEnabled
End Sub

Compare this to what you posted and you should see several
places where you would have gotten more compile errors.

Another habit you should get into is renaming the controls
that you use in control source expressions, VBA code and
query criteria to something meaningful. Beyond the
inference that it's a text box, there is just no information
value in a name like Text71. Maybe something like
txtOtherDetail would be more useful.

 

[Marshall Barton]

Now that you have a chance to take a breath, do you want to
keep trying to get this to work for the next release?
--
Marsh
MVP [MS Access]

Thank you so much for all of your help and input on this problem! I am
sincerely grateful. I tried out some of the code, and just couldn't resolve
the issue. I am just failing to see/understand something vital. The project
was due yesterday, so I just gave the client what I had. No biggie, I was
volunteering my time. They were real happy.

Those are great practice tips, I will definitely get in the habit of doing
those. Thanks a bunch, will probably save me a lot of headaches from here on

[quoted text clipped - 4 lines]

"Compile error: Argument not optional" The error only highlights ".
ItemsSelected" in the "If PrimaryIssue.ItemsSelected > 0 Then" statement.

My fault. The line was supposed to be:

If PrimaryIssue.ItemsSelected.Count > 0 Then

So I'm not getting that compile error for a missing argument. I tried Debug -
Compile after adding "Dim varItm As Variant" to the "SetEnabled" Sub, but get
the same compile error.

That's probably because the compiler stops when it runs into
the first error, you have to fix the compile errors one at a
time and compile again to find the next error.

Starting to a feel a bit lost, and not completely sure what the original code
I posted here means (i.e., Dim varItm As Variant, etc.). Is this any closer

[quoted text clipped - 15 lines]

SetEnabled
End Sub

Just to make your code easier to read/understand, you should
use more consistent indenting. this will help make it
obvious where there are missing End If, Next and End Sub
statements. A very common style is to indent another level
at each program flow (For. If, etc) type of statement:

Private Sub SetEnabled()
Dim varItm As Variant
If PrimaryIssue.ItemsSelected.Count > 0 Then
For Each varItm In PrimaryIssue.ItemsSelected
If PrimaryIssue.ItemData(varItm) = 6 Then
Me.Text71.Enabled = True
Else
Me.Text71.Enabled = False
Me.Text71.Value = Null
End If
Next varitm
End If
End Sub

Private Sub PrimaryIssue_AfterUpdate()
SetEnabled
End Sub

Compare this to what you posted and you should see several
places where you would have gotten more compile errors.

Another habit you should get into is renaming the controls
that you use in control source expressions, VBA code and
query criteria to something meaningful. Beyond the
inference that it's a text box, there is just no information
value in a name like Text71. Maybe something like
txtOtherDetail would be more useful.