Where do I put my validation code and message boxes?

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

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
 
Tony,

You are leaning in the right direction. For validating an existing record
which is being edited, put your validation code in the form's BeforeUpdate
event; for a new, unsaved record, put code in the form's BeforeInsert event.


hth,
 
Thanks Cheryl Did you see anything else wrong with my code BTW?
Tony
Cheryl Fischer said:
Tony,

You are leaning in the right direction. For validating an existing record
which is being edited, put your validation code in the form's BeforeUpdate
event; for a new, unsaved record, put code in the form's BeforeInsert event.


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Tony Williams said:
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
Click to expand...
Click to expand...
 
I don't see anything that jumps out as syntactically incorrect. I notice
that in your posting you have code to validate, followed by code to ask the
user what the next step should be (add another new record or edit the
just-saved record). I would move the code asking for the next step to the
form's AfterInsert and AfterUpdate events.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Tony Williams said:
Thanks Cheryl Did you see anything else wrong with my code BTW?
Tony
Cheryl Fischer said:
Tony,

You are leaning in the right direction. For validating an existing record
which is being edited, put your validation code in the form's BeforeUpdate
event; for a new, unsaved record, put code in the form's BeforeInsert event.


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Tony Williams said:
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,
Click to expand...
"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
Click to expand...
Click to expand...
Click to expand...
 
Thanks Cheryl those were the things I was wondering. Does the add another
record message go in the AfterInsert and the edit code go in the AfterUpdate
or the other way round?
TIA
Tony
Cheryl Fischer said:
I don't see anything that jumps out as syntactically incorrect. I notice
that in your posting you have code to validate, followed by code to ask the
user what the next step should be (add another new record or edit the
just-saved record). I would move the code asking for the next step to the
form's AfterInsert and AfterUpdate events.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Tony Williams said:
Thanks Cheryl Did you see anything else wrong with my code BTW?
Tony
Cheryl Fischer said:
Tony,

You are leaning in the right direction. For validating an existing record
which is being edited, put your validation code in the form's BeforeUpdate
event; for a new, unsaved record, put code in the form's BeforeInsert event.


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


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
Click to expand...
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." &
Click to expand...
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
If I understand your code correctly, you have two options offered to the
user after adding a new record: Add another new record *or* edit the
just-saved record. So, the code for both of those would go in the
AfterInsert event, when the action just completed is the addition of a new
record.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Tony Williams said:
Thanks Cheryl those were the things I was wondering. Does the add another
record message go in the AfterInsert and the edit code go in the AfterUpdate
or the other way round?
TIA
Tony
Cheryl Fischer said:
I don't see anything that jumps out as syntactically incorrect. I notice
that in your posting you have code to validate, followed by code to ask the
user what the next step should be (add another new record or edit the
just-saved record). I would move the code asking for the next step to the
form's AfterInsert and AfterUpdate events.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Tony Williams said:
Thanks Cheryl Did you see anything else wrong with my code BTW?
Tony
Tony,

You are leaning in the right direction. For validating an existing
record
which is being edited, put your validation code in the form's BeforeUpdate
event; for a new, unsaved record, put code in the form's BeforeInsert
event.


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


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." &
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Cheryl here is my After Insert code
Private Sub Form_AfterInsert()
Dim Answer2 As Integer
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
End If
End Sub

And here is my code on the cmdSave button
Dim Answer As Integer
Answer = MsgBox("Are you sure you want to save this record?", 36, "Enter New
Record")
If Answer = vbYes Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
If IsNull(DocNametxt.Value) Then
MsgBox "You cannot save a record without a Document Name. Please
enter a name.", vbCritical, "Name Required"
[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"
[cmbAuthor].SetFocus
Cancel = True
End If
End If


BUT the questions from the AfterInsert procedure appear before the Docnumtxt
and cmdAuthor messages. What am I doing wrong?
Thanks for sticking with me on this I know it's Saturday and |I'm sure we'd
both be better out in the fresh air today but this is bugging me!!!
TIA
Tony

Cheryl Fischer said:
If I understand your code correctly, you have two options offered to the
user after adding a new record: Add another new record *or* edit the
just-saved record. So, the code for both of those would go in the
AfterInsert event, when the action just completed is the addition of a new
record.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Tony Williams said:
Thanks Cheryl those were the things I was wondering. Does the add another
record message go in the AfterInsert and the edit code go in the AfterUpdate
or the other way round?
TIA
Tony
Cheryl Fischer said:
I don't see anything that jumps out as syntactically incorrect. I notice
that in your posting you have code to validate, followed by code to
Click to expand...
ask
the
user what the next step should be (add another new record or edit the
just-saved record). I would move the code asking for the next step
Click to expand...
to
the
form's AfterInsert and AfterUpdate events.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Thanks Cheryl Did you see anything else wrong with my code BTW?
Tony
Tony,

You are leaning in the right direction. For validating an existing
record
which is being edited, put your validation code in the form's
BeforeUpdate
event; for a new, unsaved record, put code in the form's BeforeInsert
event.


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Hi Cheryl it's 17.50 UK time and we're having folks round to dinner so I've
got to go and peel the veg!!! can we pick this up either Sunday or Monday?
Many thanks for your patience
Tony
Cheryl Fischer said:
If I understand your code correctly, you have two options offered to the
user after adding a new record: Add another new record *or* edit the
just-saved record. So, the code for both of those would go in the
AfterInsert event, when the action just completed is the addition of a new
record.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Tony Williams said:
Thanks Cheryl those were the things I was wondering. Does the add another
record message go in the AfterInsert and the edit code go in the AfterUpdate
or the other way round?
TIA
Tony
Cheryl Fischer said:
I don't see anything that jumps out as syntactically incorrect. I notice
that in your posting you have code to validate, followed by code to
Click to expand...
ask
the
user what the next step should be (add another new record or edit the
just-saved record). I would move the code asking for the next step
Click to expand...
to
the
form's AfterInsert and AfterUpdate events.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Thanks Cheryl Did you see anything else wrong with my code BTW?
Tony
Tony,

You are leaning in the right direction. For validating an existing
record
which is being edited, put your validation code in the form's
BeforeUpdate
event; for a new, unsaved record, put code in the form's BeforeInsert
event.


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Tony,

As soon as the following line from your Save button is executed:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

the AfterInsert code runs. Seems to me, from looking at your code behind
the cmdSave button, that you are saving first and then validating. I'd
move the actual save command to run after the validation sequence, as
follows:


Dim Answer 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.", vbCritical, "Name Required"
[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"
[cmbAuthor].SetFocus
Cancel = True
End If

'Validations passed!
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord,
acMenuVer70
End If


--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Tony Williams said:
Cheryl here is my After Insert code
Private Sub Form_AfterInsert()
Dim Answer2 As Integer
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
End If
End Sub

And here is my code on the cmdSave button
Dim Answer As Integer
Answer = MsgBox("Are you sure you want to save this record?", 36, "Enter New
Record")
If Answer = vbYes Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
If IsNull(DocNametxt.Value) Then
MsgBox "You cannot save a record without a Document Name. Please
enter a name.", vbCritical, "Name Required"
[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"
[cmbAuthor].SetFocus
Cancel = True
End If
End If


BUT the questions from the AfterInsert procedure appear before the Docnumtxt
and cmdAuthor messages. What am I doing wrong?
Thanks for sticking with me on this I know it's Saturday and |I'm sure we'd
both be better out in the fresh air today but this is bugging me!!!
TIA
Tony

Cheryl Fischer said:
If I understand your code correctly, you have two options offered to the
user after adding a new record: Add another new record *or* edit the
just-saved record. So, the code for both of those would go in the
AfterInsert event, when the action just completed is the addition of a new
record.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Tony Williams said:
Thanks Cheryl those were the things I was wondering. Does the add another
record message go in the AfterInsert and the edit code go in the AfterUpdate
or the other way round?
TIA
Tony
I don't see anything that jumps out as syntactically incorrect. I notice
that in your posting you have code to validate, followed by code to ask
the
user what the next step should be (add another new record or edit the
just-saved record). I would move the code asking for the next step to
the
form's AfterInsert and AfterUpdate events.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Thanks Cheryl Did you see anything else wrong with my code BTW?
Tony
Tony,

You are leaning in the right direction. For validating an existing
record
which is being edited, put your validation code in the form's
BeforeUpdate
event; for a new, unsaved record, put code in the form's BeforeInsert
event.


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


I have an input form which has a command button called
Click to expand...
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,
Click to expand...
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top