OnClick listbox - populating two additional listboxes

[twoplustwo]

Morning all, another forms-based question.

I have a listbox containing customers, once one is selected a second listbox containing contract is populated. When only one contract is found against a customer I am trying to then use the PK from the contract table to retrieve the action(s) ID and populate a second listbox. This is all attempted in the OnClick event in the main customer list.

My problem seems to be that I can't grab the ID from the contract listbox (when only one exists) - the database seems to keep the ID from the previously selected contract.

Any ideas appreciated.

Private Sub lstCustomers_Click()
 
Dim qNoActions As Boolean
Dim iToleranceID As Integer
Dim sCustomer As String
Dim sMessage As String
Dim sSql As String
 
With Me
   .lstActions.RowSource = ""
End With
 
sCustomer = Me.lstCustomers.Value
sSql = "SELECT ToleranceID, StartDate, EndDate, Tolerance, ReviewPeriod, BillingCycle"
sSql = sSql & " FROM tblCustomers"
sSql = sSql & " INNER JOIN tblToleranceDetails"
sSql = sSql & " ON tblCustomers.CustomerID = tblToleranceDetails.CustomerFieldKey"
sSql = sSql & " WHERE CustomerName = '" & sCustomer & "'"
sSql = sSql & " GROUP BY ToleranceID, StartDate, EndDate, Tolerance, ReviewPeriod, BillingCycle"
sSql = sSql & " ORDER BY StartDate DESC"
 
With Me.lstToleranceDetails
   .RowSource = sSql
   .Requery
   If .ListCount = 1 Then 
      If Not IsNull(.Value) Then
         iToleranceID = Me.lstToleranceDetails.Value '**value here doesn't refresh
         sSql = "SELECT ActionDate, ActionShort "
         sSql = sSql & " FROM tblActions"
         sSql = sSql & " INNER JOIN tblToleranceDetails"
         sSql = sSql & " ON tblToleranceDetails.ToleranceID = tblActions.ToleranceFieldKey"
         sSql = sSql & " WHERE ToleranceID = " & iToleranceID
         sSql = sSql & " GROUP BY ActionDate, ActionShort"
         sSql = sSql & " ORDER BY ActionDate DESC"
         With Me.lstActions
            .RowSource = sSql
         End With
      Else
         qNoActions = True
      End If
   Else
      If Not qNoActions Then
         sMessage = .ListCount & " contracts available. Select to view actions."
         MsgBox sMessage, vbInformation, "Select Required Contract"
      End If
   End If
End With
 
End Sub

 

[nathansav]

can you attach the DB to look at?