looping through combox in a form access VBA

Question

I am working on access 2010 database. Im fairly new to this and have no much knowledge of VBA.

I have a form (based on a query) that display ina text box the test frequency of certain items. it's named "TestFrequency"

Then I have 3 combo boxes (named: Year1 , Year2 and Year3, respectively).

I had added a button and created an event en "Onclick", what im trying to do is to set the value of the comboboxes depending on the testfrequency value. I also added some messagebox to popup when the event is done.

Im trying with the following code but is not working:

Private Sub Reset_Click()
Dim Response As VbMsgBoxResult
 Response = MsgBox("Do you want to reset planning to default test frequency?", vbQuestion + vbYesNo, "Planning Settings")
If Response = vbNo Then Exit Sub Else
Dim ctrl AS Controls
For Each ctrl From Me.Controls
 If TestFrequency.Value = "Test Annually" Then
   Me.Year1.Value = "Yes"
   Me.Year2.Value = "Yes"
   Me.Year3.Value = "Yes"
   ElseIf TestFrequency.Value = "Test Every 2 years" Then
   Me.Year1.Value = "No"
   Me.Year2.Value = "Yes"
   Me.Year3.Value = "No"
   ElseIf TestFrequency.Value = "Test every 3 years" Then
   Me.Year1.Value = "No"
   Me.Year2.Value = "No"
   Me.Year3.Value = "Yes"
   ElseIf TestFrequency.Value = "Ad-hoc" Then
   Me.Year1.Value = "No"
   Me.Year2.Value = "No"
   Me.Year3.Value = "No"
   End If
Next ctrl
  MsgBox "Settings were changed.", vbInformation
 End If
End Sub

Can somebody please help me? thanks!

You don't need for loop. And get rid of Me. as its within the codes of the form. I don't understand your logic in the form presentation, shouldn't TestFrequency be a ComboBox and when selected/changed, changes the other comboboxes (I would use CheckBox for Year1, Year2, Year3)? — PatricK
Thanks for your comment Patrick. what happens is that "TestFrequency" is a value calculated from other query. Also, I though I would no need for loops but when i don't use loop it only change for the first item of the form (the form is based on a query and contains about 170 items). Finally, the reason why I am using a combobox on Year1, Year2 and Year3 is because i want to give the option to the user of customizing when each item can be tested (year 1,Year2, Year 3) while the click button is a default setting. Thanks! — Matos

PatricK PatricK · Accepted Answer · 2013-09-11T07:16:32

If "Yes" in top of list, and "No" is the next, then try this (ComboBox uses .ListIndex to select):

Private Sub Reset_Click()
    If vbNo = MsgBox("Do you want to reset planning to default test frequency?", vbQuestion + vbYesNo, "Planning Settings") Then Exit Sub
    Select Case TestFrequency.Value
        Case "Test Annually"
            Year1.ListIndex = 0
            Year2.ListIndex = 0
            Year3.ListIndex = 0
        Case "Test Every 2 years"
            Year1.ListIndex = 1
            Year2.ListIndex = 0
            Year3.ListIndex = 1
        Case "Test every 3 years"
            Year1.ListIndex = 1
            Year2.ListIndex = 1
            Year3.ListIndex = 0
        Case "Ad-hoc"
            Year1.ListIndex = 1
            Year2.ListIndex = 1
            Year3.ListIndex = 1
    End Select
    If Err.Number = 0 Then MsgBox "Settings were changed.", vbInformation
End Sub

looping through combox in a form access VBA

1 Answers