Help with messages

T

Tony Williams

I have a form that has 2 controls that the user must complete before the
record is saved. Here is my code:

If IsNull(DocNametxt.Value) Then
MsgBox "You cannot save a record without a Document Name. Please
enter a name.", vbCritical, "Name Required"
[DocNametxt].SetFocus

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

Else
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
End If
End If
End If
DoCmd.Close

The problem is if the DocNametxt or cmbAuthor are blank both messages
appear and the "Are you sure you want to save this record?" message appear
and then the form closes. It doesn't give the user the oppurtunity to
complete the blank controls and then carry on.
What am I doing wrong? This code is behind a command button that is cmdSave
TIA
Tony Williams
 
S

Steve Schapel

Tony,

Try it like this...

If IsNull(Me.DocNametxt) Then
MsgBox "You cannot save a record without a Document Name. Please
enter a name.", vbCritical, "Name Required"
Me.DocNametxt.SetFocus
ElseIf IsNull(Me.cmbAuthor) Then
MsgBox "You cannot save a record without an Author." & vbCrLf & _
"Please enter an Author's name.", vbCritical, "Name Required"
Me.cmbAuthor.SetFocus
Else
Answer = MsgBox("Are you sure you want to save this record?", _
vbQuestion + vbYesNo, "Enter New Record")
If Answer = vbYes Then
DoCmd.RunCommand acCmdSaveRecord
End If
DoCmd.Close acForm, Me.Name
End If

However, I am not sure what the purpose of this code can be. The record
will be saved anyway when the form closes, or you move to another
record. It would be more usual to either use a Validation Rule for
these fields, or to check that they have data on the Form's BeforeUpdate
event.
 
T

Tony Williams

Thanks Steve Does that mean I don't need the line
DoCmd.RunCommand acCmdSaveRecord
I've tried putting the validation in the before update of the control but I
can't get the messages to appear there
TIA
Tony
Steve Schapel said:
Tony,

Try it like this...

If IsNull(Me.DocNametxt) Then
MsgBox "You cannot save a record without a Document Name. Please
enter a name.", vbCritical, "Name Required"
Me.DocNametxt.SetFocus
ElseIf IsNull(Me.cmbAuthor) Then
MsgBox "You cannot save a record without an Author." & vbCrLf & _
"Please enter an Author's name.", vbCritical, "Name Required"
Me.cmbAuthor.SetFocus
Else
Answer = MsgBox("Are you sure you want to save this record?", _
vbQuestion + vbYesNo, "Enter New Record")
If Answer = vbYes Then
DoCmd.RunCommand acCmdSaveRecord
End If
DoCmd.Close acForm, Me.Name
End If

However, I am not sure what the purpose of this code can be. The record
will be saved anyway when the form closes, or you move to another
record. It would be more usual to either use a Validation Rule for
these fields, or to check that they have data on the Form's BeforeUpdate
event.

--
Steve Schapel, Microsoft Access MVP


Tony said:
I have a form that has 2 controls that the user must complete before the
record is saved. Here is my code:

If IsNull(DocNametxt.Value) Then
MsgBox "You cannot save a record without a Document Name. Please
enter a name.", vbCritical, "Name Required"
[DocNametxt].SetFocus

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

Else
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
End If
End If
End If
DoCmd.Close

The problem is if the DocNametxt or cmbAuthor are blank both messages
appear and the "Are you sure you want to save this record?" message appear
and then the form closes. It doesn't give the user the oppurtunity to
complete the blank controls and then carry on.
What am I doing wrong? This code is behind a command button that is cmdSave
TIA
Tony Williams
Click to expand...
Click to expand...
 
S

Steve Schapel

Tony,

I was referring to the Before Update event of the Form itself, not the
control.

You could leave out the DoCmd.RunCommand acCmdSaveRecord line. In fact
you could leave out the whole section:
Answer = MsgBox("Are you sure you want to save this record?", _
vbQuestion + vbYesNo, "Enter New Record")
If Answer = vbYes Then
DoCmd.RunCommand acCmdSaveRecord
End If
.... and it wouldn't make any difference to the functionality. I guess
the point is, what happens if the user says No, they are not sure they
want to save the record... it gets saved anyway when the form closes.
 
T

Tony Williams

Steve If they say no I was going on to ask them if they wanted to go back
and edit the record or input a new record and get a blank form> Does that
make sense?
Tony
 
S

Steve Schapel

Tony,

Yep, it makes sense. However, if they say new record, how are you going
to do that? If you go to a new record on the form, the existing record,
which they say they don't want to save, will get saved. And then, what
if they don't want to edit the record or do a new one, they just want to
go to lunch... what then?
 
T

Tony Williams

Steve I was thinking about something along these lines

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

If IsNull(Me.DocNametxt) Then
MsgBox "You cannot save a record without a Document Name. Please enter
a name.", vbCritical, "Name Required"
Me.DocNametxt.SetFocus
ElseIf IsNull(Me.cmbAuthor) Then
MsgBox "You cannot save a record without an Author." & vbCrLf & _
"Please enter an Author's name.", vbCritical, "Name Required"
Me.cmbAuthor.SetFocus
Else
Answer = MsgBox("Are you sure you want to save this record?", _
vbQuestion + vbYesNo, "Enter New Record")
If Answer = vbYes Then
DoCmd.RunCommand acCmdSaveRecord


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

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
DoCmd.SetWarnings True
Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub
 
T

Tony Williams

Hi Steve 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
 
S

Steve Schapel

Tony,

Sorry, this won't work. Cancel = True is supposed to refer to what?
This type of code refers to cancelling an event, if the event supposrts
a Cancel parameter. The 'active' Event is the Click event of the
cmdSave button, but you can't cancel a button click.

If it was mine, I would scrap the idea of a Save button (I have *never*
had one in any of my databases ever), I would use Validation Rule
settings to check for the entry of data into the 2 required fields, I
would use the Before Update event of the form to get confirmation (if
you really need this) to save the record, and I would use separate
buttons on the form to navigate to a new record or to close the form.
 
T

Tony Williams

Thanks Steve that sounds like a major rethink but I take the advice of an
expert that's why I post on here. I'm a 60 year old newbie at this and more
or less trying to teach myself so I suppose I don't always see the obvious
or best way of doing things. I've never used validation rules so I'd better
read up on that and think about restructuring my form.
Thanks again for your patience.
Tony
Steve Schapel said:
Tony,

Sorry, this won't work. Cancel = True is supposed to refer to what?
This type of code refers to cancelling an event, if the event supposrts
a Cancel parameter. The 'active' Event is the Click event of the
cmdSave button, but you can't cancel a button click.

If it was mine, I would scrap the idea of a Save button (I have *never*
had one in any of my databases ever), I would use Validation Rule
settings to check for the entry of data into the 2 required fields, I
would use the Before Update event of the form to get confirmation (if
you really need this) to save the record, and I would use separate
buttons on the form to navigate to a new record or to close the form.

--
Steve Schapel, Microsoft Access MVP


Tony said:
Steve I was thinking about something along these lines

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

If IsNull(Me.DocNametxt) Then
MsgBox "You cannot save a record without a Document Name. Please enter
a name.", vbCritical, "Name Required"
Me.DocNametxt.SetFocus
ElseIf IsNull(Me.cmbAuthor) Then
MsgBox "You cannot save a record without an Author." & vbCrLf & _
"Please enter an Author's name.", vbCritical, "Name Required"
Me.cmbAuthor.SetFocus
Else
Answer = MsgBox("Are you sure you want to save this record?", _
vbQuestion + vbYesNo, "Enter New Record")
If Answer = vbYes Then
DoCmd.RunCommand acCmdSaveRecord


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

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
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...
 
S

Steve Schapel

Tony,

Regarding the Validation Rules, you can set this up in the table design,
in this case you would put Is Not Null as the validation rule for the
two fields where an entry is required, and then your message prompt in
the Validation Text property.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Set Focus 4
Where do I put my validation code and message boxes? 8
Restrict entering Null or Empty in Textbox - Access 2007 0
Custom Menus 2
Run Time Error 3075 2
Am I missing something? 12
Save and Close command buttons 1
Help with DataEntry in a form 3

Top