Trying to understand NewRecord

[GraeagleBill]

I have a single record form with RecordSource query containing several records. I've run the OnCurrent event in Debug and found the Me.NewRecord = True. I ran the statement If Me.Dirty then Me.Dirty = False in the belief that a control bound to AutoNumber RecordID field would become set the the record ID of a new record could be obtained, not so. What needs to happen? (I realize I could create a DAO recordset and use AddNew and create a new record, but I shouldn't have to......... right?)

[ranman256]

I find .dirty property useless and dont ever use it.
but, It is a new record until you save it and then the ID is created, then not new.

the query can add new records, so you dont need DAO.

[Missinglinq]

A Record doesn't become a Record until at least one character is entered into a Control on the Form.

Why do you want to Save a Record that only has the Autonumber Control populated?

If you absolutely have to do this kind of thing...I think your only approach would be to use an auto-incrementing hack to populate the Control/Field, rather than an AutoNumber.

Linq ;0)>

[GraeagleBill]

A Record doesn't become a Record until at least one character is entered into a Control on the Form.

That's the answer to my question. Thank you.

[Missinglinq]

Glad we could help!

Linq ;0)>

[GraeagleBill]

This issue is still giving problems. I do in fact obtain a new record ID as soon as one of the bound controls is set. However, that action alone DOES NOT make the new record THE CURRENT record. As soon as I add to another control on the form I get ANOTHER new record, clearly that's not what's needed.

I thought perhaps Me.Bookmark = Me.RecordsetClone.Bookmark would do the trick but that fails.

[Micron]

Open a table with autonumber id. Start typing in a field and note what happens to the autonumber field. Now cancel the record edit (esc key). Click off then back on that new record. Start a new edit. What happens in the autonumber field now? Same number?
Thus a record doesn't become a record until a field edit is performed AND you move off of the record. You can't even add a record via DAO unless you replicate those actions in code, which is a puzzle as to why you'd want a record with only an autonumber in it - especially if you plan to use it for something, which we know you should not, right?

[GraeagleBill]

I don't want a record with only an autonumber set. Either I'm not asking the question correctly or I'm lacking something. I have a form. That form has a table (query on a table) as its RecordSource. That form has a number of bound text boxes. I add text to one of those text boxes and that causes a new record to be created with the newly entered text in the corresponding bound field. Then, I type into another of the bound text boxes on that same form and rather than that text being added to the record already created it causes another new record to be created. The implication is that for every bound text box on the form I type into I'm going to get a new record each time.

I know I'm getting fuzzy in the brain but this is nuts!

Do I have something messed up in Properties?


BTW, I did go through the exercise you outlined and once I entered data into a field the autonumber set as expected and stayed that way no matter what I did.

[Micron]

As soon as I add to another control on the form I get ANOTHER new record

If you are entering data and leaving the control and staying on the same record (you said it was a single record form) and that is saving a record AND moving to a new one then you must be doing something in code to cause that move or record commitment. But, not sure what you mean by "new record"? Same table, new record? Or a new record in another table that's involved in a multi table updatable query? I also can't figure out how you'd get a new record on the same single record view form unless your code is the cause.

that action alone DOES NOT make the new record THE CURRENT record.

If you're editing a record, it has to be the current record, even if technically it hasn't been committed yet. What else can it be?
Note, that is not the same as the Current event. You say the current event is executing, and there you try to save with Me.Dirty = False? That would not save an autonumber id as I pointed out, which at the risk of repeating myself, you would have to enter data into at least one bound field first - then commit the record somehow. You might be able to get the Text property value of the ID control, but probably not its value as it hasn't been saved just because you started a new record.

control bound to AutoNumber RecordID field would become set the the record ID of a new record

Don't understand your comment about the test. My point was, if you esc (don't save) the next autonumber is not +1 more than the last. The one you had is gone forever because the record was never saved. Not even editing a field on that record will make it a record unless you do something to cause it to be committed. It may be erroneous to refer to this "in progress" record as the current record, but I guess we have to make some allowances.
Maybe you should post a db copy and instruct on how to recreate the issue vs what should be happening if there's still a problem. Sorry if I'm not understanding what you're saying.

[GraeagleBill]

In the hopes that walking through exactly what I'm experiencing, I'm including a snippet of screenshot that depicts how the form looks and what code runs behind as I proceed. With the design view added, you can see the control binding.


When I enter text in the "Family Name", the AfterUpdate gets control and the following code runs. As the code runs, I Msgbox the record ID to verify that a record has been created. After the code runs and the combo dropdown is selected, the newly entered text in the "Family Name" control becomes blank and the focus moves to the POBox field. If I enter a value there, another record is created. My intend, of course, is to populate a single record with all the values that would be entered in each of the controls. The code references record fields "FamilyID" and "FamilyName" correctly, leading me to believe erroneously that the new record had become the Current Record.

Private Sub tbFamilyName_AfterUpdate()

If bolNewFamily = True Then
    If ParseFamilyName = False Then
        MsgBox "Sorry, but you must enter a unique family name of the form:" & vbNewLine & _
               "LastName,FirstName OR, if a Pseudo family is being established" & vbNewLine & _
               "then just prefix the name of your choice with ""*-"". "
        Exit Sub
    End If
End If

MsgBox "FamilyID = " & FamilyID      'Did the autonumber "fire"

If bolPseudoFamily Then
    If MsgBox("The addition of a new ""Pseudo"" family has been detected" & vbNewLine & vbNewLine & _
            "If one chooses to associate this family with one of the" & vbNewLine & _
            "established TMS Groups, it must be done at this juncture." & vbNewLine & vbNewLine & _
            "Further, it must be understood that one cannot change the" & vbNewLine & _
            "Group once the association is established, which occurs" & vbNewLine & _
            "upon entry of the Head-of-Household.", vbOKCancel) = vbOK Then
        
        Me.lblAssoc.Caption = "Select Desired Group:"
        Me.lblAssoc.Visible = True
        
        Me.cboGroups.Visible = True
        Me.Repaint
        Me.cboGroups.SetFocus
        Me.cboGroups.Dropdown

        bolShowGroups = True
    End If
End If

End Sub

Private Function ParseFamilyName() As Boolean
Dim strName() As String

ParseFamilyName = False

If Left(FamilyName, 2) = "*-" Then    'New pseudo family name?
    If Len(FamilyName) > 2 Then       'Make sure more than just the prefix
        bolPseudoFamily = True        'Okay to proceed
        ParseFamilyName = True
    End If
    
Else
    strName = Split([FamilyName], ",")
    If UBound(strName) = 1 Then
        strLName = strName(0)
        strFName = strName(1)
        FamilyName = strLName & "," & strFName
        ParseFamilyName = True
    End If
End If

End Function

Could Enter Key behavior on the "Family Name" when entered be causing another record to be created? The form's Cycle Property is already set to "Current Record", but still I wonder.

[Micron]

As the code runs, I Msgbox the record ID to verify that a record has been created.

Again, no. Your message only proves that the control/field was updated, provided that the control is bound (which we know it is).
The record is not saved until action is taken to commit (save) it.

If I enter a value there, another record is created.

Again, no - unless you're dealing with a different table, and then yet again, not until that record is committed.

A record you start is not a record until something causes it to be saved/committed.
I'm getting lost as to whether or not you still have an issue. You don't seem willing to post a db so we can remove any doubt about what's going on, so I'm not sure what else I can do or say. I may be missing your point but I also think that you're not absorbing the message. Or maybe it's your usage of certain terms, like "current" for example.

[GraeagleBill]

I think we're almost there.

The record is not saved until action is taken to commit (save) it.

Okay, the "action" of exercising a selection from the combo, the dropdown you see within the code posted in #10, does in fact caused the record identified by the autonumber shown with the Msgbox to be committed. The question now becomes how do I make that record the "Current" record so that the bound controls become bound to that record? As it is, subsequent entry of data into the other controls begins the initial process of creating another record. Maybe add a statement like "Me.AllowAdditions = False" after the combo selection??

I use the term Current to refer to the single record among the current RecordSource that is to receive the data entered in the bound controls. Obviously, or not, I have always assumed that when an autonumber is advanced that a record was added to the corresponding table.

I don't post the db only because it's a pain in the posterior. I would expend the effort only as a last resort.

[GraeagleBill]

NO NO NO NO!!!!!!

Some idiot put a Me.Requery in the OnClick event for the combo! Without that, the record HAS NOT been committed. So, I think, the question becomes a more simple one. What "action" (code statement) will cause the record to commit AND then become the current record? I do know the newly advanced autonumber, so if I code "DoCmd.RunCommand acCmdSaveRecord" followed by Me.RecordsetClone.Find "my new recordID" should that do the trick?

[GraeagleBill]

I haven't thoroughly test this code, but I think it solves this issue. I'll post "SOLVED" later if it does

Private Function CommitNow(ID As Integer) As Boolean
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  A new family named "scrubbed" so we can commit the new record and then make it the
'  current record.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*

On Error GoTo CmtErr:

DoCmd.RunCommand acCmdSaveRecord
Me.Requery

Me.RecordsetClone.FindFirst "[FamilyID] = " & ID
Me.Bookmark = Me.RecordsetClone.Bookmark

tbFamilyName.SetFocus
Me.AllowAdditions = False

CmtErr:
    MsgBox "Error attempting to save new family" & vbNewLine & _
           "Error number: " & Err.Number & vbNewLine & _
           "Error description: " & Err.Description
    CommitNow = False
    Exit Function

End Function

[GraeagleBill]

I haven't thoroughly test this code, but I think it solves this issue. I'll post "SOLVED" later if it does

Private Function CommitNow(ID As Integer) As Boolean
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  A new family named "scrubbed" so we can commit the new record and then make it the
'  current record.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*

On Error GoTo CmtErr:

DoCmd.RunCommand acCmdSaveRecord
Me.Requery

Me.RecordsetClone.FindFirst "[FamilyID] = " & ID
Me.Bookmark = Me.RecordsetClone.Bookmark

tbFamilyName.SetFocus
Me.AllowAdditions = False

CmtErr:
    MsgBox "Error attempting to save new family" & vbNewLine & _
           "Error number: " & Err.Number & vbNewLine & _
           "Error description: " & Err.Description
    CommitNow = False
    Exit Function

End Function