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

[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.

A side-question,
In your modified code:

Private Sub User_MainProductShowDetail()

    Dim rs As DAO.Recordset
    
    Set rs = Me.Form.Parent.Form.Recordset
    rs.FindFirst "[VendorID] = " & Txt_ID
    If rs.NoMatch Then MsgBox "Unknow Error - No Vendor Name " & Txt_QField1 & " !"
    
End Sub

How did you nevigate main form to current record of subform?
I thought we have to use bookmark technique such as

Me.Form.Parent.Form.Bookmark = .Bookmark

 

[MajP]

In the old days a form did not directly expose the recordset you had to use the recordsetclone. Also there are cases where you want to move within the recordset and not move the form. Example, you want to check the value of the prior or following record. If you work with the clone, you move in the clone without moving the actual recordset. Find the corresponding bookmark and set the recordset to the bookmark. But if you are going to move the recordset anyways just move the recordset. If the recordset cannot find the record it stays in current location.

 

[isladogs]

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

This is a normally clear indication of corruption - usually in one of the system tables.
Once you have recovered from the issue, I strongly recommend you make a backup, then decompile, compact and recompile.

Good luck

 

[gemma-the-husky]

Similar to @isladogs . I have always associated that error with a corruption. As there's an error code, I assume it's legitimate in some cases, but in general for me it's a result of a corruption, maybe with Chinese characters in corrupt records.

Sometimes you can struggle to remove the corruption, as any query using the corrupt record will fail. I have been able to rebuild the table in question by working around the PK of the suspect record, and then had to delete RIs to link in the new table.

Always take copies of your database before attempting to fix, as it could become completely unusable.

 

[Josef P.]

The error can be reproduced.

Condition: Use Subform.Filter and thereby insert a data field in the filter that does not exist in the subform but does exist in the main form. => So there is no error message when filtering, but with every record change in the main form the data source of the subform is updated. With this the dataset pointer always jumps to the 1st position in the subform.

 

[Babycat]

Similar to @isladogs . I have always associated that error with a corruption. As there's an error code, I assume it's legitimate in some cases, but in general for me it's a result of a corruption, maybe with Chinese characters in corrupt records.

Sometimes you can struggle to remove the corruption, as any query using the corrupt record will fail. I have been able to rebuild the table in question by working around the PK of the suspect record, and then had to delete RIs to link in the new table.

Always take copies of your database before attempting to fix, as it could become completely unusable.

Yeah, as no-experience access user, I am always scared this scenerio, code error cause forms/local tables coruption without knowing.
I am not so clear what difference btw compact and decompile. I usually do Cmpact & Close before copy my accdb as a backup.
Let me learn to use decompile as Isladog's suggestion with hope unknow corruptions can be minimal and recovered

 

[Babycat]

The error can be reproduced.

Condition: Use Subform.Filter and thereby insert a data field in the filter that does not exist in the subform but does exist in the main form. => So there is no error message when filtering, but with every record change in the main form the data source of the subform is updated. With this the dataset pointer always jumps to the 1st position in the subform.

yeah, it's easy to reprocuded it. Ms Access does not handle this error properly i think

 

[Gasman]

yeah, it's easy to reprocuded it. Ms Access does not handle this error properly i think

Why do you consider it an error on the part of Access and not the developer?

 

[isladogs]

SELECT TBLVENDOR.*, IIf(Not [TypeCustomer],"Supplier",IIf(Not [TypeSupplier],"Customer","Both")) AS Type
FROM TBLVENDOR
ORDER BY VShortName, VFullName;

The error can be reproduced.

Condition: Use Subform.Filter and thereby insert a data field in the filter that does not exist in the subform but does exist in the main form. => So there is no error message when filtering, but with every record change in the main form the data source of the subform is updated. With this the dataset pointer always jumps to the 1st position in the subform.

Agreed.
Personally I would use an unbound main form containing the search box and two subforms (as already suggested in an earlier post)
However, it also works without error if you include all fields in the query:

SELECT TBLVENDOR.*, IIf(Not [TypeCustomer],"Supplier",IIf(Not [TypeSupplier],"Customer","Both")) AS Type
FROM TBLVENDOR
ORDER BY VShortName, VFullName;

 

[Babycat]

Why do you consider it an error on the part of Access and not the developer?

I did not say it is Access's error.
Post #20, @Josef P. has mentioned it too, developer makes a wrong SQL on filter string, however Access does not notice error when applying filter but it only noitice when we change current record on subform. It makes we not think the problem comes from filtering code.

 

[Babycat]

SELECT TBLVENDOR.*, IIf(Not [TypeCustomer],"Supplier",IIf(Not [TypeSupplier],"Customer","Both")) AS Type
FROM TBLVENDOR
ORDER BY VShortName, VFullName;

Yeah, I already changed my query like that, so there is no potential issue if we add new field on TBLVENDOR in future. Howeverm abit concern, if a table on network db has many fields, getting TBLVENDOR.* might slow down performance while i just need few fields. Right?

In fact, my project has unbound main and many subform too. But I dont really see its benifit comparing to bound-main (which shows record detail) and subform (show records summary),
May you please insight me?

 

[Gasman]

Yeah, I already changed my query like that, so there is no potential issue if we add new field on TBLVENDOR in future. Howeverm abit concern, if a table on network db has many fields, getting TBLVENDOR.* might slow down performance while i just need few fields. Right?

In fact, my project has unbound main and many subform too. But I dont really see its benifit comparing to bound-main (which shows record detail) and subform (show records summary),
May you please insight me?

Surely this error is just because YOU missed two fields off?. If you get the fields correct, it would not happen?, so you can still remain with only those fields required.

 

[Josef P.]

However, it also works without error if you include all fields in the query

The resulting error message doesn't bother me that much. I am much more bothered by the fact that there is no (error) message when using subformControl.Form.Filter if you mistakenly include a data field from the main form in the filter expression.

If you use a data field name that does not exist either in the subform or in the main form, you will be prompted for the value of the unknown "parameter", as expected.

 

[MajP]

I think some of these issues may be due to different language versions of Access. I see this in the table for the field description
Mã số thuế/số ĐKKD
Was this developed in a Vietnamese version of Access?
Sometimes on this forum when users post certain language versions (especially Arabic ones) I get errors and behavior that I cannot fix or explain. I am seeing behavior that makes no sense and I do not think it is related to the code.

 

[isladogs]

Yeah, I already changed my query like that, so there is no potential issue if we add new field on TBLVENDOR in future. Howeverm abit concern, if a table on network db has many fields, getting TBLVENDOR.* might slow down performance while i just need few fields. Right?

In fact, my project has unbound main and many subform too. But I dont really see its benifit comparing to bound-main (which shows record detail) and subform (show records summary),
May you please insight me?

1. Yes it would impact performance over a network if you have many fields in your table. However, a large number of fields often indicates design issues.
2. If you have a unbound form with 2 subforms, I think the error will no longer arise even without using all the table fields. If so, performance should also be better. However, you would have to redo your code

 

[MajP]

2. If you have a unbound form with 2 subforms, I think the error will no longer arise even without using all the table fields. If so, performance should also be better. However, you would have to redo your code

FYI, I attempted 2 unbound subforms and still got weird behavior and errors even with my own code. I still think there is more going on.

 

[Josef P.]

Attached is an example that shows the behavior of using a control from the main form in the filter string of the subform.

The behavior was unexpected for me. On the other hand it fits to the behavior of e.g. a combobox, where you can also use control names of the form in the data source of the combobox. => unexpected form me, but not a bug.

 

[MajP]

I fixed my problems. I am convinced that this DB is corrupted. Look at the PK and it is all over the map. Sure sign of corruption. I removed the PK compact and reparited and added a new PK. Works fine.

This is likely corruption since that VendorID is an autonumber. Unless this is a subset of records from something bigger.
Here is a working version. I left as much as your code but did go with the second subform ID. I believe the corrupted PK masked other problems.

TBLVENDOR TBLVENDOR

VendorID	VShortName
233855199​	BMT (R&P)
819708067​	AMEYA
1064521384​	Arrow
1069579518​	Company ABC
1444050198​	Avnet

I did go back and check to see if the PK increment was set to random and in fact it was. So maybe it was OK. However, I have seen where the increment becomes random through corruption where originally not set to random.

@Babycat, did you purposely set the vendorID to random? If not they may confirm my suspicions of corruption. I have almost never seen someone do this on purpose unless building their own replication.

 

[Babycat]

I fixed my problems. I am convinced that this DB is corrupted. Look at the PK and it is all over the map. Sure sign of corruption. I removed the PK compact and reparited and added a new PK. Works fine.

This is likely corruption since that VendorID is an autonumber. Unless this is a subset of records from something bigger.
Here is a working version. I left as much as your code but did go with the second subform ID. I believe the corrupted PK masked other problems.

TBLVENDOR TBLVENDOR

VendorID	VShortName
233855199​	BMT (R&P)
819708067​	AMEYA
1064521384​	Arrow
1069579518​	Company ABC
1444050198​	Avnet

I did go back and check to see if the PK increment was set to random and in fact it was. So maybe it was OK. However, I have seen where the increment becomes random through corruption where originally not set to random.

@Babycat, did you purposely set the vendorID to random? If not they may confirm my suspicions of corruption. I have almost never seen someone do this on purpose unless building their own replication.

Hi Majp.

Thank for your valuable time of debugging.
At first, the problem is solved. We found the issue on post #19

Yeah, my vendorID is random number, I dont want increament number as if someone see that they can estimate how many vendor/customer I have in system.

The local language characters is not related to the issue in this case.
The issue is about missing field name in filter expression but Access does not notice us properly...

 

[Babycat]

The resulting error message doesn't bother me that much. I am much more bothered by the fact that there is no (error) message when using subformControl.Form.Filter if you mistakenly include a data field from the main form in the filter expression.

If you use a data field name that does not exist either in the subform or in the main form, you will be prompted for the value of the unknown "parameter", as expected.

Yeah, you're right.
We were on wrong debugging direction with that given weird error msg.