subform moving me to end of recordset

[SaviorSix]

I have a form with 3 subforms. They are all correctly linked.

Main form is
MainID (PK auto)
field1
field2
etc

Subform 1:
subform1ID (PK auto)
MainID (FK to mainFormPK 1 to n)
field1
field2
etc

Subform2
subform2ID (PK auto)
MainID (FK to mainFormPK 1 to n)
field1
field2
etc

Basic setup. However, when entering a new record, when I enter one of the subforms from the main form, I am automatically taken to the last record in the recordset?!

The record I entered is saved, but I need to navigate back to the record I was just at in order to enter into the subforms.

WHat could be causing this?

 

[RainLover]

Just for my clarification are you wanting to enter more than one record on the SubForm.

Not sure where you want to go to.

 

[SaviorSix]

The main form is a single form, the subforms are continuous forms, linked to the PK of the main form's control source table, so the links are all correct....but for some reason when I click onto one of the subforms (when entering a NEW record only), it takes me to a different record.

OK...LOL..I am not at work now, but I think I might have realized what it is....I have the main form's record source sorted by one of the fields....I think maybe when I click to the subform, the main form record gets saved, and then re-sorted, therefore changing the current record.....I think

 

[SaviorSix]

Update, THIS IS the cause of the problem, however I do not have a solution.


No matter what, if I enter a new record on the main form, then move to the subform, the record source saves the record and re-sorts, effectively 'moving' me to a different record.

I have tried putting an accmdSaveRecord in the Before Update event of the main form to tru to force the save first, however this returns a run time error 2115.

Any possible ways to solve this?

 

[RainLover]

Do not save the record.

This is not excel it is a Database.

Your record will be saved automatically as soon as you take the focus away from the Main Form.

 

[boblarson]

Do you have a .Requery happening somewhere in the main form code?

Also, just as an explanation - you cannot have an acCmdSaveRecord in a BeforeUpdate event of the form because it is already in the process of being saved, if the BeforeUpdate event has fired.

 

[RainLover]

Bob

Good to see you found work.

Congratulations.

 

[boblarson]

Bob

Good to see you found work.

Congratulations.

Thanks, and I've been meaning to ask - as I noticed your avatar - is there a rain lover in there somewhere?

 

[RainLover]

You got me.

 

[boblarson]

You got me.

Then, welcome to AWF! Glad to have you here. I was wondering about this guy NedKelly as he seemed to be better than the average new poster here.

Glad you have come by.

 

[SaviorSix]

Do not save the record.

This is not excel it is a Database.

Your record will be saved automatically as soon as you take the focus away from the Main Form.

I know this.

The saving is what is causing the problem. When the focus leaves the main form to the subform, thats when the record changes on me. I have to navigate back to the record I was entering to start entry into the subforms

Do you have a .Requery happening somewhere in the main form code?

Also, just as an explanation - you cannot have an acCmdSaveRecord in a BeforeUpdate event of the form because it is already in the process of being saved, if the BeforeUpdate event has fired.

Negative. I took my .requeries out of the main form, I also took out my sorting in the main form. It still happens.

 

[boblarson]

Can you post the database so we can test it? The problem is that there is just so many possibilities that could be happening, it is hard to keep trying to think of them, where if I look, it is a lot easier.