Opening Access Form to Last Record in Table

[Merrill B.]

I have a simple macro which does the following:
(1) It opens a table and allows me to enter a record at the end of the table which it automatically saves.
For this example, the record saved in the table is number 100. (This is copied information that creates the new record).
(2) The macro next opens a form that is linked to this table and it is instructed to go to the "last record".
The purpose of this would be to enter additional information coming from a written source.
The problem is this:


(A) The form opens to number "99" which does not correspond to the record number in the table, which is 100.
So I cannot enter the information into the form view which should correspond to record 100 as it stops at 99.
I have to then close the form and reload it and then the form record number indicator shows the last one now as 100 so I can finish my data entry.
(B) If I do not use a macro and enter a record into the same table (#100) and then open the form and go to the last record, it is there "but the macro will not do that".
I note that the form properties are the same with or without the macro and the table has no changes in properties. And I do not wish to develope a query or use language programming so help getting this simple macro to work would be appreciated

[pbaldy]

It sounds like you may need to explicitly save the new record so that the next form will see it. I don't use macros so don't know what that command is, but a way around it would be to go to a new record, which would save the current record automatically.

[Merrill B.]

It sounds like you may need to explicitly save the new record so that the next form will see it. I don't use macros so don't know what that command is, but a way around it would be to go to a new record, which would save the current record automatically.

Thank-you and I have inserted an extra save command before closing the table (records in Access are normally saved automatically when doing this) but there was no difference as the Access Form did not indicate a new record added to the table even though it was there. Why the heck does the form not update to the correct record total after running a simple macro? I then added an extra open form command after closing the preceeding one in the macro and the macro ignored it! And I have run repair of my MS Suite and there is no problem with access. I know a form is not a table but it should reflect what is it it, that is the attached fields and the record counter should match the number of records in the table.

[orange]

Like Paul, I don't use macros. However, if you are assigning record numbers, I think you would want the form to open on the record with the largest/Max ID/number.
You may get some insight from this Youtube video Where Condition macro by Kirt Kershaw

[Merrill B.]

Thank-you and I have inserted an extra save command before closing the table (records in Access are normally saved automatically when doing this) but there was no difference as the Access Form did not indicate a new record added to the table even though it was there. Why the heck does the form not update to the correct record total after running a simple macro? I then added an extra open form command after closing the preceeding one in the macro and the macro ignored it! And I have run repair of my MS Suite and there is no problem with access. I know a form is not a table but it should reflect what is it it, that is the attached fields and the record counter should match the number of records in the table.

Thank-you orange, but I am letting access assign record numbers which means that the last record in the table has the highest number and the form when opened to the last record should also reflect that number but "not" when running a macro as described.

[pbaldy]

Can you attach the db here?

[davegri]

It opens a table and allows me to enter a record at the end of the table which it automatically saves.

I wouldn't do that. It's bad practice to edit tables directly.
Why don't you just open the form and add a new record from there?

[Merrill B.]

Attached is a sample database to demonstrate the problem.
It consists of one table, one form and one macro.
Test one:
Enter a record into the table and then open the form. You will note that the form counter shows the same number of records as in the table.
Test two:
Run the macro (enter a record into the table) which access automatically saves, and then the macro opens the form.
You will note the that form counter indicates one less record than the table. The macro is then finished and the only way to have the form show the correct number of records in the table is to close it (the form) and then reopen!
Challenge:have the form show the correct number of records in the table when using the macro and access that record then!

[Welshgasman]

Nothing attached?

[orange]

Merrill B,
Do you understand what Davegri is suggesting? Best practices would NOT have users interacting directly with Table(s).
User interactions are always via form.
Your question may be a worthwhile academic exercise, but you would probably be better served by working from the form and adding a record as Dave suggested.

[señor_sriracha]

Not having seen the macro, I'm guessing you're running into an async/await problem.
The macro is opening the table, but it is not waiting for you to enter your new data before opening the form/moving to the last record.
AFAIK Access has no real async capabilities.

[Merrill B.]

Well here is my second attempt to attach the database for testing. The three small access files are in zip format.

[señor_sriracha]

Looks like it's as I suspected. There's nothing telling the macro to wait until the form closes before opening the table.
Both items open simultaneously. Then you change the recordset by adding a record, but the form needs a refresh to see it.

[Merrill B.]

Thanks for analytical response and clear response. I note that the macro instructs the table to open first at which point there is an edit (addition of a record) "before" the form opens. If correct then it is not simultaneous occurence and the instruction to open the form (as the lattter instruction), should have the form reflect the updated table. Do you agree and if so, can you offer a tested solution that demonstrates the form showing the same number of records as the table without refeshing manuallyl?

[Welshgasman]

The problem is that the macro just runs the steps?, it is not waiting for you to finish editing the table.
You could try a Refresh or Requery of the form, but I would say STOP what you are doing and do what every other Access user/developer does, and that is use Forms for data entry and learn VBA.

Sadly not many experienced developers use macros (for good reason). You might be better off asking in an MS forum for Access.

This

And I do not wish to develope a query or use language programming so help getting this simple macro to work would be appreciated

tells me sadly that is not going to happen.