Issue Executing a Search Query with Form - Run-time error 2580

[rakotonirinas]

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.

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

[pbaldy]

I'm not a fan of the method, but this should help show the problem (lack of spaces for one thing).

http://www.baldyweb.com/ImmediateWindow.htm

[rkeifer]

Besides fixing your spaces School Details should be [School Details] and [LA Name] should be [LA (name)]

[ssanfu]

Since I am not a fan of changing a form's record source, I made some changes to your dB so you could see a different method (that I think is better).
I filter by seting a form's filter property. I also added some code to clear the filter.

I fixed the object names that had spaces and special characters in them. (This includes form names.)

Here are some sites about naming conventions:
Naming Conventions https://access-programmers.co.uk/for...d.php?t=225837
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html

Also:
Object names should be letters and numbers.
Do not begin an object name with a number.
NO spaces, punctuation or special characters (exception is the underscore) in object names.


Good luck with your project....

[rakotonirinas]

Dear Paul, rkeifer and Steve,

I feel very dumb and inadequate when I read you comments and suggestions as they sound very 'chinese' to me. Yet, I'm sure everything you said is a standard procedure and answer to any Access project.

I obviously need to learn more about spacing in Access and really build a bit of knowledge from what Steve has done to fix the issue. Steve, I will definitely look into them and get my head around them.

Steve, thanks so much for your valuable contribution and help and I can say that what you have done is what I am looking exactly to achieve.

PS: What books do you recommend to get quickly into understanding both Access and its VBA?

Much appreciated all.

Rako

[ssanfu]

We're happy to help.

I added the "Bells" (I didn't add the whistles ) for you to see where things might go and to show you examples of VBA code.


Good luck on your project.