Assigning Default Values To Cascading Combo Boxes

G

Guest

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
 
D

Dirk Goldgar

Eddie's Bakery and Cafe' said:
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
Click to expand...

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

Guest

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
 
D

Dirk Goldgar

Eddie's Bakery and Cafe' said:
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
Click to expand...

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 -----
 
G

Guest

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
 
D

Dirk Goldgar

Eddie's Bakery and Cafe' said:
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.
Click to expand...

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.
Click to expand...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Having trouble with combo boxes. 11
How to fill a combo box with choice from another combo box 7
Unbound combobox AfterUpdate 5
Combo Boxes getting data from Work Sheet. 4
cascading combo boxes 1
combo box data type 1
Same Items in 3 different Combo Boxes 3
multiple cascading combo 3

Top