Incomplete record saving message

wrweaver · Mar 5, 2013

Prevent incomplete record saving

I'm trying to prevent my database from saving incomplete records when users don't complete certain fields on the form. I put this code in the before update event but it still saves incomplete records. What am I missing? Thanks for the help!

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.SectorCombo) And IsNull(Me.StationCombo) And IsNull(Me.BuildingCombo) And IsNull(Me.Description) And IsNull(Me.Requestor) And IsNull(Me.RequestorPhone) Then
Me.Dirty = False

End If

End Sub

pbaldy · Mar 5, 2013

Re: Prevent incomplete record saving

You're missing the Cancel = True:

http://www.baldyweb.com/BeforeUpdate.htm

Pat Hartman · Mar 5, 2013

Re: Prevent incomplete record saving

I know it is counter intuitive but
Me.Dirty = False
Actually SAVES the record. It doesn't discard changes. This is the primary reason I don't use this trick in my own code. It is recommened by several people as an alternative to DoCmd.RunCommand acSaveRecord. But, the problem they refer to (an error that arises when you run the code from outside the current form so the active form is not the form running the code or something like that) is extremely rare and in all my years with Access I have never encountered it so until I do, I'll stick with the more obvious solution.

Code:

If IsNull(Me.SectorCombo) And IsNull(Me.StationCombo) And IsNull(Me.BuildingCombo) And IsNull(Me.Description) And IsNull(Me.Requestor) And IsNull(Me.RequestorPhone) Then
    Cancel = True
    Exit Sub
End If

Note also that your code requires ALL the referenced fields to be null before it stops the save rather than ANY field. Is that really what you want?

Also, you are already in the save process if your code is in the Form's BeforeUpdate event so forcing a save at this point would technically put Access into a loop so don't attempt it.

mdlueck · Mar 5, 2013

Re: Prevent incomplete record saving

wrweaver said:
I'm trying to prevent my database from saving incomplete records when users don't complete certain fields on the form.

So is this form bound to the database table?

Rather than have to load up event code on multiple fields, you could make the form into an unbound form, have a Save button, have that button first scan through all of the fields validating that the values are all appropriate, then if all is in order, plug the field values into one SQL statement sending ALL of the field to the database table at one time.

Here is a post which would be helpful for such code:

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Pat Hartman · Mar 6, 2013

Re: Prevent incomplete record saving

I guess I'm missing something here. How could an unbound form possibly be less code than a bound one? With a bound form, Access does all the heavy lifting. With an unbound form, you have to code everything yourself including populating all the controls and scrolling the recordset if it can contain multiple records.

All you need to understand to get control of bound forms is the importance of the Form's BeforeUpdate event.

mdlueck · Mar 6, 2013

Re: Prevent incomplete record saving

Pat Hartman said:
I guess I'm missing something here. How could an unbound form possibly be less code than a bound one?

I saw no requirement stated seeking the "least lines of code" solution.

wrweaver · Mar 7, 2013

Re: Prevent incomplete record saving

The form is bound to a database table and @ Pat, thanks for pointing out the AND, I chose to go with Or. Where do I put the function if not in the Before Event? And I'm still getting the warning telling me if I can't save the record at this time do i want to close anyway. I just want them to be able to close the window or the database and for the database to discard any changes to the form.

wrweaver · Mar 7, 2013

I'm trying to prevent incomplete record saving so I entered this code in the before event of the form to prevent the record from saving if any of those fields are blank:

If IsNull(Me.SectorCombo) Or IsNull(Me.StationCombo) Or IsNull(Me.BuildingCombo) Or IsNull(Me.Description) Or IsNull(Me.Requestor) Or IsNull(Me.RequestorPhone) Then
Cancel = True
Exit Sub
End If

But when I close the incomplete filled form or the database, I'm still getting the warning telling me if I can't save the record at this time do i want to close anyway. I just want them to be able to close the window or the database and for the database to discard any changes to the form and not save the oncomplete record.

Any ideas are welcome! Thanks!

boblarson · Mar 7, 2013

You need to UNDO anything that occurred before exiting. So, add this part to the code just after the Cancel = True part:

Me.Undo

wrweaver · Mar 7, 2013

I still get the ms access message "you can't save this record at this time. blahblahblah. do you want to close anway."

pbaldy · Mar 7, 2013

Re: Prevent incomplete record saving

Apparently continued here:

http://www.access-programmers.co.uk/forums/showthread.php?t=243328

boblarson · Mar 7, 2013

wrweaver said:
I still get the ms access message "you can't save this record at this time. blahblahblah. do you want to close anway."

Are you absolutely sure this is in the FORM'S before update event and not just a control's before update event?

boblarson · Mar 7, 2013

Also, a control may not be null but not have anything in it (i.e. Empty String), so you should do your check like this:

If Len(Me.SectorCombo & vbNullString) = 0 Or Len(Me.StationCombo& vbNullString) = 0 Or Len(Me.BuildingCombo& vbNullString) = 0 Or Len(Me.Description& vbNullString) = 0 Or Len(Me.Requestor& vbNullString) = 0 Or Len(Me.RequestorPhone& vbNullString) = 0 Then

wrweaver · Mar 7, 2013

I'm absolutely positive. The code is in the before update of the form. And everytime there's a partial record and you try to close the form or the database the ms reminder pops up.

wrweaver · Mar 7, 2013

And I tried plugging in that code and it gives me the compile error: expected: )

boblarson · Mar 7, 2013

wrweaver said:
And I tried plugging in that code and it gives me the compile error: expected: )

Sorry, it didn't paste in to the forum from Word too well. It should be:

Code:

If Len(Me.SectorCombo & vbNullString) = 0 Or Len(Me.StationCombo & vbNullString) = 0 _
Or Len(Me.BuildingCombo & vbNullString) = 0 Or Len(Me.Description & vbNullString) = 0 _
Or Len(Me.Requestor & vbNullString) = 0 Or Len(Me.RequestorPhone & vbNullString) = 0 Then

wrweaver · Mar 7, 2013

MS Access You can't save record at this time...still! Is it possible that the code belongs somewhere other than the before event?

wrweaver · Mar 7, 2013

Re: Prevent incomplete record saving

And still haven't found a solution...

boblarson · Mar 7, 2013

wrweaver said:
MS Access You can't save record at this time...still! Is it possible that the code belongs somewhere other than the before event?

Nope, but can you post a copy of the database with fake data so we can take a look? If you do, post which form to look at and how to replicate the error.

wrweaver · Mar 11, 2013

Prevent automatic ms access message when closing unsaved form

Does anyone know how to prevent the ms access message "You can't save this record at this time. Microsoft Access may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close anyway?" The message pops up everytime the user tries to close an incomplete form. I have all fields set to required = no and the before update event is:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Len(Me.SectorCombo & vbNullString) = 0 Or Len(Me.StationCombo & vbNullString) = 0 _
Or Len(Me.BuildingCombo & vbNullString) = 0 Or Len(Me.Description & vbNullString) = 0 _
Or Len(Me.Requestor & vbNullString) = 0 Or Len(Me.RequestorPhone & vbNullString) = 0 Then

MsgBox "You are closing the Work Order Form without submitting; the form will be cleared."
Cancel = True
Exit Sub
End If

If mSaved = False Then
Cancel = True
Me.Undo
Cancel = False
End If

Incomplete record saving message

Registered User.

Wino Moderator

Super Moderator

Sr. Application Developer

Super Moderator

Sr. Application Developer

Registered User.

Registered User.

Smeghead

Registered User.

Wino Moderator

Smeghead

Smeghead

Registered User.

Registered User.

Smeghead

Registered User.

Registered User.

Smeghead

Registered User.

Similar threads

Users who are viewing this thread