Deleting blank records when aform is closed or a button is click.

[Ayo]

We are have a somewhat of an annoying problem with one of our database forms.
Everytime we open the form it creates a new record, even if you close the
form or cancelled it. We are trying to figure out a way to not create a blank
record in our database everytime the form is opened. I tried:

DoCmd.RunCommand acCmdDeleteRecord
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "Invoice Database Launch", acNormal

but the blank record is still showing up. So, is there a way to tell access
not to create a record if the form is close or the cancel button is clicked.
Thanks

 

[Dirk Goldgar]

We are have a somewhat of an annoying problem with one of our database
forms.
Everytime we open the form it creates a new record, even if you close the
form or cancelled it. We are trying to figure out a way to not create a
blank
record in our database everytime the form is opened. I tried:

DoCmd.RunCommand acCmdDeleteRecord
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "Invoice Database Launch", acNormal

but the blank record is still showing up. So, is there a way to tell
access
not to create a record if the form is close or the cancel button is
clicked.

A form will not automatically create a new record unless there's code (or a
macro) that sets the value of some field on the form. Check for code in
your form's Open, Load, and Current events, or macros executed in those
events, that might be doing that.

Are you sure the form is really creating a new record, that it's not just
the normal "blank" record for additions that is being displayed? When you
look directly in the table, do you see these records?

 

[Ayo]

We only have a Current event that is suppose to execute only when there are
values in 2 of the txt boxes, see below
Private Sub Form_Current()
If Me.Date_Invoice_Received_In_A_P.Value <> "" And
Me.Date_Invoice_Received_In_Market.Value <> "" Then
Me.Days_to_Market.Value = DateDiff("d",
Me.Date_Invoice_Received_In_A_P.Value,
Me.Date_Invoice_Received_In_Market.Value)
Else
Me.Days_to_Market.Value = 0
End If
End Sub

This is the code that opens the form from the main form:
Private Sub CreateNewInvoice_Click()
DoCmd.Close acForm, "Invoice Database Launch", acSaveYes
DoCmd.OpenForm "Invoice Tracker New Entry", acNormal
Application.Forms("Invoice Tracker New Entry").Caption = "CREATE INVOICE"

DoCmd.GoToRecord , , acNewRec
End Sub

 

[Ayo]

Yes. The form creates a new blank record everytime you close it, even if all
the txt boxes and combo boxes are blank.

 

[Pat Hartman]

"" is a zero-length-string (ZLS) which is NOT the same as a null. Dates are
numeric values and therefore NOT strings. To determine if a date is empty,
use the IsNull() function rather than comparing to ""

If Not IsNull(Me.Date_Invoice_Received_In_A_P.Value) And

Not IsNull(Me.Date_Invoice_Received_In_Market.Value) Then

Another useful date function is IsDate() which in this case will allow you
to make a positive rather than a negative comparison.

If IsDate(Me.Date_Invoice_Received_In_A_P.Value) And

 

[Dirk Goldgar]

We only have a Current event that is suppose to execute only when there
are
values in 2 of the txt boxes, see below
Private Sub Form_Current()
If Me.Date_Invoice_Received_In_A_P.Value <> "" And
Me.Date_Invoice_Received_In_Market.Value <> "" Then
Me.Days_to_Market.Value = DateDiff("d",
Me.Date_Invoice_Received_In_A_P.Value,
Me.Date_Invoice_Received_In_Market.Value)
Else
Me.Days_to_Market.Value = 0
End If
End Sub

Yes, that would do it, all right. Every time you arrive at a new record,
you set the value of Days_to_Market, either to a value calculated from the
other text boxes, or to zero. That will happen whether you are on a blank
new record, or on an existing record.

At the very least, you should fix the date tests to check for Null rather
than "", and you should drop the Else clause that sets Days_to_Market to
zero. How about this:

If Not IsNull(Me.Date_Invoice_Received_In_A_P) _
And Not IsNull(Me.Date_Invoice_Received_In_Market) _
Then
Me.Days_to_Market = _
DateDiff("d", _
Me.Date_Invoice_Received_In_A_P,
Me.Date_Invoice_Received_In_Market)
End If

I would leave Days_to_Market as Null when either of those base dates is
unknown; however, you could have it *default* to zero by setting it's
Default Value property to 0.

But aside from all that, this looks a whole lot like storing a calculated
value. Is it possible for the value of Days_to_Market ever to have a value
that is *different* from the number of days between those two dates? If
not, there is not reason ever to store it, and several good reasons not to
(efficiency, reliability). So in all probability, you should just define
this as a calculated field, either in the text box's controlsource or as a
calculated field in the form's recordsource query. If you do it as a
calculated control, the controlsource would be:

=DateDiff("d", [Date_Invoice_Received_In_A_P],
[Date_Invoice_Received_In_Market])

As a calculated field in the query, you might enter something like this in
the query design grid:


Days_to_Market: DateDiff("d", Date_Invoice_Received_In_A_P,
Date_Invoice_Received_In_Market)

Note that both of the above expressions will have been broken onto two lines
by the newsreader, but they are intended each to be on a single line.

 

[Ayo]

Thanks pat for the correction. But my problem still persist.