Solved VBA blocking the saving of foreign key and line numbering in the subform and child table

[nector]

Hi

As I was concluding the pos , after now checking the actual receipts I was surprised to see that the receipts were coming without details lines. That forced me to use the audit software which revealed the following:

(1) The code below does a good thing to set focus the cursor after inserting a line in the subform and then set focus goes straight in a parent control called txtProductCode that is fine:

Effects or final results in the subform and child table:
(a) No foreign is inserted in the child table leaving the data lines inserted as orphans and no line number also.

'Parent form VBA code
Public Sub txtProductCode_AfterUpdate()
Dim lngProdID As Long
Dim sReturn$, varValue As Variant
If Not (IsNull(mID)) Then
 
    With Me![sfrmPosLineDetails Subform].Form.RecordsetClone
        .AddNew
     
        ![ProductID] = mID
        ![QtySold] = 1
     
        sReturn = DLookup("ProductName & '|' & vatCatCd & '|' & dftPrc & '|' & VAT", _
            "tblProducts", _
            "BarCode = '" & Me.txtProductCode & "'")
        varValue = Split(sReturn, "|")
     
        ![ProductName] = varValue(0)
        ![TaxClassA] = varValue(1)
        ![SellingPrice] = varValue(2)
        ![Tax] = varValue(3)
     
           
        ![RRP] = 0
     
        'immediately save the record
         .Update
        End With
 
End If
Me.TimerInterval = 100
End Sub

'Subform VBA code

'This is inserted in the control source in order to help create the lines numbering
txtPosition =[Form].[CurrentRecord]

Private Sub Form_BeforeInsert(Cancel As Integer)
'itemesID represents lines numbering in the subform and must be saved on the child table
Me.ItemesID = Me.txtPosition
End Sub

 

[dalski]

With Me![sfrmPosLineDetails Subform].Form.RecordsetClone

This is sinister. You're opening the recordset with the option as dbOpenDynaset or dbOpenTable right? Not recordset clone, as that will not allow an edit/ save changes. Requery your form or your form's recordset after the update. If you requery the recordset the form will stay on same record & be sure to close recordset when done to avoid corrupting db.

frmName.Requery
frmName.Recordset.Requery
rs.close

Dunno what you're up to at the bottom there with:

Private Sub Form_BeforeInsert(Cancel As Integer)
'itemesID represents lines numbering in the subform and must be saved on the child table
Me.ItemesID = Me.txtPosition
End Sub

Setting a controls value with the position of a mouse-cursor it looks like. In which case you're not opening a recordset & altering data.

 

[nector]

You free

With Me![sfrmPosLineDetails Subform].Form.RecordsetClone

This is sinister. You're opening the recordset with the option as dbOpenDynaset or dbOpenTable right? Not recordset clone, as that will not allow an edit/ save changes. Requery your form or your form's recordset after the update. If you requery the recordset the form will stay on same record & be sure to close recordset when done to avoid corrupting db.

frmName.Requery
frmName.Recordset.Requery
rs.close

Dunno what you're up to at the bottom there with:

Private Sub Form_BeforeInsert(Cancel As Integer)
'itemesID represents lines numbering in the subform and must be saved on the child table
Me.ItemesID = Me.txtPosition
End Sub

Setting a controls value with the position of a mouse-cursor it looks like. In which case you're not opening a recordset & altering data.

With Me![sfrmPosLineDetails Subform].Form.RecordsetClone

This is sinister. You're opening the recordset with the option as dbOpenDynaset or dbOpenTable right? Not recordset clone, as that will not allow an edit/ save changes. Requery your form or your form's recordset after the update. If you requery the recordset the form will stay on same record & be sure to close recordset when done to avoid corrupting db.

frmName.Requery
frmName.Recordset.Requery
rs.close

Dunno what you're up to at the bottom there with:

Private Sub Form_BeforeInsert(Cancel As Integer)
'itemesID represents lines numbering in the subform and must be saved on the child table
Me.ItemesID = Me.txtPosition
End Sub

Setting a controls value with the position of a mouse-cursor it looks like. In which case you're not opening a recordset & altering data.

You are free to make changes on the attached db and see whether it will work

 

[nector]

With Me![sfrmPosLineDetails Subform].Form.RecordsetClone

This is sinister. You're opening the recordset with the option as dbOpenDynaset or dbOpenTable right? Not recordset clone, as that will not allow an edit/ save changes. Requery your form or your form's recordset after the update. If you requery the recordset the form will stay on same record & be sure to close recordset when done to avoid corrupting db.

frmName.Requery
frmName.Recordset.Requery
rs.close

Dunno what you're up to at the bottom there with:

Private Sub Form_BeforeInsert(Cancel As Integer)
'itemesID represents lines numbering in the subform and must be saved on the child table
Me.ItemesID = Me.txtPosition
End Sub

Setting a controls value with the position of a mouse-cursor it looks like. In which case you're not opening a recordset & altering data.

If you meant changing the code like below then does not work either

Public Sub txtProductCode_AfterUpdate()
Dim lngProdID As Long
Dim sReturn$, varValue As Variant
If Not (IsNull(mID)) Then
    
    With Me![sfrmPosLineDetails Subform].Form.RecordsetClone
        .AddNew
        
        ![ProductID] = mID
        ![QtySold] = 1
        
        sReturn = DLookup("ProductName & '|' & vatCatCd & '|' & dftPrc & '|' & VAT", _
            "tblProducts", _
            "BarCode = '" & Me.txtProductCode & "'")
        varValue = Split(sReturn, "|")
        
        ![ProductName] = varValue(0)
        ![TaxClassA] = varValue(1)
        ![SellingPrice] = varValue(2)
        ![Tax] = varValue(3)
        
              
        ![RRP] = 0
        
        'immediately save the record
         .Update
        End With
Me![sfrmPosLineDetails Subform].Requery

Me![sfrmPosLineDetails Subform].Recordset.Requery
    
End If
Me.TimerInterval = 100
End Sub

 

[dalski]

It wouldn't it's just a heads up as it was such an inadequate OP & I have much of my own work to do was just trying to be helpful is all shooting back a quick response for you to investigate further incase you were under pressure is all. Trying to give back to the forum which has helped me so much.

1 - You don't requery both the recordset and the form/ it's either or with the benefits as explained previously.

2 - You haven't change the parameters in opening the recordset either. You are opening a copy of the dataset, not the real dataset, thus you're changes will never be saved. You have opened a copy.

I'm short on time atm so will take a look when I have some time & take a look at your db.

 

[arnelgp]

test your form and check out the code on AfterUpdate event of txtProductCode.

 

[nector]

test your form and check out the code on AfterUpdate event of txtProductCode.

Finally you have sorted it sir arnelgp, this is amazing

 

[Minty]

But do you understand why