Lst of 3 comboboxes is not requerying correctly

[PinkLady50]

I have 3 comboboxes on my form; ContractNum, TaskOrder and CLIN. When I select a ContractNum or TaskOrder and then reselect a new one the selection updates in the combobox and the associated fields.

MY PROBLEM: When I select a CLIN for the first time it displays in the combobox correctly as well as the associated fields. HOWEVER, when I change the selection in the CLIN combobox it is not updating but the associated fields are.

Not sure why this is happening!!

I tried adding a Me.cboCLIN.Requery as the first line and then as the last line of the cboCLIN_Click(). I also tried to add this code to an Events Procedure in After Update. Not of these worked.

Private Sub cboContractNum_Click()
        Me.cboContractNum = Me.cboContractNum.Column(0)
        Me.txtContractor = Me.cboContractNum.Column(1)
        Me.txtNameofMATO = Me.cboContractNum.Column(2)
        Me.cboTaskOrder.Requery
        Me.cboCLIN.Requery
End Sub
Private Sub cboTaskOrder_Click()
        Me.txtTOStartDate = Me.cboTaskOrder.Column(1)
        Me.txtTOEndDate = Me.cboTaskOrder.Column(2)
        Me.txtMTFDTF = Me.cboTaskOrder.Column(3)
        Me.txtCOR = Me.cboTaskOrder.Column(4)
        Me.cboCLIN.Requery
End Sub
Private Sub cboCLIN_Click()
        Me.txtLaborBand = Me.cboCLIN.Column(3)
        Me.txtLaborCat = Me.cboCLIN.Column(4)
        Me.txtSiteofService = Me.cboCLIN.Column(5)
        Me.txtClinicalArea = Me.cboCLIN.Column(6)
        Me.txtIndCov = Me.cboCLIN.Column(7)
        If Me.txtIndCov = "Individual" Then
             Me.txtCovHours.Enabled = False
             Me.txtIndHours.Enabled = True
          Else
             If Me.txtIndCov = "Coverage" Then
             Me.txtIndHours.Enabled = False
             Me.txtCovHours.Enabled = True
          Else
           If Me.txtIndCov = "" Then
             Me.txtIndHours.Enabled = True
             Me.txtCovHours.Enabled = True
          Else
            If Me.txtIndCov = "Service Only" Then
             Me.txtIndHours.Enabled = False
             Me.txtCovHours.Enabled = False
             Me.txtIndCov.SetFocus
           End If
          End If
        End If
      End If
End Sub

[June7]

Suggest using AfterUpdate event instead of Click. Set the dependent comboboxes to Null. Then in the dependent comboboxes GotFocus event, Requery.

Instead of VBA code to set values of textboxes, use expressions in ControlSource property because I hope you are not saving this data which would be duplication. Also, use of Conditional Formatting to enable/disable textboxes and comboboxes could eliminate more VBA.

[PinkLady50]

Where would I set the dependent comboboxes to Null. In the properties or in the code?

I added Me.cboCLIN.Requery and Me.cboTaskOrder.Requery to their events for On Focus.

So far there is no change. Even when I reselect a new Task Order and then select a CLIN but decide to change the CLIN the first choice remains in the CLIN combobox and doesn't refresh.

[June7]

Reset the dependent combobox to null in VBA.

What are the RowSource sql statements?

If you want to provide db for analysis, follow instructions at bottom of my post.

[PinkLady50]

The Null statement aren't working. It prohibits the list from coming up.
Here is the RowSource for ContractNum.

SELECT DISTINCT qryPerfIssuesContractNumber.ContractNum, qryPerfIssuesContractNumber.Contractor, qryPerfIssuesContractNumber.NameofMATO FROM qryPerfIssuesContractNumber ORDER BY qryPerfIssuesContractNumber.ContractNum DESC;

See the attached images for RowSources for the TaskORder and CLIN.

[June7]

Could have just posted SQL statements instead going to bother of images. And most reviewers would prefer to see SQL statement anyway.

I build SQL statement in the RowSource property and don't use the Forms!formname prefix in the WHERE clause. I just type the comboxbox name.

Techniques described have all worked. So at this point would have to review db to debug issue.

[PinkLady50]

I think my problem is actually a little different from what I previously described. The ContractNum and TaskOrder comboboxes are querying , displaying and changing correctly when the user selects a different choice. However, the CLIN combox is querying correctly because the list it produces which ties to the ContractNum and TaskOrder are correct.

My problem is the CLIN combobox is grabbing the first item in the combobox list even if I highlight another choice. For example, If the values in the CLIN are 0001, 0002, 0003 and I highlight 0003, it is displaying 0001 but it will display the associated data for record 0003 in the LaborBand, LaborCat, SiteofService fields etc.

No matter what I do it is defaulting to the first record in the CLIN list and won't change.

[PinkLady50]

I added some code to change the CLIN RowSource but I am getting a Run time error 13 Type Mismatch. But the code is actually changing the CLIN like it is supposed to! Now if I can fix this error I can hopefully get this running!

Private Sub cboCLIN_Click()
    Me.cboCLIN.RowSource = "SELECT CLIN " & _
    "FROM qryPerfIssuesCLIN " & _
    "WHERE ContractNum = " & Nz(Me.cboContractNum) And "WHERE TaskOrder = " & Nz(Me.cboTaskOrder)
      
  Me.txtLaborBand = Me.cboCLIN.Column(3)
        Me.txtLaborCat = Me.cboCLIN.Column(4)
        Me.txtSiteofService = Me.cboCLIN.Column(5)
        Me.txtClinicalArea = Me.cboCLIN.Column(6)
        Me.txtIndCov = Me.cboCLIN.Column(7)
        If Me.txtIndCov = "Individual" Then
             Me.txtCovHours.Enabled = False
             Me.txtIndHours.Enabled = True
          Else
             If Me.txtIndCov = "Coverage" Then
             Me.txtIndHours.Enabled = False
             Me.txtCovHours.Enabled = True
          Else
           If Me.txtIndCov = "" Then
             Me.txtIndHours.Enabled = True
             Me.txtCovHours.Enabled = True
          Else
            If Me.txtIndCov = "Service Only" Then
             Me.txtIndHours.Enabled = False
             Me.txtCovHours.Enabled = False
             Me.txtIndCov.SetFocus
           End If
          End If
        End If
      End If
     
End Sub

[June7]

If fields are text type, need apostrophe delimiters. Also AND needs to be within quotes:

"WHERE ContractNum = '" & Nz(Me.cboContractNum) & "' And WHERE TaskOrder = '" & Nz(Me.cboTaskOrder) & "'"

[PinkLady50]

Solved!!!

The CLIN field was not the first field in the query used for this comboxbox.

[June7]

Oh, and WHERE should not be repeated in the WHERE clause.

"WHERE ContractNum = '" & Nz(Me.cboContractNum) & "' And TaskOrder = '" & Nz(Me.cboTaskOrder) & "'"