The ms access adatabase engine cannot find a record in the table "" with key matching

[Abouya]

The ms access adatabase engine cannot find a record in the table "" with key matching

Hello,

I've been trying to figure out how I can fix this issue in my form, This error happens sometimes if i leave GlAccount field empty. any help will be much appreciated.

Thanks

 

[Minty]

Re: The ms access adatabase engine cannot find a record in the table "" with key matc

You have it set up that you must have an entry in GLAccount, possibly in your relationships to enforce referential integrity. If it's not always required - you set the rules - you can change them

 

[Abouya]

Re: The ms access adatabase engine cannot find a record in the table "" with key matc

If that's a field that used in the link field between the main form and subform then it can't be left blank. You could test this in the forms beforeupdate event to give the user a friendlier message.

Thanks for reaching out. Gl Account field is part of the main form called [frmlRequisitionOrder]. If i fill everything in the main form and leave Gl Account field empty, then jump to the subform/Query that start with 'line' then i get this message: The microsoft access database engine cannot find a record in the table 'tblGLAccounts' with key matching field(s) 'fk_GLAccounts'.

 

[Abouya]

Re: The ms access adatabase engine cannot find a record in the table "" with key matc

You have it set up that you must have an entry in GLAccount, possibly in your relationships to enforce referential integrity. If it's not always required - you set the rules - you can change them

Thanks for reaching out. Gl Account field is part of the main form called [frmlRequisitionOrder]. If i fill everything in the main form and leave Gl Account field empty, then jump to the subform/Query that start with 'line' then i get this message: The microsoft access database engine cannot find a record in the table 'tblGLAccounts' with key matching field(s) 'fk_GLAccounts'.

I do have enforce referential entegrity in all the relationships, no other fields give me such error. Gl Account is the only one giving this eeror. i can't understand why?!!

 

[Abouya]

Re: The ms access adatabase engine cannot find a record in the table "" with key matc

You have it set up that you must have an entry in GLAccount, possibly in your relationships to enforce referential integrity. If it's not always required - you set the rules - you can change them

i took the referential integrity from GL account table but still have the same error. it happens if i leave Gl account blank and jump to the Query/subform to enter details!

 

[Minty]

Re: The ms access adatabase engine cannot find a record in the table "" with key matc

If GL account is the required in the sub form as well, then you will still have the same problem. Disable the subform if the required fields aren't complete.

 

[sneuberg]

Re: The ms access adatabase engine cannot find a record in the table "" with key matc

I do have enforce referential entegrity in all the relationships, no other fields give me such error. Gl Account is the only one giving this eeror. i can't understand why?!!

Sorry I didn't look at the relationships before. The difference between this field and the others is that it is a foreign key. It's has to be populated with a value from the parent table. Can give this combo box a default value that's one of the values in the tblGlAccounts table?

 

[Abouya]

Re: The ms access adatabase engine cannot find a record in the table "" with key matc

If GL account is the required in the sub form as well, then you will still have the same problem. Disable the subform if the required fields aren't complete.

Gl Account field has nothing to do with the subform. GL account is part of the main form. here is screenshot of the subform:

 

[Abouya]

Re: The ms access adatabase engine cannot find a record in the table "" with key matc

Sorry I didn't look at the relationships before. The difference between this field and the others is that it is a foreign key. It's has to be populated with a value from the parent table. Can give this combo box a default value that's one of the values in the tblGlAccounts table?

Please, how i can do this? I really appreciate your help.

 

[sneuberg]

Re: The ms access adatabase engine cannot find a record in the table "" with key matc

Sorry again. What I stated before is incorrect. You should be able to leave that field blank if it isn't specified as being required. I suggest checking the tblRequisitionOrder table and see if the Required property of that field is set to Yes.

 

[Abouya]

Re: The ms access adatabase engine cannot find a record in the table "" with key matc

Sorry again. What I stated before is incorrect. You should be able to leave that field blank if it isn't specified as being required. I suggest checking the tblRequisitionOrder table and see if the Required property of that field is set to Yes.

I changed the gl account required to Yes in tblRequisitionOrder and now the subform shows older data as soon as i start filling the requestor name and date (or when a new requisition ID is assigned)!

Very weird to see this happened. can i send you the database to have a look please. this issue is :banghead:

 

[sneuberg]

Re: The ms access adatabase engine cannot find a record in the table "" with key matc

I meant for you to change it to No if it was Yes. Anyway, Yes please upload the database.

 

[Abouya]

Re: The ms access adatabase engine cannot find a record in the table "" with key matc

I meant for you to change it to No if it was Yes. Anyway, Yes please upload the database.

i did revert it to Required: no , but still see the same issue. I really can't thank you enough for your help.

 

[Abouya]

Re: The ms access adatabase engine cannot find a record in the table "" with key matc

I meant for you to change it to No if it was Yes. Anyway, Yes please upload the database.

I hope it's nothing serious with the database.

 

[sneuberg]

Re: The ms access adatabase engine cannot find a record in the table "" with key matc

I hope it's nothing serious with the database.

Probably not but the problem isn't obvious to me. This make take some time to figure out. I'll get you an answer as soon as I can.

 

[Abouya]

Re: The ms access adatabase engine cannot find a record in the table "" with key matc

Probably not but the problem isn't obvious to me. This make take some time to figure out. I'll get you an answer as soon as I can.

Thank you so much for helping me out.

 

[sneuberg]

Re: The ms access adatabase engine cannot find a record in the table "" with key matc

This problem is being caused by the tblGLAccounts table being part of the record source of the frmlRequisitionOrder form. I don't see any reason why that table should be in the record source. I certainly didn't expect to find it there and that's why it took me so long to figure this out. The general rule is one table per form and for this form that would be tblRequisitionOrder. I'm pretty sure if you remove tblGLAccounts from the record source you won't miss it.

 

[Abouya]

Re: The ms access adatabase engine cannot find a record in the table "" with key matc

This problem is being caused by the tblGLAccounts table being part of the record source of the frmlRequisitionOrder form. I don't see any reason why that table should be in the record source. I certainly didn't expect to find it there and that's why it took me so long to figure this out. The general rule is one table per form and for this form that would be tblRequisitionOrder. I'm pretty sure if you remove tblGLAccounts from the record source you won't miss it.

Thanks a lot. Please advise what to do in steps, I'm less than a beginner when it come to access.

 

[Abouya]

Re: The ms access adatabase engine cannot find a record in the table "" with key matc

Thanks a lot. Please advise what to do in steps, I'm less than a beginner when it come to access.

i used a combobox to get the Gl account field to frmlRequisitionOrder form. do you refer to change the Gl account field combobox source?

 

[sneuberg]

Re: The ms access adatabase engine cannot find a record in the table "" with key matc

Thanks a lot. Please advise what to do in steps, I'm less than a beginner when it come to access.

I guess this wasn't your creation then. To remove the table from the form's record source:

1. Open the form in design view.
2. Right click on the form and select Form Properties
3. In the DATA tab of the Properties Sheet click on the ellipsis (...) on the far right of Record Source.
4. Right click on the table (tblGLAccounts) and select Remove