Access: How to open a second form to the same record as the first?

Question

This should be so straightforward but it keeps opening the second form as a new record for some reason! I haven't created a database for years and have had two children since then so please go easy on my jelly brain!

This is a accident reporting database which we want to run through each section of questions in a new window each time, much like a wizard. The first section asks if you want to report a new incident or the other navigation options, the command for reporting a new incident was fine, macro builder, openform, form name: About the impacted person 02, set data mode to "add" to go into a new record each time. All good so far.

A form ID autonumber is generated as you fill in name, address etc and then there's a button to click for the next section. I assumed that since the ID was already filled in and that record had been created in the table that if I set up the command button to open form and filter based on the ID then it would appear in the next form (the ID field is visible in the top corner of every form), but for some reason it isn't filtering, just bringing up a new record each time in each section. Each section is saving independently as it's own record.

I've tried filtering through the macro builder (setting up the data mode to "edit" for these subsequent forms) and I've tried following the command button wizard and linking the two, the relationships are there, the first form is still open at this time as I thought that maybe if I set up the macro to close it as the other one opened then it wouldn't have the data to lookup? Clutching at straws now to try and get it to work but I've done this kind of thing so many times and never had this problem!!

Any help or suggestions would be massively appreciated, thank you!

Answer 1

The fact that the auto number is created does NOT mean the record been created nor does it mean the record been saved. If the user’s hits undo and exits, then the data is never saved and in fact the auto number is skipped and never used. This explains why you can often see gaps in the auto number when no deleting has occurred.

The simple solution is before you launch the other/next form, is to ensure that the data in the current form is written to the table. The following code will achieve this goal:

If me.dirty = true then me.dirty = false

Docmd.OpenForm "Form2",,,"id = " & me!id

So the first line forces a record save, and the second line of code will launch the second form to the same current record we are on.

Answer 2

I think I've got there... after lots of frustration!! If anybody else is searching for a resolution to this issue, Albert D. Kallal was right, it WAS saving the data, but it was opening the new form first, then when the previous form was closed it was saving the data so that it appeared in my table but didn't exist yet to be pulled through to the next form. Simple fix, changed the order of the commands on the macro. I expect the code he suggested would have worked too if it wasn't in the hands of someone with a serious case of baby brain...