Update a combo box after changing another combo box

[jmartmem]

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

[puppydogbuddy]

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.

[jmartmem]

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).

[KingKen]

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.

[puppydogbuddy]

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.

Private Sub cbo1_GotFocus()
     Me.optGrpProject.Value = 1
     optGrpProject_AfterUpdate
End Sub
 
Private Sub cbo2_GotFocus()
     Me.optGrpProject.Value = 2
     optGrpProject_AfterUpdate
End Sub
 
Private Sub cbo3_GotFocus()
     Me.optGrpProject.Value = 3
     optGrpProject_AfterUpdate
End Sub
 
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.
 
Private Sub optgrpProject_AfterUpdate()
' capture the option group value selected and proceed accordingly
 
On Error GoTo Error_Routine
 
    Select Case Me.optgrpProject.Value
        Case 1                                 'status is yellow
             Me!cboStatus = "Yellow"              
        Case 2                                 'status is blue
             Me!cboStatus = "Blue"
        Case 3                                 'status is green
             Me!cboStatus = "Green"
    End Select
 
 
Exit_Continue:
    Exit Sub
 
Error_Routine:
    MsgBox "Error# " & Err.Number & " " & Err.Description
    Resume Exit_Continue
 
End Sub

[jmartmem]

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.

Private Sub cbo1_GotFocus()
     Me.optGrpProject.Value = 1
     optGrpProject_AfterUpdate
End Sub
 
Private Sub cbo2_GotFocus()
     Me.optGrpProject.Value = 2
     optGrpProject_AfterUpdate
End Sub
 
Private Sub cbo3_GotFocus()
     Me.optGrpProject.Value = 3
     optGrpProject_AfterUpdate
End Sub
 
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.
 
Private Sub optgrpProject_AfterUpdate()
' capture the option group value selected and proceed accordingly
 
On Error GoTo Error_Routine
 
    Select Case Me.optgrpProject.Value
        Case 1                                 'status is yellow
             Me!cboStatus = "Yellow"              
        Case 2                                 'status is blue
             Me!cboStatus = "Blue"
        Case 3                                 'status is green
             Me!cboStatus = "Green"
    End Select
 
 
Exit_Continue:
    Exit Sub
 
Error_Routine:
    MsgBox "Error# " & Err.Number & " " & Err.Description
    Resume Exit_Continue
 
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!!

[puppydogbuddy]

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

[jmartmem]

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

[puppydogbuddy]

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.