Solved Very weird behavior- Error '3709': The search key not found in any record

[Babycat]

Dear everyone

I have been struggling for 3 days to debug but only narrow down some clues...
I have a mainform and a continous subform, they act like split form.

Main form has button to filter out records on subform, and when user click on a record in subform the main form will show current record detail information.
It works fine when no filter applied on subform.

When applying a filter (type "ABC" in search box), subform's records are filterd out. However when I click on a record on subform, it will introduce error #3709

I have narrowed down the causes and find it causes by following code in form's filter

        wildcardstr = " like '*" & str & "*') "
        Strfilter = "(VshortName" & wildcardstr & "OR (VFullName" & wildcardstr _
                    & "OR (Country" & wildcardstr & "OR (ContactName" & wildcardstr _
                    & "OR (ROCNumber" & wildcardstr & "OR (Note" & wildcardstr

IF I make less criteria such as

        wildcardstr = " like '*" & str & "*') "
        Strfilter = "(VshortName" & wildcardstr & "OR (VFullName" & wildcardstr _
                    & "OR (ContactName" & wildcardstr

then I see It works again.

I attached BD for your kind help.
I am too much exhausted )

 

[mike60smart]

Hi
I cannot replicate the error/ When I click in a record in the Subform it displays that specific record selected.

I do however query why you need a subform when you only have 1 Table.

 

[Gasman]

Have you actually tried and debug.print the filter?

 

[Babycat]

Hi
I cannot replicate the error/ When I click in a record in the Subform it displays that specific record selected.

I do however query why you need a subform when you only have 1 Table.

Please apply type "ABC" in the search box first, then you click on #2, #3 record of filtered result.

 

[mike60smart]

Please apply type "ABC" in the search box first, then you click on #2, #3 record of filtered result.

Hi
That is exactly what I did. No error.

 

[Babycat]

Have you actually tried and debug.print the filter?

Yes, I tried it. I found that assigning bookmark on mainform causes subform's on-current event fires. That's why I said it is very strange/weird behavior.
A question is, why does it not happen when reducing filter criteria string...?

 

[Babycat]

Hi
That is exactly what I did. No error.

Oh, sorry the attached file was with "short-criteria fillter string". That is why no error.
I have re-attached the correct DB file . Please help to take a look again

 

[ebs17]

Strfilter = "(VshortName" & wildcardstr & "OR (VFullName" & wildcardstr _
                    & "OR (Country" & wildcardstr & "OR (ContactName" & wildcardstr _
                    & "OR (ROCNumber" & wildcardstr & "OR (Note" & wildcardstr

There should be a space before each OR.

 

[Babycat]

There should be a space before each OR.

wildcardstr = " like '*" & str & "*') "

The wildcardstr is with a SPACE at its end, so it is OK

 

[MajP]

There is something really strange. To demonstrate i put record selectors back and a message box on the current event. Filter the list then click on a record. It moves back to the first record twice. I cannot figure out. There must be something linked I do not see, because the code should not do that.

I can fix this by modifying the code, but I think you could try rebuilding the form.

 

[Gasman]

wildcardstr = " like '*" & str & "*') "

The wildcardstr is with a SPACE at its end, so it is OK

Yes, nothing like making it harder to read.
Putting the space at the start of each concatenated line, would remove the need to scroll to the end of each line.

 

[Babycat]

There is something really strange. To demonstrate i put record selectors back and a message box on the current event. Filter the list then click on a record. It moves back to the first record twice. I cannot figure out. There must be something linked I do not see, because the code should not do that.

I can fix this by modifying the code, but I think you could try rebuilding the form.

Hi MajP.

The attached db is already fresh-build. I thought my form was corrupt but rebuid it did not help.
May you please digout further from clue that the error does not happen with short-criteria filter string?

 

[Babycat]

Yes, nothing like making it harder to read.
Putting the space at the start of each concatenated line, would remove the need to scroll to the end of each line.

Sorry about that, it costs nothing with sapce before "OR"... but this is cut-down version I did not have must time to make it clearer

 

[MajP]

This thing is acting so bizarre i cannot figure it out. I can no longer modify it. At this point it is a waste of time to look at it. I could rebuild from scratch faster. I cannot locate what is happening.

 

[MajP]

My first guess is the whole thing is corrupted, unless there is just something I am not seeing.

 

[Josef P.]

Try this:
Set RecordSource instead of Filter:

Private Sub User_VendorsFnameFilterParameter(str As String)

   Dim SQLfilter As String
   Dim SelectSql As String

   SelectSql = "select * from Q_QinfoVendor_Source"
   If Len(str) > 0 Then
      SQLfilter = User_VendorsQinfoFilterString(str)
      SelectSql = SelectSql & " where " & SQLfilter
   End If

   With Me.SubFrmQInfo
      .Form.RecordSource = SelectSql
      .LinkChildFields = vbNullString
      .LinkMasterFields = vbNullString
   End With

End Sub

The better solution would be to use an unbound main form with 2 subforms - 1 subform with the list and 1 subform with the detail data.

 

[Babycat]

My first guess is the whole thing is corrupted, unless there is just something I am not seeing.

I was guesting same then I build forms from scratch but same error. Please build the form at your side. Hope you can re-use my data, i suppect the local language characters might make filter work improperly... if so it is my nightmare

 

[MajP]

I will take a look but the weird behavior seems unrelated to the code.

 

[Babycat]

Dear all.

Solved.

With your advices, i followed and found that the subform record source was not table TBLVendor but from query Q_QinfoVendor_Source.
And in Q_QinfoVendor_Source, there are no field names: Country and ROCNumber.
Access introduces no meaningful msg in that case:

Error '3709': The search key not found in any record​

From Josef P. advice, I tried with recordsource, manually run the record source string and now access asked me Country and ROCNumber name....

 

[Josef P.]

... especially, the message comes only when changing the record in the subform and not when filtering.
There are situations where one must not ask why.