Make record only on "ADD" button.. Urgent..

[Fred]

Hi.. I'm having a problem with my form.. It has 3 textboxs connected to a
table.. On the form options "data entry" = yes..
The problem i have it's that i made this form to add those 3 field to a
table and i only want to add them when the user clicks the "ADD" button.. but
what is happening is that automaticly when the user just clicks one of those
3 textboxs, not even needs to write something, if i close it on the X (its a
popup form) or if i click the "CANCEL" button when i check the table a new
registry was made.. I dont want that.. i only want the record to be made when
the user clicks the "ADD" button with this code:

Private Sub Command1_Click()
If IsNull(DLookup("Park", "Query_park")) = False Then
MsgBox "Park already created!"
Else
If IsNull(parkname) = True Or IsNull(area) = True Or IsNull(Panel) = True Then
MsgBox "ERROR:Fill every field"
Else
If Me.Dirty Then Me.Dirty = False
DoCmd.RunCommand acCmdRecordsGoToNew
MsgBox "Parque adicionado!"
DoCmd.Close
DoCmd.OpenForm "Form2"
End If
End If
End Sub

How can i solve this problem??
I cant have blank records or Wrong records on the table connected to this
form because on other form i have a combobox looking up for the "parkname"
column of the table..

Thanks
Nuno

 

[BruceM]

The best place to validate is typically in the form's Before Update event.
The trouble with the Click event of a command button is that it will present
the user with the message box about filling every field, but after the user
clicks OK the data will be written to the fields anyhow. The record is
saved when the user goes to a new record, closes Access, closes the form,
clicks a subform, and maybe for a few other reasons I can't bring to mind
just now.
I'm not sure just what you are trying to check with the DLookup, but I doubt
it's what you think it is. As it stands you are checking whether there is
any record in Query_Park with a value in the Park field.
You can simplify the syntax of IsNull. In this example I have added an
example of a criteria statement so that you can check whether a park of the
same name as in this record has been created:
If Not IsNull(DLookup("Park", "Query_park","ParkName = " & Me.ParkName))
IsNull ... = False or IsNull ... = True will work, but it's extra code you
don't really need.

The form's Before Update event has a Cancel event. If the conditions aren't
met you can Cancel the update. In the command button Click event you could
save the record:

Private Sub Command1_Click()

If Me.Dirty Then Me.Dirty = False
Me.Recordset.AddNew

End Sub

You can also use:
DoCmd.GoToRecord , , acNewRec instead of
Me.Recordset.AddNew

In the form's Before Update event:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.ParkName) Then
MsgBox "Park Name is needed"
Me.txtParkName.SetFocus
Cancel = True
ElseIf IsNull(Me.Area) Then
MsgBox "Area is needed"
Me.txtArea.SetFocus
Cancel = True
ElseIf IsNull(Me.Panel) Then
MsgBox "Panel is needed"
Me.txtPanel.SetFocus
Cancel = True
End If

End Sub

The above is a user-friendly version. You could also do:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.ParkName) Or IsNull(Me.Area) Or IsNull(Me.Panel) Then
MsgBox "Fill in all fields"
Cancel = True
End If

End Sub

In either case, note that the Before Update event can be cancelled. You
can't cancel a Click, which has already happened by the time the code runs.
You can exit the sub, but that doesn't stop the user from saving the record
by other means, as I described.

There are many other ways to approach validation (checking for required
fields, etc.). Some are user-friendly but more compact in terms of code
than my first example, but they require more up-front explanation. For
checking just three fields it doesn't make much difference.

 

[BruceM]

I need to issue a bit of a correction. You can't cancel a Click event, but
you can cancel a Double Click event. I have to admit I don't follow the
reasoning, if any, behind this. I mention this because of the reason I gave
(it has already happened) for the Click event having no Cancel argument. If
that was true, the Double Click event could have no Cancel argument either,
it seems to me, so there must be something else going on, even if that
something is an arbitrary decision somewhere along the line.