Cancel unload No currrent record (1 Viewer)

[ClaraBarton]

I run a check before closing the form to make sure 3 fields have data. The form has subforms so I preload a new record with autonumber.

Everything I read says that data checking belongs in the before update event.
However, a preloaded record with no data is not dirty so it misses the before update and I get empty records.
Using unload.. cancel returns with "no current record if the unload is canceled."
What is the proper way of canceling a close and fixing the record?

 

[theDBguy]

Can you show us exactly how you "preload" the subform? Thanks.

 

[Gasman]

So make the record dirty when you preload. though I do not think preloading is such a good idea?

 

[ClaraBarton]

No no... I'm talking about preloading a new record.

Dim rst As DAO.Recordset
Dim strLinkCriteria
strLinkCriteria = "[ContactsID]=" & NewContactID()
Me.Requery
    
'reposition form to new record
Me.FilterOn = False
Set rst = Me.RecordsetClone
      rst.FindFirst strLinkCriteria
        If Not rst.EOF Then
            Me.Bookmark = rst.Bookmark
        End If
 
rst.Close
Set rst = Nothing

    Me.FirstName.SetFocus

Nothing needs to be updated if the user closes the form. But other fields are still empty.

 

[ClaraBarton]

Really? How do I make it dirty? Just say it is?

 

[Gasman]

I do not see how that creates a new record?
Yes, I believe so. I have only ever used If me.dirty then me.dirty = False

 

[ClaraBarton]

Sorry! missed part of it:

Public Function NewContactID() As Long
On Error GoTo Error_Handler

'This function creates a new Contact record and returns the key.

    Dim db As Database
    Dim RS As Recordset
    Dim intID As Integer
    
    Set db = CurrentDb
    Set RS = db.OpenRecordset("Contacts")
    
    intID = DMax("ID", "Contacts") + 1
    'Add the record, storing new key value as variable and
    'passing it out as the function name
    With RS
        .AddNew
        NewContactID = RS!ContactsID  'this is the internal ID
        !FirstName = "(New)"
        !ID = intID  'this is Fred's ID; not contactsID
        .Update
'        .Bookmark = RS.LastModified
        .Close
    End With
    
    Set RS = Nothing

 

[Pat Hartman]

I run a check before closing the form to make sure 3 fields have data. The form has subforms so I preload a new record with autonumber.

Everything I read says that data checking belongs in the before update event.
However, a preloaded record with no data is not dirty so it misses the before update and I get empty records.
Using unload.. cancel returns with "no current record if the unload is canceled."
What is the proper way of canceling a close and fixing the record?

NEVER save "empty" records. Why are you "preloading" anything?

Validation belongs in the BeforeUpdate event. the unload event is too late. The bad data has already been saved. Validating after a record has been saved is just plain silly.

 

[The_Doc_Man]

I understand that the pre-load is necessary to prevent orphan-record formation. No problems with that. However, the idea that sometimes the pre-loaded record isn't needed can trip you up.

When you do the unload, delete the record. BUT when it gives you the error "no current record" - that should be a trappable error that you can simply dismiss. I think it is 3021, but you could just do an experiment there to make it print out the error number and thereafter, just test for that number in your error handler and ignore it. Don't cancel the unload. Just cancel the error.

 

[theDBguy]

Sorry! missed part of it:

Public Function NewContactID() As Long
On Error GoTo Error_Handler

'This function creates a new Contact record and returns the key.

    Dim db As Database
    Dim RS As Recordset
    Dim intID As Integer
 
    Set db = CurrentDb
    Set RS = db.OpenRecordset("Contacts")
 
    intID = DMax("ID", "Contacts") + 1
    'Add the record, storing new key value as variable and
    'passing it out as the function name
    With RS
        .AddNew
        NewContactID = RS!ContactsID  'this is the internal ID
        !FirstName = "(New)"
        !ID = intID  'this is Fred's ID; not contactsID
        .Update
'        .Bookmark = RS.LastModified
        .Close
    End With
 
    Set RS = Nothing

Looks like you're creating a record (.AddNew) before presenting it to the user in the hopes that they will fill out the rest of the information, but you get stuck with an empty record if they don't. So, one solution should be to not create the record first. Instead, just navigate the form to a new blank record and let the user decide whether to fill it in or not. As for the required or calculated fields, you can either populate them as soon as the form gets dirtied or use the BeforeUpdate event.

PS. By the way, you can "preload" a new record without dirtying the form, which means you won't get empty records, by simply using the DefaultValue property.

Sent from phone...

 

[ClaraBarton]

If Me.FirstName = "(New)" Then
         Me.Dirty = True
    End If

this does not work. You can't tell a form it's dirty.

 

[Pat Hartman]

I understand that the pre-load is necessary to prevent orphan-record formation. No problems with that. However, the idea that sometimes the pre-loaded record isn't needed can trip you up.

No it isn't.

In the BeforeInsert event of the subforms, you simply check to see if the parent record has a PK. If it doesn't, you cancel the event and undo the typing. No orphans. No validation errors.

It is amazing what you can control with minimal code when you know what form events are used for.

 

[Gasman]

If Me.FirstName = "(New)" Then
         Me.Dirty = True
    End If

this does not work. You can't tell a form it's dirty.

Really?

Private Sub Command23_Click()
Debug.Print "Dirty is " & Me.Dirty
Me.txtSteps.SetFocus
Me.Dirty = True
Debug.Print "Dirty is " & Me.Dirty
End Sub

Dirty is False
Dirty is True

 

[Pat Hartman]

Why, why, why, do you want to save a record that is "empty" except for the ID?

 

[ClaraBarton]

I don't. That's what I'm trying to fix.

 

[theDBguy]

I don't. That's what I'm trying to fix.

Did you see my post?

 

[ClaraBarton]

I saw it. Have no clue how to implement it. I want a number in addition to the autonumber. I need to go back to the drawing board and research to figure it out. Takes different code. Thanks for your help. Oh wait... dbguy! thought I was replying to PH. I'm on it.

 

[Pat Hartman]

@ClaraBarton just stop dirtying the record. Use the code I suggested in the subform's BeforeInsert event to keep users from trying to add records before the parent record has been added. This is a relational database. The database engine will Not allow you to add orphans if you enforce RI but you will get an obscure error message. Put you validation code in the correct event to ward off the obscure "no parent" error message.

Your current method is wrong. It is allowing the "empty" record to be saved.

 

[ClaraBarton]

My reasoning (obviously bad) is that it's a contact db. A phone call comes in and quickly the user wants to put notes in the subform. Doesn't work well without a number. How would you handle this? More info is forthcoming through this call but not immediately...

 

[Gasman]

When I worked in a call centre I had to find the parent record first?, using various search data.