Solved Form to only create record or update record when button is pressed (1 Viewer)

tcneo

Member
Local time
Today, 20:56
Joined
Dec 7, 2021
Messages
68
Hi,

Database as attached. Open Main Menu form. Click on Admin tab. Click on View/Edit Analyst button to open up a Form that shows a datasheet-view of analysts.

My story starts here.

In the above datasheet, clicking on the Edit hyperlink next to an Analyst's name will open up the form, Analyst_Edit, in edit mode. Click on the bottom-most Edit hyperlink where there are no Analyst will open up the form, Analyst_Edit in add-record mode (i created this using a macro embedded on the Click event for the hyperlink. Feel free to critique how I did this. But this is not the focus of this post).

I wanted the Analyst_Edit form to only update record or create a new record when the "Save & Exit" command button is pressed (i set caption of button to "Save & Exit" but it appears as "Save _Exit")

So I found the following post on this forum and used the code suggested by arnelgp. =)

(1) Saving a record only via a save button | Access World Forums (access-programmers.co.uk)

I need help to fix the VBA code. Currently, when I click on the "Save & Exit" button. The record will be updated or created but the form will "go blank" i.e. go to new blank record. How do I have it close the form instead?

Secondly, i noticed that if i make changes to the records and then click on the "Close Form" button, the MsgBox "Do I want to discard changes" will appear. If I click "No". I will get an error from MS Access saying "You can't save a record at this time. Microsoft Access may have encountered an error while trying to save a record.... blah blah blah... Do you want to close the database object anyway? If I click "No" in response, I get an Error 2169.

How can I fix this? I had expected that if I clicked "No" to the MsgBox "Do I want to discard changes", it will just take me back to the form without the changes being discarded.
 

Attachments

  • Test Planner 2022-01-27 SB.accdb
    3.1 MB · Views: 191

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:56
Joined
May 7, 2009
Messages
19,175
if you are asked: "Do I want to discard changes?"
you clicked on Cancel button to go back to your form
for further editing.
 

Attachments

  • Test Planner 2022-01-27 SB.accdb
    3.1 MB · Views: 205

tcneo

Member
Local time
Today, 20:56
Joined
Dec 7, 2021
Messages
68
if you are asked: "Do I want to discard changes?"
you clicked on Cancel button to go back to your form
for further editing.
dear arnelgp,

thanks for showing me the clsTransactedForm class. how did you "apply" it to the form, I presume, you apply it to the Analyst_Edit form.

Also, i note that the record is now saved only if i click the Save & Edit button.

But I noticed the following two observations too:
1) If I make any changes and click on Close Form. The Form simply closes. There is no MsgBox.
2) also, clicking on any Edit hyperlink will always bring up the Analyst_Edit form with the first record (Analyst One, M01) loaded. in other words, I can only edit the first record.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:56
Joined
Feb 19, 2002
Messages
42,981
"Save & Exit" command button is pressed (i set caption of button to "Save & Exit" but it appears as "Save _Exit")
When using certain special characters in strings, you need to double them.

Save && Exit

There are easier ways than class modules but I'm not going to interfere if you are happy with using the class.
 

tcneo

Member
Local time
Today, 20:56
Joined
Dec 7, 2021
Messages
68
When using certain special characters in strings, you need to double them.

Save && Exit

There are easier ways than class modules but I'm not going to interfere if you are happy with using the class.
Thanks for the information about special characters.

Please do explain how to implement the above without using class modules. Cos I have yet to learn about using class modules in VBA. Thanks!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:56
Joined
Feb 19, 2002
Messages
42,981
See if you can work with this
 

Users who are viewing this thread

Top Bottom