CODEWITHSUNDEEP
ms-accesscomboboxvba
Giles
Giles

Reputation: 1

Enable controls based if a record exists

I have a database where participants answer 3 surveys (1,2 and 3) and I am trying to create a form that will determine which surveys have been completed (if any) for a particular participant (ID) and then disable the button(s) (Add survey 1, Add survey 2 and Add survey 3) for existing records.

The table (Surveys) the form is linked to has the fields ID and Survey_Number (1 to 3). Here, one participant (ID) could have 0 to 3 entries depending on how many surveys have been entered.

What I have at the moment works very erratically: I have used a cascading Combobox approach where the second Combobox is used as a temporary array (I apologise for this - I do not code) to hold the Survey_Number for the ID value (in the first Combobox).

Private Sub Combo29_AfterUpdate()
Dim i As Integer

 If Not IsNull(Me.Combo29) Then
  If DCount("ID", "Surveys", "[ID] =" & Me.Combo29) > 0 Then
    Combo2.RowSource = "SELECT Surveys.Survey_Number " & _
                       "FROM Surveys " & _
                       "WHERE Surveys.ID = " & Combo29.Value
Me.Combo2.Requery

i = 0
For i = 0 To (Me.Combo2.ListCount - 1)

Select Case Me.Combo2.ItemData(i)
Case "1"
Me!Command34.Enabled = False
Case "2"
Me!Command35.Enabled = False
Case "3"
Me!Command36.Enabled = False
End Select
i = i + 1
Next
  Else
    MsgBox "This is the first time this participant has responded"
    Me!Command34.Enabled = True
    Me!Command35.Enabled = True
    Me!Command36.Enabled = True
    Me.Combo2 = Me.Combo2.ItemData(0)
  End If
 End If

End Sub

This works well when there are no entries (Enable all buttons) but it does not read ItemData(1) correctly when there are some/one survey present ie. if 1, 2 and 3 have been entered, only buttons 2 and 3 are disabled.

Upvotes: 0

Views: 290

Answers (2)

Sham Yemul
Sham Yemul

Reputation: 463

Use the code the of Enable/Disable on Current event of Form. What happens is when you change your focus to any other record, the enable/disable controls might need to change. And your code for enable/disable is only for afterupdate of combo. we can't enable/disable selected row control, it enables/disables all the controls of column.

Upvotes: 0

random_answer_guy
random_answer_guy

Reputation: 856

I'm not sure how useful Combo2 is but, using the controls you already have, this should work:

Private Sub Combo29_AfterUpdate()

Dim i As Integer

Me!Command34.Enabled = True
Me!Command35.Enabled = True
Me!Command36.Enabled = True

If Not IsNull(Me.Combo29) Then
    If DCount("Survey_Number", "Surveys", "[ID] =" & Me.Combo29) > 0 Then
        Combo2.RowSource = "SELECT Surveys.Survey_Number " & _
                           "FROM Surveys " & _
                           "WHERE Surveys.ID = " & Combo29.Value
        Me.Combo2.Requery

        With Combo2
            For i = 0 To .ListCount - 1
                Select Case .ItemData(i)
                    Case 1
                        Me!Command34.Enabled = False
                    Case 2
                        Me!Command35.Enabled = False
                    Case 3
                        Me!Command36.Enabled = False
                    Case Else
                        'Nothing
                End Select
            Next i
        End With
    Else
        Me!Command34.Enabled = True
        Me!Command35.Enabled = True
        Me!Command36.Enabled = True
    End If

End If

End Sub

Upvotes: 1

Related Questions