Command button with conditions

[Geo]

Can you please, tell me how can I make a cmdbutton in a form to save a
record in a table ONLY if NO ONE of the fields (<=> no one of the text
boxes or combo boxes from the form used to introduce data in the table) is
null, eventually to display a custom message, and to delete the respective
row if I am exiting from this form without pressing the button "SAVE" first
to save the record?

Thank you in advance!
Geo

 

[Pieter Wijnen]

Requires Single Row Form

Sub btnSave_Click()
Dim C As Access.Control
For Each C In Me.Controls
If TypeOf C is Access.TextBox Or TypeOf C is Access.ComboBox Then ' Or
Access.Checkbox etc
If (Len(Nz(C.Value,VBA.vbNullstring)) = 0) And
(VBA.inStr(C.Tag,"Required")> 0) Then ' Handle Nulls & strings of length 0
for all required fields
#If StayOnForm Then ' Compiler directive
MsgBox "Field: " & C.Name & " is Required!"
C.SetFocus
Exit Sub
#Else ' Clear & Close
Me.Undo ' Cancel changes
Access.DoCmd Close ,Access.AcForm, Me.Name
Exit Sub
#End If
End If ' IsNull
End If ' TypeOf
Next 'C
Me.Dirty=False ' Saves!
Access.DoCmd Close ,Access.AcForm, Me.Name
End Sub

Sub btnCancel_Click()
Me.Undo
Access.DoCmd Close ,Access.AcForm, Me.Name
End Sub

HTH

Pieter

 

[Albert D. Kallal]

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

However, I am at a loss about the code deleting a record without prompting
the user, or even telling the user that you deleting a record?

It seems strange that ms-access automatically saves data for you, but you
now want to prompt the user to save, but actually delete records without any
kind of prompts? (sounds like you got this thinking backwards????). Access
usually prompts you when you are about to delete data..but always saved for
you automatically.

I would suggest that you put a delete button on your form, or at least in
the menu bar (if you are making a custom one).

If the user opens a form with a new record, that record is NOT created until
they type some data, so if you close the form, then you don't need to delete
the record...as none will have been crated.

However, you seem to be telling me that a user can enter a whole bunch of
data..and then if they close the form, you just go ahead and delete this
form, and they loose all their changes? Huh? What happens when they are in a
hurry, and hit the X button to close the form, and forget to hit the save
button? Surely you can't tell me you now want to delete all this data? You
mean they fill out all data except for ONE required field..and the whole
mess gets thrown out? You are kidding..right? What about editing a existing
form? You mean if they remove data from one field...now if they close and
don't hit save...you delete the data? You will have NOTHING but constant
complaints from your users about data being lost, or deleted if you do this!

Anyway, I will let you figure out some kind of delete button stuff..as the
above don't quite make sense. If the user wants to exit a form with required
fields, the either force them to enter those required fields, or give them a
delete button to throw the whole thing out.

The code below is a great way to verify fields that you want to be requited.
If a user does not correctly fill out all the required fields..they can't
save, or exit the form. And, if the user does not want the data..then they
should be forced to use a delete button.

The code is used as folows:

in the forms bedore update event..you go:

Cancel = MyVerify.

And, then the two follwing rouintes need be put into the forms module. You
can see how in the first example, you just put in the list of contorls that
you want requited..and also the text "error" message to dispaly. Note
carefully how the full string is enclosed in quotes..


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

 

[Brian]

I think you can accomplish both things by making an Exit button that runs the
following code. It requires Single Form, not Continuous. Also disable the
system Close button for the form and turn off the navigation buttons;
otherwise, the user could bypass this by just moving forward to the next
record.

Private Sub ExitButton()
If IsNull(Control1) or IsNull(Control2) (etc. for all controls) then Me.Undo
Docmd.Close
End Sub

This will clear all fields if any are null but will save the record
automatically upon closing if all are populated.

If you want to notify the user of which box is incomplete, try this instead
(Form_BeforeUpdate):

Private Sub Form_BeforeUpdate()
If Me.Dirty then 'prevents checking on empty/unchanged records
If IsNull(Control1) then
MsgBox "The record cannot be saved because...", vbExclamation, "Cannot
save"
Cancel = True
Exit Sub
End if
If IsNull(Control2) then
MsgBox "The record cannot be saved because...", vbExclamation, "Cannot
save"
Cancel = True
Exit Sub
End if
End If
End Sub

This will check to ensure that Control1 & Control2 (etc.) are not null
before saving and cancel the save if any one is. Then, just add a Cancel
Button with the command Me.Undo to allow the user to cancel entry prior to
exiting the form.

 

[Geo]

Thank you!

 

[Grahammer]

Your welcome?