Problems with Form_BeforeUpdate and DoCmd.RunCommand acCmdSaveRecord

R

Richard1980

Registered User.
Local time
Today, 19:22
Joined
Aug 13, 2012
Messages
72
Hello gents,
I am struggling since yesterday on this item. I have a Form_BeforeUpdate event to run some (several) checks in my form. This is a bound form to a table and it has these properties set:
- Cycle: current record;
- Data entry: yes.
In the form there is a button to save current record and move ahead to another (blank) record.
Code for the Form_BeforeUpdate event is the following:
Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
'### check if mandatory fields are blank/null ###'
Dim ctl As Control
Dim testo As String

For Each ctl In Me.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox
            If ctl.Tag = "Required" And IsNull(ctl) Then
              testo = ctl.Controls(0).Caption
              testo = Left(testo, Len(testo) - 1)
              MsgBox "The following field is required: " & vbCrLf & vbCrLf _
              & "- " & testo
              Cancel = True
              ctl.SetFocus
              Exit Sub
            End If
    End Select
Next ctl

'### Check lenght of Latitude field ###'
If Len(txtLat) <> 7 Then
    MsgBox "Latitude is not correct, please complete all components of the field."
    Cancel = True
    txtLat.SetFocus
    Exit Sub
ElseIf IsNull(txtLat) Then
    Dim textMessage As Integer
        textMessage = MsgBox("Latitude data is not mandatory, but the distance calculation feature in the " _
        & "flight data center will not be available. Do you want to add latitude?", vbYesNo)
            If textMessage = vbYes Then
                txtLat.SetFocus
                Exit Sub
            Else
            End If
Else
txtLat = UCase(txtLat)
End If

'### Check lenght of Longitude field ###'
If Len(txtLon) <> 8 Then
    MsgBox "Longitude is not correct, please complete all components of the field."
    Cancel = True
    txtLon.SetFocus
    Exit Sub
ElseIf IsNull(txtLon) Then
    Dim textMessage2 As Integer
    textMessage2 = MsgBox("Longitude data is not mandatory, but the distance calculation feature in the " _
        & "flight data center will not be available. Do you want to add longitude?", vbYesNo)
            If textMessage2 = vbYes Then
                txtLon.SetFocus
                Exit Sub
            Else
            MsgBox "All data you inserted will be saved shortly."
            End If
Else
txtLon = UCase(txtLon)
End If

'### checks for the Latitude field components ###'
If Mid(txtLat, 2, 2) >= 90 Then
    MsgBox "Latitude cannot be higher than 90° degrees, please edit the field accordingly"
    Cancel = True
    txtLat.SetFocus
ElseIf Mid(txtLat, 4, 2) >= 60 Then
    MsgBox "Minutes of Latitude field cannot be higher than 60, please edit the field accordingly"
    Cancel = True
    txtLat.SetFocus
ElseIf Mid(txtLat, 6, 2) >= 60 Then
    MsgBox "Seconds of Latitude field cannot be higher than 60, please edit the field accordingly"
    Cancel = True
    txtLat.SetFocus
Else
txtLat = UCase(txtLat)
End If

'### checks for the Longitude field components ###'
If Mid(txtLon, 2, 3) >= 180 Then
    MsgBox "Longitude cannot be higher than 180° degrees, please edit the field accordingly"
    Cancel = True
    txtLon.SetFocus
    Exit Sub
ElseIf Mid(txtLon, 5, 2) >= 60 Then
    MsgBox "Minutes of Longitude field cannot be higher than 60, please edit the field accordingly"
    Cancel = True
    txtLon.SetFocus
ElseIf Mid(txtLon, 7, 2) >= 60 Then
    MsgBox "Seconds of Longitude field cannot be higher than 60, please edit the field accordingly"
    Cancel = True
    txtLon.SetFocus
Else
txtLon = UCase(txtLon)
End If
End Sub

Save button has a cmdSave_click event:
Code: 
Private Sub cmdSave_Click()
Call Form_BeforeUpdate(False)
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acNewRec
End Sub

When I click on the cmdSave button the BeforeUpdate event doesn't work as expected. A MsgBox comes up twice (The following field .... is mandatory), I press OK on the MsgBox and the procedure try to run the DoCmd.RunCommand acCmdSaveRecord given an error: "Property not found". Debugging shows me the row where DoCmd.RunCommand stands.
How to get running the BeforeUpdate correctly and, if all checks are confirmed, saving the records as wanted?
Thanks for your help.
Bye.

Riccardo
 
1. You don't need to call the before update event in your click event. It already occurs when you attempt to save the record.

2. Change your click event to this:

Code: 
Private Sub cmdSave_Click()
If Me.Dirty Then
  Me.Dirty = False
  If Not Me.NewRecord Then 
     DoCmd.RunCommand acCmdRecordsGoToNew
  End If
Else
   MsgBox "There is nothing to save.", vbInformation
End If
End Sub
The benefit of using the If Me.Dirty Then is that it will only attempt a save if there is something to save. If you use the command you are using it will attempt to save regardless of anything needing to be saved.

Also, I don't have time right now to do it but you can consolidate your messages in your before update event so only ONE message will appear should more than one thing not be filled out properly and not all of them firing then making the user fill it out and then they click save again and find out they had another one missing, and so on.
 
Thanks Bob, but unfortunately the code does not work.
Situation: Completely blank form, click on the cmdSave button, instead of the message you wrote, the MsgBox for the first required field comes up. Press on OK, then Run-Time error '3270': "Property not found". Debugging the error:
Me.Dirty = False
is highlighted.
No choice.
 
Richard1980 said:
Thanks Bob, but unfortunately the code does not work.
Situation: Completely blank form, click on the cmdSave button, instead of the message you wrote, the MsgBox for the first required field comes up. Press on OK, then Run-Time error '3270': "Property not found". Debugging the error:
Me.Dirty = False
is highlighted.
No choice.
Click to expand...

If it doesn't error on the first part

If Me.Dirty Then

Then I'm confused. Show your code you are now trying.
 
boblarson said:
If it doesn't error on the first part

If Me.Dirty Then

Then I'm confused. Show your code you are now trying.
Click to expand...

the code is exactly what you posted + mine (published in the first thread's post) about the BeforeUpdate event.
 
boblarson said:
So my code exclusively in the Click event?
Click to expand...
your code:

Code: 
Private Sub cmdSave_Click()
If Me.Dirty Then
Me.Dirty = False
If Not Me.NewRecord Then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
Else
MsgBox "There is nothing to save.", vbInformation
End If
End Sub
plus mine on the Form_BeforeUpdate (Cancel As Integer) event
Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
'### check if mandatory fields are blank/null ###'
Dim ctl As Control
Dim testo As String

For Each ctl In Me.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox
            If ctl.Tag = "Required" And IsNull(ctl) Then
              testo = ctl.Controls(0).Caption
              testo = Left(testo, Len(testo) - 1)
              MsgBox "The following field is required: " & vbCrLf & vbCrLf _
              & "- " & testo
              Cancel = True
              ctl.SetFocus
              Exit Sub
            End If
    End Select
Next ctl

'### Check lenght of Latitude field ###'
If Len(txtLat) <> 7 Then
    MsgBox "Latitude is not correct, please complete all components of the field."
    Cancel = True
    txtLat.SetFocus
    Exit Sub
ElseIf IsNull(txtLat) Then
    Dim textMessage As Integer
        textMessage = MsgBox("Latitude data is not mandatory, but the distance calculation feature in the " _
        & "flight data center will not be available. Do you want to add latitude?", vbYesNo)
            If textMessage = vbYes Then
                txtLat.SetFocus
                Exit Sub
            Else
            End If
Else
txtLat = UCase(txtLat)
End If

'### Check lenght of Longitude field ###'
If Len(txtLon) <> 8 Then
    MsgBox "Longitude is not correct, please complete all components of the field."
    Cancel = True
    txtLon.SetFocus
    Exit Sub
ElseIf IsNull(txtLon) Then
    Dim textMessage2 As Integer
    textMessage2 = MsgBox("Longitude data is not mandatory, but the distance calculation feature in the " _
        & "flight data center will not be available. Do you want to add longitude?", vbYesNo)
            If textMessage2 = vbYes Then
                txtLon.SetFocus
                Exit Sub
            Else
            MsgBox "All data you inserted will be saved shortly."
            End If
Else
txtLon = UCase(txtLon)
End If

'### checks for the Latitude field components ###'
If Mid(txtLat, 2, 2) >= 90 Then
    MsgBox "Latitude cannot be higher than 90° degrees, please edit the field accordingly"
    Cancel = True
    txtLat.SetFocus
ElseIf Mid(txtLat, 4, 2) >= 60 Then
    MsgBox "Minutes of Latitude field cannot be higher than 60, please edit the field accordingly"
    Cancel = True
    txtLat.SetFocus
ElseIf Mid(txtLat, 6, 2) >= 60 Then
    MsgBox "Seconds of Latitude field cannot be higher than 60, please edit the field accordingly"
    Cancel = True
    txtLat.SetFocus
Else
txtLat = UCase(txtLat)
End If

'### checks for the Longitude field components ###'
If Mid(txtLon, 2, 3) >= 180 Then
    MsgBox "Longitude cannot be higher than 180° degrees, please edit the field accordingly"
    Cancel = True
    txtLon.SetFocus
    Exit Sub
ElseIf Mid(txtLon, 5, 2) >= 60 Then
    MsgBox "Minutes of Longitude field cannot be higher than 60, please edit the field accordingly"
    Cancel = True
    txtLon.SetFocus
ElseIf Mid(txtLon, 7, 2) >= 60 Then
    MsgBox "Seconds of Longitude field cannot be higher than 60, please edit the field accordingly"
    Cancel = True
    txtLon.SetFocus
Else
txtLon = UCase(txtLon)
End If
End Sub
 
In other hands, I need to exit DoCmd.RunCommand acCmdSaveRecord routine (in the cmdSave_click event) until all checks in the BeforeUpdate event are passed.
I don't know how to perform this action. I want to execute all checks in the BeforeUpdate event, sospend temporarily the DoCmd.RunCommand until all checks are passed and only then perform the save record command.
How to get running a save record by a button, after having passed all BeforeUpdate checks?
 
Your Len() checks may fail. Write it like this:
Code: 
If Len([[COLOR=Red]YourField[/COLOR]] & vbNullString) <> [COLOR=Red]7[/COLOR] Then
 
It may be working but what I've just written is the recommended way of using the Len() function to validate your control. If you want to know more, read about the differences between Null and zero-length string. If you don't have the "Allow Zero-Length String" property set to No, then it's best you use what I've given you.
 
No, the problem is here:
Code: 
For Each ctl In Me.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox
            If ctl.Tag = "Required" And IsNull(ctl) Then
              testo = ctl.Controls(0).Caption
              testo = Left(testo, Len(testo) - 1)
              MsgBox "The following field is required: " & vbCrLf & vbCrLf _
              & "- " & testo
              'Cancel = True
              ctl.SetFocus
              Exit Sub
            End If
    End Select
Next ctl
Now, comment everything in the Form_BeforeUpdate except for the first part (the part showed above).
In the cmdSave_click event the code is this:
Code: 
Call Form_BeforeUpdate(False)
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acNewRec
When I firstly open the form, which is completely blank, click on the save button. The MsgBox whith the field required comes alive twice, I press on OK (twice). Instead of exit sub, going to the required control, the procedure tries to save record and now the VBA error 2105 "You can't go to the specific record" comes up, hightlighting the "DoCmd.GoToRecord , , acNewRec" row in the code.
So, the cmdSave_click event does not stop if the BeforeUpdate find some errors in validation procedure.
Don't know how to fix this matter.

edit:
even better. The problem is not in the For Each ... statement, but because that statement is in the BeforeUpdate event and not in the cmdSave_click event. It fires, Access shows me the blank (required) control and then goes ahead trying to save record, while it should have to stop. This not happens.
 
Last edited:
You would imagine that this is not the first time we've been asked this question and consequently it's not the first time we've given solutions to it too.

I've already told you the recommended way of using Len() when validating control values and Bob has also mentioned that you shouldn't be calling the BeforeUpdate event from the click event of your button. Calling this event will cause the msgbox to pop up twice. Bob also gave you code you should use in the Click event, not what you're doing at the moment.

By the way, what is testo? Is it linked to a field?
 
1. Ok, erasing Len(), it does not matter... It is not the problem.
2. testo is a string. In the For Each... sentence I obtain the name of the required field which is blank and put it in the MsgBox using that string. Call testo everything what you want.
3. I have already used the Me.Dirty suggestion by Bob and already reported the issue as well.
The issue is that: in a blank form when push the save button MsgBox with the required field comes up, press OK and then:
Run-Time error '3270'
Property not found.
Me.dirty = False is highlighted in Debug mode.

So, now, report again the code here below:
Code: 
Private Sub cmdSave_Click()
If Me.Dirty Then
   Me.Dirty = False
   If Not Me.NewRecord Then
       DoCmd.RunCommand acCmdRecordsGoToNew
   End If
Else
    MsgBox "There is nothing to save.", vbInformation
End If
End Sub
Code: 
Private sub Form_BeforeUpdate (Cancel As Integer)
Dim ctl As Control
Dim testo As String

For Each ctl in Me.Controls
     Select Case ctl.ControlType
          Case acTextBox, acComboBox
               If ctl.Tag = "required" And IsNull(ctl) Then
               testo = ctl.Controls(0).Caption
               MsgBox "The following field is required: " & testo
               Cancel = True
               ctl.SetFocus
               Exit Sub
           End if
      End select
Next ctl
End sub
 
Alright, upload your db with some test data and I will fix the code.
 
As your Form is bound there is no need to save it.

It will save automatically when closing or going to a new record.

The Before Update Event should also fire just before saving.

Try commenting out all the Save Code and test it.

I have not done any testing.
 
It is a long battle here... :)
I have reached a positive result, but not the definitive one.
This the code for the cmdSave_Click event.

Code: 
Private Sub cmdSave_Click()
If Me.Dirty Then
   Me.Dirty = False
Else
DoCmd.GoToRecord , , acNewREc
End Sub

In the BeforeUpdate event of the form I must comment the row:
Cancel = True

Code: 
Private sub Form_BeforeUpdate (Cancel As Integer)
Dim ctl As Control
Dim testo As String

For Each ctl in Me.Controls
     Select Case ctl.ControlType
          Case acTextBox, acComboBox
               If ctl.Tag = "required" Then
               If Len(Nz(ctl, vbNullString)) = 0 Then
               testo = ctl.Controls(0).Caption
               MsgBox "The following field is required: " & testo
               'Cancel = True
               ctl.SetFocus
               Exit Sub
           End if
           End if
      End select
Next ctl
End sub

Now it works pretty well, but every time I want to save the record I need to click twice on the button. Better, the first time it saves the record, but stay all data there, the second time it clears all fields and go to the new record.
 
RainLover said:
As your Form is bound there is no need to save it.

It will save automatically when closing or going to a new record.

The Before Update Event should also fire just before saving.

Try commenting out all the Save Code and test it.

I have not done any testing.
Click to expand...

I know that, but IMHO it is necessary, for many reasons:
1. not every Access user knows that moving with TAB or other to another record it saves the record. Many unskilled users could not know how to save data.
2. I want to avoid moving accidentally between records (saving the current) before having checked the correct data inserted.
and so on...
 
1. not every Access user knows that moving with TAB or other to another record it saves the record. Many unskilled users could not know how to save data.
2. I want to avoid moving accidentally between records (saving the current) before having checked the correct data inserted.
and so on...
Click to expand...

Are your users as stupid as you make them out to be. I am sure that they will catch on.

What have you done in situation number 2. I have seen nothing.

Where are the rest of the many reasons.
 
RainLover said:
Are your users as stupid as you make them out to be. I am sure that they will catch on.

What have you done in situation number 2. I have seen nothing.

Where are the rest of the many reasons.
Click to expand...

???
no words about the users...
I need to stay on the current record, avoid pressing TAB on the last control, then move to the next record and saving the current.
For this reason I set "Current record" in the form property.
Given this scenario, what to do for saving record, if not with a button?
Thanks.

they are only two of n reasons... I don't want to list them all.
 
You must log in or register to reply here.

Similar threads

Mike Krailo
Solved Need help with before update code logic?
Replies
12
Views
395
Mike Krailo
Mike Krailo
T
Can someone please assist with the VBA code Below - Error Message 3075
Replies
16
Views
811
CJ_London
C
D
Solved Unbound form required using VBA
Replies
9
Views
662
Pat Hartman
P
V
Need help with VBA to check for required fields
Replies
6
Views
393
MajP
MajP
N
Solved How to send multiple attachment with CDO Gmail in MS Access VBA
Replies
11
Views
511
nector
N

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom