Hello,
I'm very new with Access but it seems it's the only way to properly manipulate data which I need to analyse in the future.
I am currently attempting to execute a Search by using a Form with ComboBoxes under the form: frm_School_Search_Criteria, which includes a sub-form dubbed tbl_School_Search_SB01.
The idea is that I can choose up to 5 criteria (Diocese, Local Authority, Status Type, Education Phase, and DfE Region) to filter my data.
All sources of every ComboBox is taken from the corresponding table except for the DfE Region which is extracted from the School Details.
What constitutes the sub-form data is a combination of data from both the tables School Details and School Trusts.
Not all 5 ComboBoxes have to be populated but they should be interinked once it's populated.
I used VBA to execute the search but it came up with the run-time error '2580'..."The Record source Select ..."
Clearly I believe it's the VBA is where the issue is but cannot resolve the issue to make it work.
Below is the code I use for executing the search.
I really appreciate your help and guide please. Thank you.
Code:
Option Compare Database
Option Explicit
Private Sub cboDiocese_AfterUpdate()
Call SearchCriteria
End Sub
Private Sub cboLA_AfterUpdate()
Call SearchCriteria
End Sub
Private Sub cboPhase_AfterUpdate()
Call SearchCriteria
End Sub
Private Sub cboRegion_AfterUpdate()
Call SearchCriteria
End Sub
Private Sub cboStatus_AfterUpdate()
Call SearchCriteria
End Sub
Function SearchCriteria()
Dim strDiocese As String, strLA As String, strStatus As String, strPhase As String, strRegion As String
Dim strTask As String, strCriteria As String
If IsNull(Me.cboDiocese) Then
strDiocese = "[Diocese (code)] like '*'"
Else
strDiocese = "[Diocese (code)] = '" & Me.cboDiocese & "'"
End If
If IsNull(Me.cboLA) Then
strLA = "[LA Name] like '*'"
Else
strLA = "[LA Name]= '" & Me.cboLA & "'"
End If
If IsNull(Me.cboStatus) Then
strStatus = "[TypeOfEstablishment (name)] like '*'"
Else
strStatus = "[TypeOfEstablishment (name)]= '" & Me.cboStatus & "'"
End If
If IsNull(Me.cboPhase) Then
strPhase = "[Education Phase] like '*'"
Else
strPhase = "[Education Phase]= '" & Me.cboPhase & "'"
End If
If IsNull(Me.cboRegion) Then
strRegion = "[DfE Region] like '*'"
Else
strRegion = "[DfE Region]= '" & Me.cboRegion & "'"
End If
strCriteria = strDiocese & "And" & strLA & "And" & strStatus & "And" & strPhase & "And" & strRegion
strTask = "Select * from School Details where " & strCriteria
Me.tbl_School_Search_SB01.Form.RecordSource = strTask
Me.tbl_School_Search_SB01.Form.Requery
End Function