Error 3022 - Duplicate Key/Index

[KjWhal]

Just when I thought I had my problem licked...

I created a subforum with an updateable query, and this works. I can view the results of the query, modify them and add new records.

However I shortly realized that once I deleted a record (either by some VBA code, or by SQL) that I could not re-add the same record through the subform.

I've made sure that the record is deleted, so there is no duplication of the key.
I've made sure that Index is Duplicates OK, so there is no duplication issues there.

What am I missing?

 

[RuralGuy]

Have you Requeried the SubForm to get a fresh RecordSet?

 

[KjWhal]

Have you Requeried the SubForm to get a fresh RecordSet?

I tried requering it but it says it can't find my subform.

This is the proper way to do it correct?

Forms!frmWorkshop!frmSubWorkshop.Requery

I'm starting to think something in my query got hosed, now I can add things on the subform but they're never put in the table (But existing records can be modified.)

 

[RuralGuy]

The syntax is:
FORMS!MainFormName!SubFormControlName.FORM.REQUERY
If Your MainForm is named frmWorkshop
and your SubFormControl is named frmSubWorkshop
then this should work
Forms!frmWorkshop!frmSubWorkshop.FORM.Requery
...remember that forms are displayed on other form by means of a SubFormControl and that SubFormControl need *not* have the same same as the form it is displaying.

 

[KjWhal]

The syntax is:
FORMS!MainFormName!SubFormControlName.FORM.REQUERY
If Your MainForm is named frmWorkshop
and your SubFormControl is named frmSubWorkshop
then this should work
Forms!frmWorkshop!frmSubWorkshop.FORM.Requery
...remember that forms are displayed on other form by means of a SubFormControl and that SubFormControl need *not* have the same same as the form it is displaying.

Thanks! That however didn't solve my original issue, which I think something in my query broke, and all I did was copy and paste the sucker. Any suggestions on running that down?

I can add records manually, but when I attempt to add them on the subform they don't save to the table, and if I try and enter something that has been deleted from the table (IE enetered before when this worked), I get the Error3022 message.

 

[RuralGuy]

Is your SubForm bound to a query or a table? If a query then can you open the query directly and make any changes? If not then it is a NonUpdateable query.

 

[KjWhal]

Is your SubForm bound to a query or a table? If a query then can you open the query directly and make any changes? If not then it is a NonUpdateable query.

It's a query.

I can open the query directly and make changes and additions and deletions.

 

[RuralGuy]

When weird things start to happen, the first thing I do is create a new, blank mdb and import everything from the old db into the new one and then try it. It only takes a few seconds and can eliminate some corruption.

 

[KjWhal]

When weird things start to happen, the first thing I do is create a new, blank mdb and import everything from the old db into the new one and then try it. It only takes a few seconds and can eliminate some corruption.

That didn't do the trick specifically but I finally caught on.

I made the assumption that the query critieria were being fed into each new record, and it wasn't. So while I was looking for records that had x,y,z the records being created had x,null,null,null.

 

[RuralGuy]

Outstanding! Thanks for posting back with your success.