Proper way to save a record

[Pat Dools]

Hello,

I have the following code that is set behind a 'New Record' Command button
at the footer of the form that I wish to 1) Save the record that has been
entered (after checking for 'Required' fields), 2) then open a New Record.
Is the 'Save' portion of this code done correctly? Thank you.

Private Sub CommandNewRecord_Click()
If Len(Me.site.Value & "") = 0 Then
MsgBox "You must enter a value into Site Number."
Me.site.SetFocus
ElseIf Len(Me.id.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Number."
Me.id.SetFocus
ElseIf Len(Me.ptin.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Initials."
Me.ptin.SetFocus
ElseIf Len(Me.course.Value & "") = 0 Then
MsgBox "You must enter a value into Course."
Me.course.SetFocus
ElseIf Len(Me.vs_Adoseday.Value & "") = 0 Then
MsgBox "You must enter a value into Cycle."
Me.vs_Adoseday.SetFocus
ElseIf Len(Me.data1_init.Value & "") = 0 Then
MsgBox "You must enter a value into Data Entry 1 Initials."
Me.data1_init.SetFocus
Else
DoCmd.Save acForm, "FCycDoseVitalAmp"
DoCmd.OpenForm "FCycDoseVitalAmp", acNormal
DoCmd.GoToRecord acDataForm, "FCycDoseVitalAmp", acNewRec
DoCmd.GoToControl "course"
End If
End Sub

 

[Albert D.Kallal]

DoCmd.Save acForm, "FCycDoseVitalAmp"

The above command actually saves the form, and not the data. Clearly, you
don't need, nor want to save the form, so, dump the above line, it is NOT
needed.

DoCmd.OpenForm "FCycDoseVitalAmp", acNormal

Why are you opening the form when it is already open? Again, you don't need
to open a form that is already open. So, dump the above line, it is NOT
needed.

DoCmd.GoToRecord acDataForm, "FCycDoseVitalAmp", acNewRec

The above looks fine. Remember, if your code, or your user navigates to
another record, ms-access thoughtfully, and kindly saves the current data
for you

DoCmd.GoToControl "course"

The above is also fine. I tend to prefer using the setfocus method of a
control., such as

course.SetFocus

I prefer the above since it implies that I am working with a control, and
you also get inteli-sense as you type it. Further, if you miss type the
name, and compile your code, you get a error. With the docmd, you only get
the error at RUNTIME, and so the compiler can't check your syntax for you.
As developer, you no doubt compile your code after any changes you make, and
thus using methods of control are checked at compile time. (setfocus is a
method of the control called course, and this is checked for you during
compile time).

 

[Pat Dools]

Hi Albert,

Thanks for your response. One of the things I am trying to accomplish in
addition to checking for required fields and calling up a new record is to
give the user an informative error message if they try to enter a record with
identical 'key' field info. If I don't do the 'DoCmd.RunCommand
acCmdSaveRecord' (see code below), all the user sees is, 'You can't go to the
specified record'. The error message tells the user the exact problem
(duplicate key) when I have the following code. Is this a reasonably
efficient way to do this? Thx. (Code below):

Private Sub CommandOpenNewRecord_Click()
If Len(Me.site.Value & "") = 0 Then
MsgBox "You must enter a value into Site Number."
Me.site.SetFocus
ElseIf Len(Me.id.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Number."
Me.id.SetFocus
ElseIf Len(Me.ptin.Value & "") = 0 Then
MsgBox "You must enter a value into Patient Initials."
Me.ptin.SetFocus
ElseIf Len(Me.course.Value & "") = 0 Then
MsgBox "You must enter a value into Course."
Me.course.SetFocus
ElseIf Len(Me.data1_init.Value & "") = 0 Then
MsgBox "You must enter a value into Data Entry 1 Initials."
Me.data1_init.SetFocus
Else
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord acDataForm, "FCycUrinalysis", acNewRec
Call ClearControls(Me)
DoCmd.GoToControl "course"
End If
End Sub

 

[Albert D.Kallal]

If I don't do the

DoCmd.RunCommand acCmdSaveRecord'

(see code below),

The above is fine, however, in your original example, you have NO SUCH CODE.
You have:

DoCmd.Save acForm, "FCycDoseVitalAmp"

I think is it QUITE important to point out that the above line DOES NOT save
the data, but executes a form save. I don't think that is what you wanted,
and in fact your new posts shows complete different code, and has no such
line as a above.

The rest of you code seems to look fine. When you move to a new record,
typically all of the controls are blanked out for you, so I am not sure if
you need

Call ClearControls(Me)

However, it is possible that you have need for the above, and it might do
other things. (and, if it does..then so be it..and you need it!!).

So, you code is now fine, but as oringally posted, you were saving the
form..and not the data...

Improvments? Hum...

I use the following "general" code routine to give custom messages for
fields that are not
filled out.

The code below is a great way to verify fields that you want to be requited.

Another nice feature is that after the message, the cursor moves to the
field in question.

The code is used as follows:

in the forms before update event..you go:

Cancel = MyVerify.

And, then the two following routines need be put into the forms module.

You
can see how in the first example, you just put in the list of controls that
you want requited, and also the text "error" message to display. Note
carefully how the full string is enclosed in quotes.

So, you put in the fieldname,Error text

Private Function MyVerify() As Boolean

Dim colFields As New Collection

MyVerify = False

colFields.Add "TourDate,Tour date"
colFields.Add "Description,Description"
colFields.Add "City,City"
colFields.Add "cboProvince,Province"
colFields.Add "StartDate,Start date"
colFields.Add "EndDate,end date"

MyVerify = vfields(colFields)


End Function

Private Function vfields(colFields As Collection) As Boolean

Dim strErrorText As String
Dim strControl As String
Dim i As Integer

vfields = False

For i = 1 To colFields.Count
strControl = Split(colFields(i), ",")(0)
strErrorText = Split(colFields(i), ",")(1)
If IsNull(Me(strControl)) = True Then

MsgBox strErrorText & " is required", vbExclamation, AppName
Me(strControl).SetFocus
vfields = True
Exit Function
End If
Next i


End Function