By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,505 Members | 1,549 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,505 IT Pros & Developers. It's quick & easy.

Update a combo box after changing another combo box

P: 87
Greetings,

I have an Access 2002 continuous form called "Project Update Form" in which users can update project values presented in a series of combo boxes and text boxes. I have three combo boxes ("FY", "Qtr" and "Mth") that contain the values for a project's deadline: fiscal year, quarter and month, respectively.

My goal is to build an AfterUpdate event procedure such that when a user changes a value for any of the three aforementioned combo boxes, an automatic change will occur to the value in a fourth combo box (named "Status"), changing its value to a pre-determined setting (in this case, "Yellow"), which is sourced from a query ("QryUniqueStatus").

Whatever help you can offer would be most appreciated.

Regards,

- JM
Feb 12 '08 #1
Share this Question
Share on Google+
8 Replies


puppydogbuddy
Expert 100+
P: 1,923
Greetings,

I have an Access 2002 continuous form called "Project Update Form" in which users can update project values presented in a series of combo boxes and text boxes. I have three combo boxes ("FY", "Qtr" and "Mth") that contain the values for a project's deadline: fiscal year, quarter and month, respectively.

My goal is to build an AfterUpdate event procedure such that when a user changes a value for any of the three aforementioned combo boxes, an automatic change will occur to the value in a fourth combo box (named "Status"), changing its value to a pre-determined setting (in this case, "Yellow"), which is sourced from a query ("QryUniqueStatus").

Whatever help you can offer would be most appreciated.

Regards,

- JM
One thing is not clear....Do you want the background color in Status to change to yellow or do you want the Status to say "Yellow"? In any event, the easiest way to accomplish what you want would be to enclose the 3 combo boxes in an option group frame from Access's toolbox. Once you do that, you will be able to code for a change in any of the 3 comboboxes in the AfterUpdate of the option group. If you need further assistance with the code,post back.
Feb 13 '08 #2

P: 87
One thing is not clear....Do you want the background color in Status to change to yellow or do you want the Status to say "Yellow"? In any event, the easiest way to accomplish what you want would be to enclose the 3 combo boxes in an option group frame from Access's toolbox. Once you do that, you will be able to code for a change in any of the 3 comboboxes in the AfterUpdate of the option group. If you need further assistance with the code,post back.

I enclosed the 3 combo boxes in an option group frame but could use your assistance with the AfterUpdate code.

Thanks,

- JM

P.S. I want the Status to change to the value "Yellow" (not the color).
Feb 13 '08 #3

P: 68
I too would love to see this code since i am doing the same with aome combo boxes of mine. By the way this forum is the best... Thanks guys for the hard work you put in.
Feb 13 '08 #4

puppydogbuddy
Expert 100+
P: 1,923
Ok, let's get started with code, The code has to be improvised because (as you know) comboboxes are not one of the default options provided by the option group frame. So, if you've taken the first step of placing an option group frame around your 3 comboboxes, you had to select either option buttons, checkboxes, or toggle buttons. So at this point you should have a buttons or checkbox next to each of your comboboxes. Here is the steps you need to take to simulate the option buttons with your comboboxes.
1. Delete all of the buttons or checkboxes, and their labels.....you won't be needing them.
2. Let's assume that the option group frame is named optGrpProject and the comboboxes are named cbo1, cbo2, and cbo3.
3. code onGotFocus events for each of the comboboxes, assigning a value that is passed on to the option group. Note that the AfterUpdate event for the option group is called after the option group value has been assigned. The AfterUpdate has to be called because it does not fire if the update is made via code instead of via data input.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cbo1_GotFocus()
  2.      Me.optGrpProject.Value = 1
  3.      optGrpProject_AfterUpdate
  4. End Sub
  5.  
  6. Private Sub cbo2_GotFocus()
  7.      Me.optGrpProject.Value = 2
  8.      optGrpProject_AfterUpdate
  9. End Sub
  10.  
  11. Private Sub cbo3_GotFocus()
  12.      Me.optGrpProject.Value = 3
  13.      optGrpProject_AfterUpdate
  14. End Sub
  15.  
  16. 4. Code the AfterUpdate event of the option group.  After this has been coded and tested, let me know any refinements you want to make.
  17.  
  18. Private Sub optgrpProject_AfterUpdate()
  19. ' capture the option group value selected and proceed accordingly
  20.  
  21. On Error GoTo Error_Routine
  22.  
  23.     Select Case Me.optgrpProject.Value
  24.         Case 1                                 'status is yellow
  25.              Me!cboStatus = "Yellow"              
  26.         Case 2                                 'status is blue
  27.              Me!cboStatus = "Blue"
  28.         Case 3                                 'status is green
  29.              Me!cboStatus = "Green"
  30.     End Select
  31.  
  32.  
  33. Exit_Continue:
  34.     Exit Sub
  35.  
  36. Error_Routine:
  37.     MsgBox "Error# " & Err.Number & " " & Err.Description
  38.     Resume Exit_Continue
  39.  
  40. End Sub
Feb 13 '08 #5

P: 87
Ok, let's get started with code, The code has to be improvised because (as you know) comboboxes are not one of the default options provided by the option group frame. So, if you've taken the first step of placing an option group frame around your 3 comboboxes, you had to select either option buttons, checkboxes, or toggle buttons. So at this point you should have a buttons or checkbox next to each of your comboboxes. Here is the steps you need to take to simulate the option buttons with your comboboxes.
1. Delete all of the buttons or checkboxes, and their labels.....you won't be needing them.
2. Let's assume that the option group frame is named optGrpProject and the comboboxes are named cbo1, cbo2, and cbo3.
3. code onGotFocus events for each of the comboboxes, assigning a value that is passed on to the option group. Note that the AfterUpdate event for the option group is called after the option group value has been assigned. The AfterUpdate has to be called because it does not fire if the update is made via code instead of via data input.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cbo1_GotFocus()
  2.      Me.optGrpProject.Value = 1
  3.      optGrpProject_AfterUpdate
  4. End Sub
  5.  
  6. Private Sub cbo2_GotFocus()
  7.      Me.optGrpProject.Value = 2
  8.      optGrpProject_AfterUpdate
  9. End Sub
  10.  
  11. Private Sub cbo3_GotFocus()
  12.      Me.optGrpProject.Value = 3
  13.      optGrpProject_AfterUpdate
  14. End Sub
  15.  
  16. 4. Code the AfterUpdate event of the option group.  After this has been coded and tested, let me know any refinements you want to make.
  17.  
  18. Private Sub optgrpProject_AfterUpdate()
  19. ' capture the option group value selected and proceed accordingly
  20.  
  21. On Error GoTo Error_Routine
  22.  
  23.     Select Case Me.optgrpProject.Value
  24.         Case 1                                 'status is yellow
  25.              Me!cboStatus = "Yellow"              
  26.         Case 2                                 'status is blue
  27.              Me!cboStatus = "Blue"
  28.         Case 3                                 'status is green
  29.              Me!cboStatus = "Green"
  30.     End Select
  31.  
  32.  
  33. Exit_Continue:
  34.     Exit Sub
  35.  
  36. Error_Routine:
  37.     MsgBox "Error# " & Err.Number & " " & Err.Description
  38.     Resume Exit_Continue
  39.  
  40. End Sub

That did the trick! I made one small adjustment: I changed the event procedure from 'GotFocus()' to 'Change()' as I want the AfterUpdate procedure to occur on a change to the date fields.

One more question, if I may: I want to lock the value in cboStatus following the AfterUpdate procedure. This is because I don't want someone to go back and change the status after a date change.

For each of your three cases above, I added a line of code following the Me!cboStatus = " " to read like this, for example:

Select Case Me.optGrpProject.Value
Case 1 'status changes to YELLOW
Me!cboStatus = "YELLOW"
Me!cboStatus.Locked

But when I execute the code I get the following Access error message:

Error #438: Object doesn't support this property or method.

I'm working in Access 2002. Is there another way to code the procedure to lock this field's value?

Thanks a bunch for your help!!
Feb 14 '08 #6

puppydogbuddy
Expert 100+
P: 1,923
That did the trick! I made one small adjustment: I changed the event procedure from 'GotFocus()' to 'Change()' as I want the AfterUpdate procedure to occur on a change to the date fields.

One more question, if I may: I want to lock the value in cboStatus following the AfterUpdate procedure. This is because I don't want someone to go back and change the status after a date change.

For each of your three cases above, I added a line of code following the Me!cboStatus = " " to read like this, for example:

Select Case Me.optGrpProject.Value
Case 1 'status changes to YELLOW
Me!cboStatus = "YELLOW"
Me!cboStatus.Locked

But when I execute the code I get the following Access error message:

Error #438: Object doesn't support this property or method.

I'm working in Access 2002. Is there another way to code the procedure to lock this field's value?

Thanks a bunch for your help!!

Hmm....the combobox has a locked property, so the error is probably occuring the following reason:

You have a syntax error.
change: Me!cboStatus.Locked to: Me!cboStatus.Locked = True
Feb 14 '08 #7

P: 87
Hmm....the combobox has a locked property, so the error is probably occuring the following reason:

You have a syntax error.
change: Me!cboStatus.Locked to: Me!cboStatus.Locked = True
Thanks! That worked, but I forgot to mention that I wanted the value locked only for the specific record where the date changed. In other words, if I have 10 records in my table but only 1 whose date changed, I want the cboStatus to be locked for the one record only. Is that possible?

Sorry for the omission. You've been extremely helpful!

- JM
Feb 14 '08 #8

puppydogbuddy
Expert 100+
P: 1,923
Thanks! That worked, but I forgot to mention that I wanted the value locked only for the specific record where the date changed. In other words, if I have 10 records in my table but only 1 whose date changed, I want the cboStatus to be locked for the one record only. Is that possible?

Sorry for the omission. You've been extremely helpful!

- JM
Off the top of my head, I can't think of a way that you can lock just a control on a specifc record or subset of records ,,,,you would have to lock the entire record or records. If locking the entire record is not a problem, and the cboStatus combobox identifies the record or records to be locked, then you might be able to use Allen Browne's suggestion in the following link regarding the use of a textbox.

http://www.thescripts.com/forum/thread215953.html

Let me know what happens.
Feb 14 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.