Problems attempting to add new record to table

[Ken Sheridan]

However, when the form closes the new record DOES NOT SAVE..

One scenario in which that would happen is where all controls in the form bound to Required columns have values set as their DefaultValue property, and the form is closed without amending any of the values or inserting a value into any other control. To accept the default values without amendment assign the value of any one of them to itself. That will Dirty the form and force the record to be saved.

[GraeagleBill]

None of the fields are required.

[davegri]

Another sneaky possibility that can skip a form's update to a table is the form's before_insert event. This event is often used to validate data to prevent garbage updates. It is always called before a new record is created by the form and improper coding in the event can completely kill the update.

[GraeagleBill]

Nope, Before_Insert not active.

BTW, form works fine updating an existing record:

Private Sub Form_DblClick(Cancel As Integer)


DoCmd.OpenForm "frmItemDescriptor", , , , acFormEdit, acDialog, ItemID


End Sub

So, at almost 10PM Sunday night, I got curious to see if I could brut force add a record with the AddNew but was greeted with another 3022 error when I hit the ".Update" statement, something I had previously tried when I was hosting two different tables. Now, I'm just trying to add a record to the form's RecordSource.

Private Sub lblSAVE_Click()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' Okay, everything required to enter a new record has been satisfied, copy fields to our main table.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim rsSav As DAO.Recordset
Dim strTemp As String


If strMode = "New" Then       'Current RecordSet is QInvNew
    Set rsSav = DBEngine(0)(0).OpenRecordset("tblInv")
    With rsSav
    
        .AddNew
        !Description = Me.tbItemDescription
        !Keep = Me.chkKeep
        !Donate = Me.chkDonate
        !Sold = Me.chkSold
        !Sell = Me.chkSell
        !Comment = Me.tbComment
        !Im1 = Me.tbImage1
        !Im2 = Me.tbImage2
        !Im3 = Me.tbImage3
        !Im4 = Me.tbImage4
        !Im5 = Me.tbImage5
        !Im6 = Me.tbImage6
        .Update
        
    End With
    
rsSav.Close
Set rsSav = Nothing
End If


DoCmd.Close acForm, "frmItemDescriptor"


End Sub

[Welshgasman]

Can you add a record directly via the table interface?

[GraeagleBill]

Can you add a record directly via the table interface?

I was also beginning to suspect a corrupted back-end DB, or at least the tblInv itself.

1) So NO, my attempts to add a record was showing an autonumber that was an already used ItemID.
2) There are only 48 records in the tblInv table, a new addition autonumber should have been 49 but the addition attempt was showing 15.
3) A compact and repair followed by another attempt to add was successful.
4) Normal record addtions via the form UI were also successful with the code path shown in #18 blocked.

Pure supposition on my part, but I think a previous Update Query on the table left it corrupted.

One lingering question: How would the main form know that its Recordsource has a new record added via a subordinate form?

[Welshgasman]

Store record count before and after and compare?

[GraeagleBill]

Store record count before and after and compare?

GOOD GRIEF! Another instance of the obvious!

All you young'ns need to prepare yourselves for your abilities for the obvious. The aging brain is not always a happy place.

[pbaldy]

Post 8 was moderated, I'm posting to trigger email notifications. Sorry for being slow, was away from my computer over the weekend.

[Welshgasman]

GOOD GRIEF! Another instance of the obvious!

All you young'ns need to prepare yourselves for your abilities for the obvious. The aging brain is not always a happy place.

At 71, I would hardly class myself as a 'young'n'.

[GraeagleBill]

At 71, I would hardly class myself as a 'young'n'

Got ya beat by 19 years


BTW, surprised that this code didn't work:

Private Sub cmdNewItem_Click()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  User wants to add a new item to the inventory.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim lngCount As Long


lngCount = Me.RecordsetClone.RecordCount
DoCmd.OpenForm "frmItemDescriptor", , , , , acDialog
Me.RecordsetClone.MoveLast
If Me.RecordsetClone.RecordCount > lngCount Then Me.Requery


End Sub

My final solution:

Private Sub cmdNewItem_Click()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  User wants to add a new item to the inventory.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim lngCount As Long


lngCount = DCount("*", "QInv")
DoCmd.OpenForm "frmItemDescriptor", , , , , acDialog
If DCount("*", "QInv") > lngCount Then Me.Requery


End Sub

[Welshgasman]

I would expect you would need to MoveLast for the first count?

[GraeagleBill]

I would expect you would need to MoveLast for the first count?

No, still does not Requery:
[CODE]
Private Sub cmdNewItem_Click()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=* =*=*=*=*=*=*=*=*=*=*=*=*=*=*
' User wants to add a new item to the inventory.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=* =*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim lngCount As Long


'lngCount = DCount("*", "QInv")
'DoCmd.OpenForm "frmItemDescriptor", , , , , acDialog
'If DCount("*", "QInv") > lngCount Then Me.Requery


Me.RecordsetClone.MoveLast
lngCount = Me.RecordsetClone.RecordCount
DoCmd.OpenForm "frmItemDescriptor", , , , , acDialog
Me.RecordsetClone.MoveLast
If Me.RecordsetClone.RecordCount > lngCount Then Me.Requery


End Sub


[CODE]

[Micron]

You're assigning recordsetclone.count to the variable lngCount then asking if one is greater than the other. How would that be possible? I don't see why the number would change with successive MoveLast moves on the same recordsetclone, which doesn't look like it's necessary to me.

[GraeagleBill]

Because form "frmItemDescriptor" might create a new item and the record count would increase as a result. If so, I need to Requery so the user can see the new addition.