Cancel unload No currrent record

[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

 

[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.

 

[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

 

[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.

 

[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.

 

[Gasman]

I seem to recall in NTL, we had a mainform and subform for notes, so we could see what went on before. Double clicking a subform record would show whole record as pop up.

However that was not an Access DB.

 

[ClaraBarton]

I'm not completely in left field here. I did this a long time ago and I got my information from Microsoft Access 2010 Programmer's Reference. That's where I got most of my stuff. Teresa Hennig, Rob Cooper, Geoffrey Griffith, Jerry Dennison. I assume they know their stuff. I got it fixed. I went back to the book and read the section again. Moving on and thank you for your help.