CODEWITHSUNDEEP
formsms-accessevents
clum
clum

Reputation: 489

Initializing record default values in a Current event

I would like to automatically provide default values for the user when inserting a new record. It is important to me that these defaults should be provided when the user wishes to start entering data into the record, not only after the record is finished.

This question is basically the same as Field default value from query in MS Access, except that there the default values are based on another table, while in this case I want the defaults to be filled in based on the last created recorded, which doesn't work as user7116 pointed out in his comment.

(Clarification: The method mentioned there, to use the form control's default value, does work initially. The problem is that when the user dirties the new record, then access already sets the defaults for the next, future, new record. The problem is that at that point, the first new record didn't yet exist, and therefore the defaults will be based on the record before that, because that will be what Dlookup and DMax will see.)

I tried using the Current event of a form to initialize the default values:

Private Sub Form_Current()
    If Form.NewRecord Then
        Clinic = DLookup("Clinic", "Invoices", "InvoiceID = " & DMax("InvoiceID", "Invoices")) 'Default to the same clinic as the last record
        [Tax receipt number] = DMax("[Tax receipt number]", "Invoices") + 1 'Basically autonumber, but editable
    End If
End Sub

This works beautifully, that is, except if the user deletes the last record on the form, which doesn't succeed in deleting the record. My experimentation revealed that the Current event for the next record (which, in this case, is a new record) is called even before the BeforeDelConfirm event (despite the implication of the documentation that the Current event doesn't "occur until all the records are deleted"), and I guess editing the record prevents the deletion from being carried out.

How should I get around this? Is there a way to detect from the Current event that it is in the middle of deleting a different record, and shouldn't interrupt it?

Upvotes: 0

Views: 116

Answers (2)

Albert D. Kallal
Albert D. Kallal

Reputation: 49169

Are you only to have such code and setup run for a new record, or do you want such code to run when users edit existing records?

A HUGE detail you left out.

If you want such code ONLY for new records, then your code should be placed in the forms before-insert event. This event ONLY fires when adding a new record (so if a user goes to a new record, but does nothing and does not type, then they can exit the form, or navigate away and some blank record will not be created nor added.

If you want such code to run and setup values for already existing records, then move the above code from the before-insert to the forms on-dirty event.

You do NOT want to use the on-current event, since that fires when a user is just browsing along and NOT editing data.

Upvotes: 0

Gustav
Gustav

Reputation: 55921

You can "reverse" the process:

Private Sub Form_AfterUpdate()
    Clinic.DefaultValue = DLookup("Clinic", "Invoices", "InvoiceID = " & DMax("InvoiceID", "Invoices")) 
    [Tax receipt number].DefaultValue = DMax("[Tax receipt number]", "Invoices") + 1 
End Sub

Upvotes: 1

Related Questions