Combobox Search form Selection

[mba_110]

Hi everyone

I am trying to make search form with combo box selection i did good to have it ready for use but one problem.

I have five combo boxes on search form with different field names, all i want is when i select one combo box then rest of the combo boxes should go blank and result should show only with selected combo box.

Option Compare Database

Private Sub cmbContractSearch_ContractID_AfterUpdate()
Dim myContracts As String
myContracts = " Select * from tblEmploymentContracts WHERE ([ContractID] = '" & Me.cmbContractSearch_ContractID & "')"
frmContractsSearch_subform.Form.RecordSource = myContracts
Me.frmContractsSearch_subform.Form.Requery
End Sub

Private Sub cmbContractSearch_ContractStatus_AfterUpdate()
Dim myStatus As String
myStatus = " Select * from tblEmploymentContracts where ([ContractStatus] ='" & Me.cmbContractSearch_ContractStatus & "')"
frmContractsSearch_subform.Form.RecordSource = myStatus
Me.frmContractsSearch_subform.Form.Requery
End Sub


Private Sub cmbContractSearch_EmpID_AfterUpdate()
Dim myemployee As String
myemployee = " Select * from tblEmploymentContracts where ([EmployeeID] ='" & Me.cmbContractSearch_EmpID & "')"
frmContractsSearch_subform.Form.RecordSource = myemployee
Me.frmContractsSearch_subform.Form.Requery
End Sub

Private Sub cmbContractSearch_EmpName_AfterUpdate()
Dim EmployeeName As String
EmployeeName = " Select * from tblEmploymentContracts WHERE ([EmployeeName] = '" & Me.cmbContractSearch_EmpName & "')"
frmContractsSearch_subform.Form.RecordSource = EmployeeName
frmContractsSearch_subform.Form.Requery
End Sub

Private Sub cmbContractSearch_LineManager_AfterUpdate()
Dim myManager As String
myManager = " Select * from tblEmploymentContracts where ([LineManager] ='" & Me.cmbContractSearch_LineManager & "')"
frmContractsSearch_subform.Form.RecordSource = myManager
Me.frmContractsSearch_subform.Form.Requery
End Sub

If possible kindly advise the code for above issue.

thanks & regards,

MA

 

[JHB]

...then rest of the combo boxes should go blank..

What do you mean with that?

 

[mba_110]

The combo box i have selected that combo box should be filled with data only.

Let me give example

i have 5 combo boxes lets say Mr MA has a record in a table that being used here in this form and fields are [EmployeeID] [EmployeeName] [ContractID] [LineManager][ContractStatus] if i select employeeID its showing correct result i have no problem with this but when i select employeeName combo box then EmployeeID is showing my last selection in combo box of EmpID but result is as per my current selection which is EmployeeName this is happing with all combo boxes.

Now i need upon selection of 2nd combo box or 3rd or 4th all should filled with the details of my selected record or stay blank.

 

[Simon_MT]

Try this:

In VBA

Dim SearchCriteria as String

It basically relies on the Len(SearchCriteria) if the SearchCriteria > 0 then you need to concatenate the SearchCrtieria. If the SearchField is populated add to SearchCrtieria else if ignore.

In the example then are logical combinations as then addtional combinations.

Private Function SearchSchemesCriteria() As String

    With CodeContextObject
        If Not IsNull(.[Scheme]) Then
            SearchSchemesCriteria = "[Pig Scheme Number] = '" & .[Scheme] & "'"
        ElseIf Not IsNull(.[Producer]) And Not IsNull(.[Specification]) Then
            SearchSchemesCriteria = "[Producer] = " & .[Producer] & " And [Pig Specification] = '" & .[Specification] & "'"
        ElseIf Not IsNull(.[Producer]) And IsNull(.[Specification]) Then
            SearchSchemesCriteria = "[Producer] = " & .[Producer] & ""
        ElseIf IsNull(.[Producer]) And Not IsNull(.[Specification]) Then
            SearchSchemesCriteria = "[Pig Specification] = '" & .[Specification] & "'"
        Else
            SearchSchemesCriteria = ""
        End If
        
        If Len(SearchSchemesCriteria) <> 0 And Not IsNull(.[Status]) Then
            SearchSchemesCriteria = SearchSchemesCriteria & " And [Scheme Status] = '" & .[Status] & "'"
        ElseIf Len(SearchSchemesCriteria) = 0 And Not IsNull(.[Status]) Then
            SearchSchemesCriteria = "[Scheme Status] = '" & .[Status] & "'"
        End If
    End With

End Function

Simon