Hi All, I am trying to create a series of toggle buttons followed by
cascading combo boxes. The goal is to assign each combo box with a default
value after they have been populated with data. The reason for using default
values is to keep a user from having to re-enter data every time the combo
boxes are traversed. Each of the combo box default values are from data
found in the combo boxes. This is why the “Limit To List” Property is used
(line 5-7).

The toggle button populates the first combo box (lines 11-14) with data and
then assigns a default value to the first field of the first combo box (line
17).

Next, the first combo box populates the second combo box with data (lines
21-24) and then assigns a default value to the first field (line 27) of the
second combo box. This process is repeated for the second combo box.

When the toggle button subroutine tries to assign a default value to the
combo box (line 17), I get the following error: “Run-Time Error ‘2115’ -
The macro or function set to the BeforeUpdate or ValidationRul property for
this field is preventing MS Office from saving the data in the field.”

The toggle button is activated by the “On Click” event and the combo boxes
are activated by an “After Update” event.

Each combo box “Row Source” property is set to “Value Lists”

If you have any suggestions on how to solve this problem, your answers will
be greatly appreciated.”

1. Option Compare Database
2. Option Explicit
3.
4. Private Sub Form_Load()
5. Me.Combo1.LimitToList = True
6. Me.Combo2.LimitToList = True
7. Me.Combo3.LimitToList = True
8. End Sub
9.
10. Private Sub Toggle_Button_Click()
11. Me.Combo1.AddItem "test 1"
12. Me.Combo1.AddItem "test 2"
13. Me.Combo1.AddItem "test 3"
14. Me.Combo1.AddItem "test 4"
15.
16. Me.Combo1.SetFocus
17. Me.Combo1.Text = "test 1"
18. End Sub
19.
20. Private Sub Combo1_AfterUpdate()
21. Me.Combo2.AddItem "test 1-1"
22. Me.Combo2.AddItem "test 1-2"
23. Me.Combo2.AddItem "test 1-3"
24. Me.Combo2.AddItem "test 1-4"
25.
26. Me.Combo3.SetFocus
27. Me.Combo3.Text = "test 1-1"
28. End Sub
29.
30. Private Sub Combo2_AfterUpdate()
31. Me.Combo3.AddItem "test 1-1 a"
32. Me.Combo3.AddItem "test 1-1 b"
33. Me.Combo3.AddItem "test 1-1 c"
34. Me.Combo3.AddItem "test 1-1 d"
35. End Sub

Regards, Eddie Eytchison

"Eddie's Bakery and Cafe'" <(E-Mail Removed)> wrote in message
news:F776DC35-5DC0-4D19-B3F4-(E-Mail Removed)
> Hi All, I am trying to create a series of toggle buttons followed by
> cascading combo boxes. The goal is to assign each combo box with a
> default value after they have been populated with data. The reason
> for using default values is to keep a user from having to re-enter
> data every time the combo boxes are traversed. Each of the combo box
> default values are from data found in the combo boxes. This is why
> the "Limit To List" Property is used (line 5-7).
>
> The toggle button populates the first combo box (lines 11-14) with
> data and then assigns a default value to the first field of the first
> combo box (line 17).
>
> Next, the first combo box populates the second combo box with data
> (lines 21-24) and then assigns a default value to the first field
> (line 27) of the second combo box. This process is repeated for the
> second combo box.
>
> When the toggle button subroutine tries to assign a default value to
> the combo box (line 17), I get the following error: "Run-Time Error
> '2115' - The macro or function set to the BeforeUpdate or
> ValidationRul property for this field is preventing MS Office from
> saving the data in the field."
>
> The toggle button is activated by the "On Click" event and the combo
> boxes are activated by an "After Update" event.
>
> Each combo box "Row Source" property is set to "Value Lists"
>
> If you have any suggestions on how to solve this problem, your
> answers will be greatly appreciated."
>
> 1. Option Compare Database
> 2. Option Explicit
> 3.
> 4. Private Sub Form_Load()
> 5. Me.Combo1.LimitToList = True
> 6. Me.Combo2.LimitToList = True
> 7. Me.Combo3.LimitToList = True
> 8. End Sub
> 9.
> 10. Private Sub Toggle_Button_Click()
> 11. Me.Combo1.AddItem "test 1"
> 12. Me.Combo1.AddItem "test 2"
> 13. Me.Combo1.AddItem "test 3"
> 14. Me.Combo1.AddItem "test 4"
> 15.
> 16. Me.Combo1.SetFocus
> 17. Me.Combo1.Text = "test 1"
> 18. End Sub
> 19.
> 20. Private Sub Combo1_AfterUpdate()
> 21. Me.Combo2.AddItem "test 1-1"
> 22. Me.Combo2.AddItem "test 1-2"
> 23. Me.Combo2.AddItem "test 1-3"
> 24. Me.Combo2.AddItem "test 1-4"
> 25.
> 26. Me.Combo3.SetFocus
> 27. Me.Combo3.Text = "test 1-1"
> 28. End Sub
> 29.
> 30. Private Sub Combo2_AfterUpdate()
> 31. Me.Combo3.AddItem "test 1-1 a"
> 32. Me.Combo3.AddItem "test 1-1 b"
> 33. Me.Combo3.AddItem "test 1-1 c"
> 34. Me.Combo3.AddItem "test 1-1 d"
> 35. End Sub
>
> Regards, Eddie Eytchison

Look at lines 26-27 again. They refer to Combo3, not Combo2.

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

(please reply to the newsgroup)

Hi Dirk, Thanks for taking my question.

Line 27 was a typeo, the new code is below. I added the code for combo box
3 and please note lines 36-37. I forgot to put then in the original message.
Once again, thanks for your help

1. Option Compare Database
2. Option Explicit
3.
4. Private Sub Form_Load()
5. Me.Combo1.LimitToList = True
6. Me.Combo2.LimitToList = True
7. Me.Combo3.LimitToList = True
8. End Sub
9.
10. Private Sub Toggle_Button_Click()
11. Me!Combo1.AddItem "test 1"
12. Me!Combo1.AddItem "test 2"
13. Me!Combo1.AddItem "test 3"
14. Me!Combo1.AddItem "test 4"
15.
16. Me!Combo1.SetFocus
17. Me!Combo1.Text = "test 1"
18. End Sub
19.
20. Private Sub Combo1_AfterUpdate()
21. Me!Combo2.AddItem "test 1-1"
22. Me!Combo2.AddItem "test 1-2"
23. Me!Combo2.AddItem "test 1-3"
24. Me!Combo2.AddItem "test 1-4"
25.
26. Me!Combo2.SetFocus
27. Me!Combo2.Text = "test 1-1"
28. End Sub
29.
30. Private Sub Combo2_AfterUpdate()
31. Me!Combo3.AddItem "test 1-1 a"
32. Me!Combo3.AddItem "test 1-1 b"
33. Me!Combo3.AddItem "test 1-1 c"
34. Me!Combo3.AddItem "test 1-1 d"
35.
36. Me!Combo3.SetFocus
37. Me!Combo3.Text = "test 1-1 a"
38.
39. End Sub
40.
41. Private Sub Combo3_AfterUpdate()
42. ' values filled by combo box 2
43. End Sub



"Dirk Goldgar" wrote:

> "Eddie's Bakery and Cafe'" <(E-Mail Removed)> wrote in message
> news:F776DC35-5DC0-4D19-B3F4-(E-Mail Removed)
> > Hi All, I am trying to create a series of toggle buttons followed by
> > cascading combo boxes. The goal is to assign each combo box with a
> > default value after they have been populated with data. The reason
> > for using default values is to keep a user from having to re-enter
> > data every time the combo boxes are traversed. Each of the combo box
> > default values are from data found in the combo boxes. This is why
> > the "Limit To List" Property is used (line 5-7).
> >
> > The toggle button populates the first combo box (lines 11-14) with
> > data and then assigns a default value to the first field of the first
> > combo box (line 17).
> >
> > Next, the first combo box populates the second combo box with data
> > (lines 21-24) and then assigns a default value to the first field
> > (line 27) of the second combo box. This process is repeated for the
> > second combo box.
> >
> > When the toggle button subroutine tries to assign a default value to
> > the combo box (line 17), I get the following error: "Run-Time Error
> > '2115' - The macro or function set to the BeforeUpdate or
> > ValidationRul property for this field is preventing MS Office from
> > saving the data in the field."
> >
> > The toggle button is activated by the "On Click" event and the combo
> > boxes are activated by an "After Update" event.
> >
> > Each combo box "Row Source" property is set to "Value Lists"
> >
> > If you have any suggestions on how to solve this problem, your
> > answers will be greatly appreciated."
> >
> > 1. Option Compare Database
> > 2. Option Explicit
> > 3.
> > 4. Private Sub Form_Load()
> > 5. Me.Combo1.LimitToList = True
> > 6. Me.Combo2.LimitToList = True
> > 7. Me.Combo3.LimitToList = True
> > 8. End Sub
> > 9.
> > 10. Private Sub Toggle_Button_Click()
> > 11. Me.Combo1.AddItem "test 1"
> > 12. Me.Combo1.AddItem "test 2"
> > 13. Me.Combo1.AddItem "test 3"
> > 14. Me.Combo1.AddItem "test 4"
> > 15.
> > 16. Me.Combo1.SetFocus
> > 17. Me.Combo1.Text = "test 1"
> > 18. End Sub
> > 19.
> > 20. Private Sub Combo1_AfterUpdate()
> > 21. Me.Combo2.AddItem "test 1-1"
> > 22. Me.Combo2.AddItem "test 1-2"
> > 23. Me.Combo2.AddItem "test 1-3"
> > 24. Me.Combo2.AddItem "test 1-4"
> > 25.
> > 26. Me.Combo3.SetFocus
> > 27. Me.Combo3.Text = "test 1-1"
> > 28. End Sub
> > 29.
> > 30. Private Sub Combo2_AfterUpdate()
> > 31. Me.Combo3.AddItem "test 1-1 a"
> > 32. Me.Combo3.AddItem "test 1-1 b"
> > 33. Me.Combo3.AddItem "test 1-1 c"
> > 34. Me.Combo3.AddItem "test 1-1 d"
> > 35. End Sub
> >
> > Regards, Eddie Eytchison
>
> Look at lines 26-27 again. They refer to Combo3, not Combo2.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
>

"Eddie's Bakery and Cafe'" <(E-Mail Removed)> wrote in message
news:A6153A87-E6B7-4F47-ACF2-(E-Mail Removed)
> Hi Dirk, Thanks for taking my question.
>
> Line 27 was a typeo, the new code is below. I added the code for
> combo box 3 and please note lines 36-37. I forgot to put then in the
> original message. Once again, thanks for your help
>
> 1. Option Compare Database
> 2. Option Explicit
> 3.
> 4. Private Sub Form_Load()
> 5. Me.Combo1.LimitToList = True
> 6. Me.Combo2.LimitToList = True
> 7. Me.Combo3.LimitToList = True
> 8. End Sub
> 9.
> 10. Private Sub Toggle_Button_Click()
> 11. Me!Combo1.AddItem "test 1"
> 12. Me!Combo1.AddItem "test 2"
> 13. Me!Combo1.AddItem "test 3"
> 14. Me!Combo1.AddItem "test 4"
> 15.
> 16. Me!Combo1.SetFocus
> 17. Me!Combo1.Text = "test 1"
> 18. End Sub
> 19.
> 20. Private Sub Combo1_AfterUpdate()
> 21. Me!Combo2.AddItem "test 1-1"
> 22. Me!Combo2.AddItem "test 1-2"
> 23. Me!Combo2.AddItem "test 1-3"
> 24. Me!Combo2.AddItem "test 1-4"
> 25.
> 26. Me!Combo2.SetFocus
> 27. Me!Combo2.Text = "test 1-1"
> 28. End Sub
> 29.
> 30. Private Sub Combo2_AfterUpdate()
> 31. Me!Combo3.AddItem "test 1-1 a"
> 32. Me!Combo3.AddItem "test 1-1 b"
> 33. Me!Combo3.AddItem "test 1-1 c"
> 34. Me!Combo3.AddItem "test 1-1 d"
> 35.
> 36. Me!Combo3.SetFocus
> 37. Me!Combo3.Text = "test 1-1 a"
> 38.
> 39. End Sub
> 40.
> 41. Private Sub Combo3_AfterUpdate()
> 42. ' values filled by combo box 2
> 43. End Sub

Interesting. I don't know why it's doing that. I do recommend against
using the Text property of controls when you don't need to. The
following seems to me to accomplish your purpose just as well:

'----- start of alternative code -----
Option Compare Database
Option Explicit

Private Sub Form_Load()
Me.Combo1.LimitToList = True
Me.Combo2.LimitToList = True
Me.Combo3.LimitToList = True
End Sub

Private Sub Toggle_Button_Click()
Me!Combo1.AddItem "test 1"
Me!Combo1.AddItem "test 2"
Me!Combo1.AddItem "test 3"
Me!Combo1.AddItem "test 4"

Me!Combo1 = "test 1"
Combo1_AfterUpdate
End Sub

Private Sub Combo1_AfterUpdate()
Me!Combo2.AddItem "test 1-1"
Me!Combo2.AddItem "test 1-2"
Me!Combo2.AddItem "test 1-3"
Me!Combo2.AddItem "test 1-4"

Me!Combo2 = "test 1-1"
Combo2_AfterUpdate
End Sub

Private Sub Combo2_AfterUpdate()
Me!Combo3.AddItem "test 1-1 a"
Me!Combo3.AddItem "test 1-1 b"
Me!Combo3.AddItem "test 1-1 c"
Me!Combo3.AddItem "test 1-1 d"

Me!Combo3 = "test 1-1 a"
Combo3_AfterUpdate
End Sub

Private Sub Combo3_AfterUpdate()
' values filled by combo box 2
End Sub

'----- end of alternative code -----

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

(please reply to the newsgroup)

Hi Dirk,

It works!!! I can’t tell you how many countless days and sleepless
nights I work on that problem. I really appreciate your help.

Can you tell me why using the Text Property of controls is bad. I would
like to understand this better.

Thanks,

Eddie Eytchison


"Dirk Goldgar" wrote:

> "Eddie's Bakery and Cafe'" <(E-Mail Removed)> wrote in message
> news:A6153A87-E6B7-4F47-ACF2-(E-Mail Removed)
> > Hi Dirk, Thanks for taking my question.
> >
> > Line 27 was a typeo, the new code is below. I added the code for
> > combo box 3 and please note lines 36-37. I forgot to put then in the
> > original message. Once again, thanks for your help
> >
> > 1. Option Compare Database
> > 2. Option Explicit
> > 3.
> > 4. Private Sub Form_Load()
> > 5. Me.Combo1.LimitToList = True
> > 6. Me.Combo2.LimitToList = True
> > 7. Me.Combo3.LimitToList = True
> > 8. End Sub
> > 9.
> > 10. Private Sub Toggle_Button_Click()
> > 11. Me!Combo1.AddItem "test 1"
> > 12. Me!Combo1.AddItem "test 2"
> > 13. Me!Combo1.AddItem "test 3"
> > 14. Me!Combo1.AddItem "test 4"
> > 15.
> > 16. Me!Combo1.SetFocus
> > 17. Me!Combo1.Text = "test 1"
> > 18. End Sub
> > 19.
> > 20. Private Sub Combo1_AfterUpdate()
> > 21. Me!Combo2.AddItem "test 1-1"
> > 22. Me!Combo2.AddItem "test 1-2"
> > 23. Me!Combo2.AddItem "test 1-3"
> > 24. Me!Combo2.AddItem "test 1-4"
> > 25.
> > 26. Me!Combo2.SetFocus
> > 27. Me!Combo2.Text = "test 1-1"
> > 28. End Sub
> > 29.
> > 30. Private Sub Combo2_AfterUpdate()
> > 31. Me!Combo3.AddItem "test 1-1 a"
> > 32. Me!Combo3.AddItem "test 1-1 b"
> > 33. Me!Combo3.AddItem "test 1-1 c"
> > 34. Me!Combo3.AddItem "test 1-1 d"
> > 35.
> > 36. Me!Combo3.SetFocus
> > 37. Me!Combo3.Text = "test 1-1 a"
> > 38.
> > 39. End Sub
> > 40.
> > 41. Private Sub Combo3_AfterUpdate()
> > 42. ' values filled by combo box 2
> > 43. End Sub
>
> Interesting. I don't know why it's doing that. I do recommend against
> using the Text property of controls when you don't need to. The
> following seems to me to accomplish your purpose just as well:
>
> '----- start of alternative code -----
> Option Compare Database
> Option Explicit
>
> Private Sub Form_Load()
> Me.Combo1.LimitToList = True
> Me.Combo2.LimitToList = True
> Me.Combo3.LimitToList = True
> End Sub
>
> Private Sub Toggle_Button_Click()
> Me!Combo1.AddItem "test 1"
> Me!Combo1.AddItem "test 2"
> Me!Combo1.AddItem "test 3"
> Me!Combo1.AddItem "test 4"
>
> Me!Combo1 = "test 1"
> Combo1_AfterUpdate
> End Sub
>
> Private Sub Combo1_AfterUpdate()
> Me!Combo2.AddItem "test 1-1"
> Me!Combo2.AddItem "test 1-2"
> Me!Combo2.AddItem "test 1-3"
> Me!Combo2.AddItem "test 1-4"
>
> Me!Combo2 = "test 1-1"
> Combo2_AfterUpdate
> End Sub
>
> Private Sub Combo2_AfterUpdate()
> Me!Combo3.AddItem "test 1-1 a"
> Me!Combo3.AddItem "test 1-1 b"
> Me!Combo3.AddItem "test 1-1 c"
> Me!Combo3.AddItem "test 1-1 d"
>
> Me!Combo3 = "test 1-1 a"
> Combo3_AfterUpdate
> End Sub
>
> Private Sub Combo3_AfterUpdate()
> ' values filled by combo box 2
> End Sub
>
> '----- end of alternative code -----
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
>

"Eddie's Bakery and Cafe'" <(E-Mail Removed)> wrote in message
news:9213358F-E879-446B-97B3-(E-Mail Removed)
> Hi Dirk,
>
> It works!!! I can't tell you how many countless days and sleepless
> nights I work on that problem. I really appreciate your help.

Very good. You're welcome.

> Can you tell me why using the Text Property of controls is bad. I
> would like to understand this better.

As far as I can tell, the Text property of Access controls is created on
the fly, when the control gets the focus. As such, it's a very
transitory thing, with limits to what you can do with it. Mostly it's
used only when you care about exactly what is displayed in the control
at the moment the user is entering data, and those are rare occasions.
By contrast, the Value property is available at all times, and is more
generally useful in a database application. And, of course, the Value
property has already been converted for you to the underlying data type
of the bound field.

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

(please reply to the newsgroup)