Solved Slow Search on Subform

[DatabaseTash]

I have a search form which is performing searches extremely slow! (See screen shot of form attached) I have recently rebuilt the form and changed to a subform to help with locking data from certain users. This new form is searching, just really slowly!!

The Parish Search on this form works quickly, but the the Partial Plan Number Search takes for ever. The Partial plan number search does return a result, it takes too long though. Why would 2 searches on the same form behave so differently? In the table where the data is kept, the fields that are being searched both have the Data Type set to short text. I have tried to index the Plan Number field, but it didn't make any difference.

The code below worked really well before I changed over to the subform layout. I need to keep it as a subform now though to resolve the data locking issues I was having previously.

This is the code that is on each of the search buttons:

'PARISH SEARCH
Private Sub Command96_Click()
Dim strSearch As String
Dim strText As String
strText = Me.txtSearch.Value
Form!frmSubCombinedParishSearch.Form.RecordSource = "SELECT * from tblCadastralPlansRegister where (Parish like ""*" & strText & "*"")"
Me.RecordSource = strSearch
End Sub


'PLAN NUMBER SEARCH
Private Sub Command122_Click()
Dim strSearch As String
Dim strText As String
strText = Me.txtPlanNumber.Value
Form!frmSubCombinedParishSearch.Form.RecordSource = "SELECT * from tblCadastralPlansRegister where ([Plan Number] like ""*" & strText & "*"")"
Me.RecordSource = strSearch
End Sub

Any ideas what else I could try??

 

[pbaldy]

Is the [Plan Number] field indexed? That could explain why the other field is quicker if not (and presuming the other is).

 

[DatabaseTash]

Is the [Plan Number] field indexed? That could explain why the other field is quicker if not (and presuming the other is).

The Plan number field is indexed now, as I tried that to see if it would improve the speed. The search is slow weather indexed or not. The other search which is working correctly (Parish Search) is not indexed.

 

[arnelgp]

have you tried to use Queries.

 

[DatabaseTash]

have you tried to use Queries.

I would like to keep using this form if possible. The people using it search plan number after plan number, which is why this form has been working well.
If I used a query how would that work? Would I add that to the button on this form or would I just make the query show the results in a report?

 

[arnelgp]

the suggestion is simple:

qryParishFiltered:
SELECT * from tblCadastralPlansRegister where (Parish like "*" & Form!frmSubCombinedParishSearch!txtSearch & "*")

qryPlanFiltered:
SELECT * from tblCadastralPlansRegister where ([Plan Number] like "*" & Form!frmSubCombinedParishSearch!txtSearch & "*")

On your code:

'PARISH SEARCH
Private Sub Command96_Click()
Dim strSearch As String
Dim strText As String
strText = Me.txtSearch.Value
Me.RecordSource = "qryParishFiltered"
'Me.RecordSource = strSearch
End Sub


'PLAN NUMBER SEARCH
Private Sub Command122_Click()
Dim strSearch As String
Dim strText As String
strText = Me.txtPlanNumber.Value
Me.RecordSource = "qryPlanFiltered"
'Me.RecordSource = strSearch
End Sub

 

[DatabaseTash]

I'm glad you suggested the query. It makes me wonder weather there is a bigger problem, maybe with the table??

I just set up this basic query with a parameter (see attached). When I searched the plan number it took a long time and I got the "not responding" message on the top bar. When I put the criteria for the parameter on the Parish field and tried I got a result instantly.

This database does have 28,000 records, but the number of records didn't cause any issues before changing to the subform layout.

 

[arnelgp]

[plan number] is Short text?

 

[DatabaseTash]

[plan number] is Short text?

Yes that is correct. Short text

 

[DatabaseTash]

[plan number] is Short text?

The Plan number will always be a mix of letters and numbers eg. RP552263 or IS155267. Should it be something other than short text?

 

[Minty]

By adding the wildcard to the beginning of your search string you are rendering the Index on either field useless.
Do you have to search in that way? Try it by removing the first wild card and I bet it speeds up considerably.

 

[arnelgp]

is [plan number] always starts with "DP" + 6 digit?
you should inform the user (through label) of the correct format:

    Dim strText As String
    Dim intPos As Integer, intLen As Integer
    
    strText = Me!txtPlanNumber & vbNullString
    If Len(strText) = 0 Then Exit Sub
    intPos = InStr(strText, "DP")
    If intPos <> 0 Then
        If intPos <> 1 Then Exit Sub
    Else
        If IsNumeric(strText) = False Then Exit Sub
        strText = "DP" & strText
    End If
    intLen = Len(strText)
    If intLen < 8 Then
        strText = strText & String$(8 - intLen, "#")
    End If
    Form!frmSubCombinedParishSearch.Form.RecordSource = "SELECT * from tblCadastralPlansRegister where ([Plan Number] Like '" & strText & "')"

 

[DatabaseTash]

is [plan number] always starts with "DP" + 6 digit?
you should inform the user (through label) of the correct format:

    Dim strText As String
    Dim intPos As Integer, intLen As Integer
   
    strText = Me!txtPlanNumber & vbNullString
    If Len(strText) = 0 Then Exit Sub
    intPos = InStr(strText, "DP")
    If intPos <> 0 Then
        If intPos <> 1 Then Exit Sub
    Else
        If IsNumeric(strText) = False Then Exit Sub
        strText = "DP" & strText
    End If
    intLen = Len(strText)
    If intLen < 8 Then
        strText = strText & String$(8 - intLen, "#")
    End If
    Form!frmSubCombinedParishSearch.Form.RecordSource = "SELECT * from tblCadastralPlansRegister where ([Plan Number] Like '" & strText & "')"

Thanks, but unfortunately it is not always in that format. These are some examples of plan numbers: C3700, SP320200, RP98500, DP500600 etc. I imagine there will be 7 numbers soon the way the numbering is going. When they search, they will often only search by number as they are never sure of what the letters will be.

 

[DatabaseTash]

By adding the wildcard to the beginning of your search string you are rendering the Index on either field useless.
Do you have to search in that way? Try it by removing the first wild card and I bet it speeds up considerably.

So would that mean that I would have to search for an exact match?
They often search for just the number with no letters, because they are not sure what the letters will be. So for example if I am looking for RP320200 and not sure if it will be SP320200 or RP320200. I would just search by 320200.

Also that code worked instantly before I switched over to the Subform. Would that suggest the subform is the problem? I need to keep using the subform because of permission issues with an ordinary form.

 

[arnelgp]

here i modified the code. the ?? means to search for Alpha, while # is to search for Numeric.
and will search for exact 8 string length:

Use wildcards in queries and parameters in Access - Microsoft Support

support.microsoft.com

    Dim strText As String
    Dim intPos As Integer, intLen As Integer
    
    strText = Me!txtPlanNumber & vbNullString
    intLen = Len(strText)
    If intLen = 0 Then Exit Sub
    'check if numeric only
    If IsNumeric(strText) Then
        'add ?? to the front of text
        strText = "??" & strText
    End If
    strText = Left$(strText & String$(20, "#"), 8)
    Form!frmSubCombinedParishSearch.Form.RecordSource = "SELECT * from tblCadastralPlansRegister where ([Plan Number] Like '" & strText & "')"

 

[DatabaseTash]

here i modified the code. the ?? means to search for Alpha, while # is to search for Numeric.
and will search for exact 8 string length:

Use wildcards in queries and parameters in Access - Microsoft Support

support.microsoft.com

    Dim strText As String
    Dim intPos As Integer, intLen As Integer
   
    strText = Me!txtPlanNumber & vbNullString
    intLen = Len(strText)
    If intLen = 0 Then Exit Sub
    'check if numeric only
    If IsNumeric(strText) Then
        'add ?? to the front of text
        strText = "??" & strText
    End If
    strText = Left$(strText & String$(20, "#"), 8)
    Form!frmSubCombinedParishSearch.Form.RecordSource = "SELECT * from tblCadastralPlansRegister where ([Plan Number] Like '" & strText & "')"

Awesome!

It is almost working!!

So I did a test. I searched for IS191990. I searched 3 different ways and got an instant result for each (191990, IS191990 & ??191990)

The bad news is that the others like these don't return a result:
B11
B158201
AP6484
BON1322
BON79
P4224
CTN485

Some of these plans span back to the year 1884, so there has been many many numbering systems in that time. That is why there is so much variation in the numbers. I'm assuming it is because of the number 8 in the second last line. Is there anyway to get around that?

 

[arnelgp]

there is no fixed pattern, so i guess you will have to settle with your original one:

strtext = "*" & Me!txtPlanNumber & vbnullstring & "*"
Form!frmSubCombinedParishSearch.Form.RecordSource = "SELECT * from tblCadastralPlansRegister where ([Plan Number] Like '" & strText & "')"

 

[DatabaseTash]

there is no fixed pattern, so i guess you will have to settle with your original one:

strtext = "*" & Me!txtPlanNumber & vbnullstring & "*"
Form!frmSubCombinedParishSearch.Form.RecordSource = "SELECT * from tblCadastralPlansRegister where ([Plan Number] Like '" & strText & "')"

Bummer

I'm confused how just changing the type of form could impact the code so much and make it so slow.

Thank you for your efforts @arnelgp

 

[Minty]

I guess we should be asking what the issue is that required you to use a subform?
Is the subform linked to the main form in any way?

 

[DatabaseTash]

I guess we should be asking what the issue is that required you to use a subform?
Is the subform linked to the main form in any way?

I have permissions set that lock the data for certain users. When I had it set up as just a normal form, it wouldn't allow those users to use the search boxes. As soon as I turned it into a subform, it locked the data and allowed them to use the search field.

I inserted the subform onto the form that has the search boxes on it. How do I check that it is linked?