Adding new entries to a manually created split form (1 Viewer)

[Kronix]

I have a form where I can enter data for a single record. I also have a subform on that form that shows a datasheet of all the records. I've created VBA code that makes the datasheet highlight the selected record in the main form, or changes the record in the main form when a record in the datasheet is clicked.

I run into a problem when adding records, though. I made the datasheet a snapshot so read only with no field for a new record. When I create a new record in the single (main) form I want it to show up in the datasheet after it is saved. This works if I save the record by clicking the record marker in the single form. However, when I click into the datasheet it should automatically update the record from the single form into the list on the datasheet subform, but still move to the other record I clicked on in the datasheet. When I do a requery of the datasheet in VBA to show the record, Access seems to lose track of which record I clicked on and moves to a random record that I did not click on. I tried it in both the AfterUpdate event for the Form and OnClick event for the cells of the datasheet

Anybody know if it's possible to fix this, or a tutorial of how to make a subform operate exactly like a split form? Also whether the highlighting of a selected line in the datasheet can be disabled when adding a new record in the single form? Currently it stays on the last record in the datasheet that was selected before opening a new record.

 

[isladogs]

Have a look at the emulated split form in this thread.
https://www.access-programmers.co.uk/forums/showthread.php?t=294421

It went through lots of iterations.
The last version by static is the best of the lot

 

[Pat Hartman]

@Kronix,
Requery the DS form in the AfterUpdate event of the single form.

@Colin,
Great example. Thanks

 

[Kronix]

@Kronix,
Requery the DS form in the AfterUpdate event of the single form.

@Colin,
Great example. Thanks

"When I do a requery of the datasheet in VBA to show the record, Access seems to lose track of which record I clicked on and moves to a random record that I did not click on. I tried it in both the AfterUpdate event for the Form and OnClick event for the cells of the datasheet"

I have found that the On Click event happens after the requery event. I have not found a way to know which record the user clicked on. Keep in mind this is when clicking after a new record is created. I need to do all of the following things: Create the record, update the subform list, and go to the record clicked on.

The only logic I have found is to always requery the list when a record is clicked on, regardless of whether a record was added. This indeed will both add the new record to the list and go to the record clicked on. However, because of the requery the list jumps and the record clicked on always becomes the second to last shown in the space of the list. This is because the findfirst function of the recordset moves the whole list to center found record in the same place in the subform. I suppose where it is centered is something built into Access. In any case, this is jarring and confusing to the user. In the meantime I have just made the record always jump to the record added in AfterUpdate regardless of what was clicked on.

 

[isladogs]

Sounds like a bit of a pig's ear as we say in the UK

Suggest you at least look at the emulated split form I mentioned.
It works!