DoCmd Asks me twice

Topic

New Reply

When I run the following code with all entries made the routine asks me twice if I want to save. Can someone tell me why? Other than that, it seems to run correctly.

Private Sub btnSaveRecord_Click()
If IsNull([Active]) Then
MsgBox “You must make an entry in the ‘Active’ box”
[Active].SetFocus

ElseIf IsNull([FirstName]) Then
MsgBox “Enter First Name please.”
[FirstName].SetFocus

ElseIf IsNull([LastName]) Then
MsgBox “Enter Last Name please.”
[LastName].SetFocus

ElseIf IsNull([StartDate]) Then
MsgBox “Enter Start Date please.”
[StartDate].SetFocus

ElseIf MsgBox(“Are you sure you want to save?”, vbYesNo) = vbNo Then
Me.Undo
Else: DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close acForm, “frmMembersEntry”, acSaveYes
End If
End Sub

Reply | Quote

Replies

Are you sure it is that routine that is asking you twice? Is it possible that in the form’s BeforeUpdate event you have another “Are you sure?”?

Reply | Quote

Unless SetWarnings has been turned off the DoCmd will throw a warning.

Why do you use the save option on closing the form?

Reply | Quote

Unless SetWarnings has been turned off the DoCmd will throw a warning.

Why do you use the save option on closing the form?

Aha! There is the answer. I did not know that DoCmd would put out a warning. Now I need to find how to find the Set warnings. Maybe tomorrow.

I used the save option because if all the information was input that needs to be there it should be ready to save. I’m trying to keep my data typer from entering records that do not contain enough information to be useful. If I have those five items the organization has all it needs to go back in and edit any further information. Does that make sense? This has been a long work in progress.

Reply | Quote

I used the save option because if all the information was input that needs to be there it should be ready to save. I’m trying to keep my data typer from entering records that do not contain enough information to be useful. If I have those five items the organization has all it needs to go back in and edit any further information. Does that make sense? This has been a long work in progress.

In that type of situation you should create your form using all variables (non linked controls in Access parlance) not database fields. Then you have the option of checking all the information and deciding, in code of course, whether or not to save the record. You can even go it one better and not display the OK/Exit/Save button until enough information is present to constitute a valid record.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

“You can even go it one better and not display the OK/Exit/Save button until enough information is present to constitute a valid record.”

I like that approach. I don’t have enough code in my head to do it – but I’ll work on it. What the heck! I’m retired, 75 and have nothing else to do and, with luck, should have about 19 years to do it.

Thanks!

Reply | Quote

Morris,

Here’s a little push to get you on your way.
46595-Access-Field-Unbound

Notice how in the above form the field Text0 is UNBOUND, e.g. not tied to a database field. You can still refer to its value as Text0.value

So that’s how you set up your fields and check them in your code.

46596-Access-button

Notice how the Visible property of the button is set to Yes. Change it to No and it disappears. So in your Form Open event you set it to No then in your after update event of each field you call a routine that checks to see if you have enough information for a good record. When the conditions are met you set the button to visible e.g. cmdTestForm.Visible = True (same as Yes)

Here’s a sample:
46597-AccessOpenForm

Now fill in the data and:
46598-Access-buttonvia

Here’s the sample code:

Code:

Option Explicit

Private Sub cmdTestForm_Click()


   'Save your record by assgining the values to database fields.
   'This will involve creating a new DB Record.

End Sub

Private Sub Form_Open(Cancel As Integer)

    cmdTestForm.Visible = False
    
End Sub

Private Sub Text0_LostFocus()

   CheckValidRecord
   
End Sub

Private Sub Text4_LostFocus()

   CheckValidRecord
   
End Sub

Private Sub CheckValidRecord()

    If (IsNull(Me.Text4.Value)) Or _
       IsNull(Me.Text0.Value) Then
         MsgBox "Fields are Required", _
                vbOKOnly + vbCritical, _
                "Error: Required data missing"
      
    Else
       cmdTestForm.Visible = True
    End If
     
End Sub

Of course you’ll give your controls real names vs Text0 & Text4 so things are easier to work with and remember!

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

That’s pretty. I’ll give it a try and thank you very much.

Reply | Quote