Where do I put my code?

Tony Williams

I have an input form which has a command button called cmdSave. Thre
procedure behind this button does 2 things
1. It validates that 2 text controls are completed and
2. Pops up lots of message boxes asking the user if they want to save the
record, edit the record and input another record.
However I am having problems getting the code sequence right and wondered
first of all whether it was wise to put all this code behind the command
button and would it be better say in the BeforeUpdate property of the form.
Can anyone advise me and also can they spot anything obviously wrong with
this code.
I am a NEWBIE!!!!
Thanks
Tony Williams
Here is the code:

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click
DoCmd.SetWarnings False
Dim Answer As Integer
Dim Answer2 As Integer
Dim Answer2a As Integer
Dim Answer3 As Integer

Answer = MsgBox("Are you sure you want to save this record?", 36, "Enter New
Record")
If Answer = vbYes Then
If IsNull(DocNametxt.Value) Then
MsgBox "You cannot save a record without a Document Name. Please
enter a name.", 16
[DocNametxt].SetFocus
Cancel = True
End If
If IsNull(cmbAuthor.Value) Then
MsgBox "You cannot save a record without an Author." & vbCrLf &
"Please enter an Author's name.", vbCritical, "Name Required"
Cancel = True
[cmbAuthor].SetFocus
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Answer2 = MsgBox("Do you want to input another record?", 36,
"New Record")
If Answer2 = vbYes Then
DoCmd.OpenForm "frmdocumentrecord", acNormal
DoCmd.GoToRecord , , acNewRec
If IsNull(Me.Docnumtxt) Then
Populate_URN
[DocNametxt].SetFocus
End If
End If
If Answer2 = vbNo Then
Answer2a = MsgBox("Do you want to return to edit the
record?", 36, "New Record")
If Answer2a = vbYes Then
DoCmd.OpenForm "frmdocumentrecord", acNormal
Cancel = True
End If
If Answer2a = vbNo Then
DoCmd.Close
Cancel = True
End If
End If

If Answer = vbNo Then
Answer3 = MsgBox("Do you want to return to edit the record?",
36, "New Record")
If Answer3 = vbYes Then
DoCmd.OpenForm "frmdocumentrecord", acNormal
Cancel = True
End If
If Answer3 = vbNo Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.Close
End If
End If
End If
DoCmd.SetWarnings True
Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub

Nov 12 '05 #1

Salad

Tony Williams wrote:

I have an input form which has a command button called cmdSave. Thre
procedure behind this button does 2 things
1. It validates that 2 text controls are completed and
2. Pops up lots of message boxes asking the user if they want to save the
record, edit the record and input another record.
However I am having problems getting the code sequence right and wondered
first of all whether it was wise to put all this code behind the command
button and would it be better say in the BeforeUpdate property of the form.
Can anyone advise me and also can they spot anything obviously wrong with
this code.
I am a NEWBIE!!!!
Thanks
Tony Williams
I would do your validation in the BeforeUpdate. When the button is
pressed you can determine if the data has changed with the Dirty
property. You can then force a save. The following could be put in
your button code
If Me.Dirty Then Me.Dirty = False
and this will call the validation routine in BeforeUpdate. You will
want to have an Error routine in the button code because if you Cancel
the save in the BeforeUpdate you want to trap the error message. Ex:
Err_CommandSave:
If err.Number <> 2501 then msgbox err.description
resume Exit_CommandSave
You really don't need the error routine. It simply stops the message
from being displayed that informs the user the action was canceled.

In your case I might want to present a new form instead of annoying the
user with messageboxes. Create a new form with an option group and 3
checkbox options; New Record, Edit Current Record, Exit. Let's call
this form RecOptions. If NewRec or Edit Current are selected, make the
form invisible, else close the form. Lets say the option group is
called Frame1
Me.Visible = False

Sub CommandSave
If Me.Dirty Then Me.Dirty = False
Docmd.OpenForm "RecOptions",,,,acDialog
Select Case Forms!RecOptions!Frame1
Case 1
'code to go to new record
Case 2
'use selected to edit/view current
Me.WhateverFieldYouWant.SetFocus
Case 3
'user selected to exit
docmd.close
end select

Exit_CommandSave:
Exit sub
Err_CommandSave:
If err.Number <> 2501 then msgbox err.description
resume Exit_CommandSave
End Sub

Now you get rid of the myriad number of messageboxes.

Personally, I like the navigation buttons. You could put a button if
you like to save and do the Me.Dirty = False. But with the navigation
buttons they simply stay on the record or can press the button to go to
a new record. If the table isn't going to be huge, you can have a
synchronized combo to let the user select the record to view/edit. But
that's my personal preference.
Here is the code:

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click
DoCmd.SetWarnings False
Dim Answer As Integer
Dim Answer2 As Integer
Dim Answer2a As Integer
Dim Answer3 As Integer

Answer = MsgBox("Are you sure you want to save this record?", 36, "Enter New
Record")
If Answer = vbYes Then
If IsNull(DocNametxt.Value) Then
MsgBox "You cannot save a record without a Document Name. Please
enter a name.", 16
[DocNametxt].SetFocus
Cancel = True
End If
If IsNull(cmbAuthor.Value) Then
MsgBox "You cannot save a record without an Author." & vbCrLf &
"Please enter an Author's name.", vbCritical, "Name Required"
Cancel = True
[cmbAuthor].SetFocus
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Answer2 = MsgBox("Do you want to input another record?", 36,
"New Record")
If Answer2 = vbYes Then
DoCmd.OpenForm "frmdocumentrecord", acNormal
DoCmd.GoToRecord , , acNewRec
If IsNull(Me.Docnumtxt) Then
Populate_URN
[DocNametxt].SetFocus
End If
End If
If Answer2 = vbNo Then
Answer2a = MsgBox("Do you want to return to edit the
record?", 36, "New Record")
If Answer2a = vbYes Then
DoCmd.OpenForm "frmdocumentrecord", acNormal
Cancel = True
End If
If Answer2a = vbNo Then
DoCmd.Close
Cancel = True
End If
End If

If Answer = vbNo Then
Answer3 = MsgBox("Do you want to return to edit the record?",
36, "New Record")
If Answer3 = vbYes Then
DoCmd.OpenForm "frmdocumentrecord", acNormal
Cancel = True
End If
If Answer3 = vbNo Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.Close
End If
End If
End If
DoCmd.SetWarnings True
Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub

Nov 12 '05 #2

Tony Williams

Thanks Salad that's good advice about the option group I'll think I'll try
that
Tony
"Salad" <oi*@vinegar.com> wrote in message
news:Tf******************@newsread2.news.pas.earth link.net...

Tony Williams wrote:

I have an input form which has a command button called cmdSave. Thre
procedure behind this button does 2 things
1. It validates that 2 text controls are completed and
2. Pops up lots of message boxes asking the user if they want to save the record, edit the record and input another record.
However I am having problems getting the code sequence right and wondered first of all whether it was wise to put all this code behind the command
button and would it be better say in the BeforeUpdate property of the form. Can anyone advise me and also can they spot anything obviously wrong with this code.
I am a NEWBIE!!!!
Thanks
Tony Williams

I would do your validation in the BeforeUpdate. When the button is
pressed you can determine if the data has changed with the Dirty
property. You can then force a save. The following could be put in
your button code
If Me.Dirty Then Me.Dirty = False
and this will call the validation routine in BeforeUpdate. You will
want to have an Error routine in the button code because if you Cancel
the save in the BeforeUpdate you want to trap the error message. Ex:
Err_CommandSave:
If err.Number <> 2501 then msgbox err.description
resume Exit_CommandSave
You really don't need the error routine. It simply stops the message
from being displayed that informs the user the action was canceled.

In your case I might want to present a new form instead of annoying the
user with messageboxes. Create a new form with an option group and 3
checkbox options; New Record, Edit Current Record, Exit. Let's call
this form RecOptions. If NewRec or Edit Current are selected, make the
form invisible, else close the form. Lets say the option group is
called Frame1
Me.Visible = False

Sub CommandSave
If Me.Dirty Then Me.Dirty = False
Docmd.OpenForm "RecOptions",,,,acDialog
Select Case Forms!RecOptions!Frame1
Case 1
'code to go to new record
Case 2
'use selected to edit/view current
Me.WhateverFieldYouWant.SetFocus
Case 3
'user selected to exit
docmd.close
end select

Exit_CommandSave:
Exit sub
Err_CommandSave:
If err.Number <> 2501 then msgbox err.description
resume Exit_CommandSave
End Sub

Now you get rid of the myriad number of messageboxes.

Personally, I like the navigation buttons. You could put a button if
you like to save and do the Me.Dirty = False. But with the navigation
buttons they simply stay on the record or can press the button to go to
a new record. If the table isn't going to be huge, you can have a
synchronized combo to let the user select the record to view/edit. But
that's my personal preference.

Here is the code:

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click
DoCmd.SetWarnings False
Dim Answer As Integer
Dim Answer2 As Integer
Dim Answer2a As Integer
Dim Answer3 As Integer

Answer = MsgBox("Are you sure you want to save this record?", 36, "Enter New Record")
If Answer = vbYes Then
If IsNull(DocNametxt.Value) Then
MsgBox "You cannot save a record without a Document Name. Please
enter a name.", 16
[DocNametxt].SetFocus
Cancel = True
End If
If IsNull(cmbAuthor.Value) Then
MsgBox "You cannot save a record without an Author." & vbCrLf &
"Please enter an Author's name.", vbCritical, "Name Required"
Cancel = True
[cmbAuthor].SetFocus
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Answer2 = MsgBox("Do you want to input another record?", 36,
"New Record")
If Answer2 = vbYes Then
DoCmd.OpenForm "frmdocumentrecord", acNormal
DoCmd.GoToRecord , , acNewRec
If IsNull(Me.Docnumtxt) Then
Populate_URN
[DocNametxt].SetFocus
End If
End If
If Answer2 = vbNo Then
Answer2a = MsgBox("Do you want to return to edit the
record?", 36, "New Record")
If Answer2a = vbYes Then
DoCmd.OpenForm "frmdocumentrecord", acNormal
Cancel = True
End If
If Answer2a = vbNo Then
DoCmd.Close
Cancel = True
End If
End If

If Answer = vbNo Then
Answer3 = MsgBox("Do you want to return to edit the record?", 36, "New Record")
If Answer3 = vbYes Then
DoCmd.OpenForm "frmdocumentrecord", acNormal
Cancel = True
End If
If Answer3 = vbNo Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.Close
End If
End If
End If
DoCmd.SetWarnings True
Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub

Nov 12 '05 #3