Copy values to new record in subform

[mafhobb]

I have a pop up continuous form (frmEspecialServiceParts) with a bunch of data and a "Add Data" button on each record. I also have a form (frmInv) with a subform on it (frmInvParts). The control name for this subform is Parts.

What I need to do is to copy the data selected by the user on frmEspecialServiceParts to a new record on frmInvParts

The following code copies the data from frmEspecialServiceParts into frmInvPart, except that it overwrites existing records instead of copying it on to a new record.

How can I modify this code to make sure that the selected data is copied to a new record in frmInvParts?

Private Sub cmdAddtoInvoice_Click()

' Figure out the values that need to be copied and copy them to the appropriate subform.
'   Variable Definition
    Dim SKUNumber As String
    Dim Description As String
    Dim DealerPrice As String
    Dim CustomerPrice As String
    Dim NumberOfItems As String
    
'Give the variables their value
    Me.txtSPSKU.SetFocus
    SKUNumber = Me.txtSPSKU.Text
    Me.txtSPDescription.SetFocus
    Description = Me.txtSPDescription.Text
    Me.txtSPDealerPrice.SetFocus
    DealerPrice = Me.txtSPDealerPrice.Text
    Me.txtSPCustomerPrice.SetFocus
    CustomerPrice = Me.txtSPCustomerPrice.Text
    
'Ask for the quantity
    NumberOfItems = InputBox("Quantity?", "Please Enter the quantity", 0)
'Make sure that something was entered or simply exit sub
    If NumberOfItems = "0" Then
        Exit Sub
    ElseIf NumberOfTimes = "False" Or NumberOfItems = "" Then
        Exit Sub
    End If

'   Add the values to the recipient subform. 
    Forms![frmInv].Form.[Parts].Form.txtQuantity.SetFocus
    Forms![frmInv].Form.[Parts].Form.txtQuantity.Text = Val(NumberOfItems)
    Forms![frmInv].Form.[Parts].Form.txtSKU.SetFocus
    Forms![frmInv].Form.[Parts].Form.txtSKU.Text = SKUNumber
    Forms![frmInv].Form.[Parts].Form.txtDescription.SetFocus
    Forms![frmInv].Form.[Parts].Form.txtDescription.Text = Description
    Forms![frmInv].Form.[Parts].Form.txtPrice.SetFocus
    Forms![frmInv].Form.[Parts].Form.txtPrice.Text = Val(CustomerPrice)
    
   
    Exit Sub
 End Sub

Thanks

Mafhobb

 

[Minty]

You aren't setting the form to a specific / new record, so it will simply update the current one.
You need to put the sub form onto a new record first.

Personally I would use an sql INSERT query to add the record in code then refresh the form.

 

[mafhobb]

Thank you Minty,

So how do I put the subform in "new Record" mode...but only using VBA in this sub, because this subform can be opened from other forms and it is fine as is.

I believe that I need to use DoCmd.GoToRecord , , acNewRec on the subform, except that I do not know how to invoke this from another form

mafhobb

 

[mafhobb]

This did it!

'Set focus to frmInvParts Subform Control on frmInv (Parts is the Control Name)
    Forms![frmInv].Form.[Parts].SetFocus
    With Forms![frmInv]![Parts].Form
        .Recordset.AddNew
    End With

Thanks

mafhobb

 

[mdzahed035]

It shows can't find the field '|1' referred in your expression.


with runtime error 2465