Form: Disable Auto Save and Enable at Save Record (slight issue)

[squeakums]

Hello there, so in access I have borrowed this code that I found to disable auto save in access form and enable at the end. It works, except, it doesn't allow my form to update the lookup fields attached:

Private mIsUserUpdate As Boolean 'Flag

'Cancel Auto Save
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not mIsUserUpdate Then Cancel = True
End Sub

'Manual Save
Private Sub YourButtonName_Click()

'...
'Do work
'...

mIsUserUpdate = True 'OK to save
DoCmd.RunCommand acCmdSaveRecord
mIsUserUpdate = False 'Revert
End Sub


Do you guys know a work around to where that can update as well? I have tried several codes for the same process and all are the same issue...

 

[Micron]

I've never seen anything like that in some 15+ years of coding. So you don't want the form BeforeUpdate event to save the record. I can buy that I guess, but rather than allow it from a button click, why not just save the record if clicked?

Private Sub YourButtonName_Click()

If Me.Dirty Then Me.Dirty = False
End Sub

Please use code tags for more than a few lines of code (vba button on posting toolbar) to maintain indentation and make code easier to read.

 

[squeakums]

I've never seen anything like that in some 15+ years of coding. So you don't want the form BeforeUpdate event to save the record. I can buy that I guess, but rather than allow it from a button click, why not just save the record if clicked?

Private Sub YourButtonName_Click()

If Me.Dirty Then Me.Dirty = False
End Sub

Please use code tags for more than a few lines of code (vba button on posting toolbar) to maintain indentation and make code easier to read.

I've set this up for my save button. So, it won't save prior to the button being clicked using this code correct? I'm not sure what the Dirty function does lol

 

[squeakums]

Actually, the code you sent doesn't work, unfortunatly the form is still autosaving before I hit save. I don't want it to do this because I have a workflow setup in sharepoint where it starts sending emails etc. I don't want it to save until I hit the save button. How do I work around that? Although, I do want to make sure it still runs the requery for the other lookup fields prior to save.

 

[Micron]

I think that I overlooked something because it didn't make sense to me, as lookup fields are specific to tables, not forms (and generally should be avoided). What does this mean?

it doesn't allow my form to update the lookup fields attached:
Do you guys know a work around to where that can update as well?

What are you describing as "lookup fields"? Maybe this

Create or delete a lookup field

In this article, learn to create lookup fields (columns), modify lookup field properties, and remove lookup fields.

support.microsoft.com

The fields that are not updating are bound fields?

 

[Joe4]

What I have seen some people to is create an Unbound Form strictly for data entry (since it is not connected to any table, it will not update anything).
And there is a Save button at the bottom of it, that when they click it, runs VBA code that writes the values from that data entry form to the desired table, all at once.

 

[squeakums]

What I have seen some people to is create an Unbound Form strictly for data entry (since it is not connected to any table, it will not update anything).
And there is a Save button at the bottom of it, that when they click it, runs VBA code that writes the values from that data entry form to the desired table, all at once.

Right, it is like data entry form. For example, a business is selected and it brings over the manager name, business name, and it has other submittion fields besides these. Once submitted it saved on SharePoint and a process flow is setup that the managers need to abide by (emails are sent to them etc). The form autoloads these fields once the primary identified is selected which is unique like the number assigned to this particular business. My ultimate goal is to make sure it does not save until all fields are selected (status) field is a list field that is selected. I want this to save after all fields are entered verses automsaving as each field is entered (really annoying). Does anyone know the code for this? I found some code like posted above but it has drawbacks. For example, it turns off save when form is opened but then doesn't allow you to edit the form or the lookup fields won't process (even when I add a requery) it actually wipes out the data.. so weird..

 

[Joe4]

Right, it is like data entry form. For example, a business is selected and it brings over the manager name, business name, and it has other submittion fields besides these. Once submitted it saved on SharePoint and a process flow is setup that the managers need to abide by (emails are sent to them etc). The form autoloads these fields once the primary identified is selected which is unique like the number assigned to this particular business. My ultimate goal is to make sure it does not save until all fields are selected (status) field is a list field that is selected. I want this to save after all fields are entered verses automsaving as each field is entered (really annoying). Does anyone know the code for this? I found some code like posted above but it has drawbacks. For example, it turns off save when form is opened but then doesn't allow you to edit the form or the lookup fields won't process (even when I add a requery) it actually wipes out the data.. so weird..

The difference is it sounds like your form is a Bound form (meaning it is connected to an underlying table, which is why it updates immediately automatically).
I am talking about using an Unbound form (meaning it is NOT connected to an underlying table, so there is nothing to update right away).

You can still have certain fields pulling from other tables to populate certain fields on the form.
When you are completed entering all the necessary data in to the form, you can then have the VBA code write all the fields to whatever table you like all at once, so you control when that update/save happens. Note that this method will be very heavy in VBA code.

I see that you have been asking lots of Access questions lately. You seem to be using it extensively, so I highly recommend picking up some books or using some online tutorials to increase your knowledge in Access, especially as it relates to VBA. It will be very beneficial to you in the long run, especially since while we can help with specific questions, we really cannot teach you everything you need to know in a question/answer forum format.

 

[Micron]

Maybe easier to loop over form controls and validate that all required fields have data and if not, cancel the form update event? Less code?
That would capture moving off of any partial record no matter how the user does that. I'm not seeing the point in trying to prevent incomplete records by disabling and enabling a form event with a command button.