How to auto create a row in a table based on a field in another table

[runner231]

Hi



I’ve 2 tables Table A and Table B.

Table A has field SalesOrderID, TotalAmt, CustomerName

Table B has field SalesOrderID,PaymentAmt, PaymentDate.

Qn:
Is there a way to auto create a row in Table B with the SalesOrderID being populated whenever a new row is being created in Table A?

Table A
SalesOrderID TotalAmt CustomerName
123782 $1000 Alan
285123 $2000 Steven

Table B
SalesOrderID PaymentAmt PaymentDate
123782 $500 23/May/2018
285123 $100 26/May/2018

Suppose a new record is being created in Table A (user input these values in a Form)
SalesOrderID = 674235; TotalAmt = $2200; CustomerName = David

Table A
SalesOrderID TotalAmt CustomerName
123782 $1000 Alan
285123 $2000 Steven
674235 $2200 David

Table B
SalesOrderID PaymentAmt PaymentDate
123782 $500 23/May/2018
285123 $100 26/May/2018
674235 $0 23/Jun/2018

Then I want a new row in Table B with SalesOrderID 674235 being insert automatically (without user manually input 674235 in a form or directly in Table B). PaymentAmt and PaymentDate will take the default value when the new row is first being created.

Can this be done? Thanks.

[June7]

Can be done with code. But why would you want a payment record if there is no payment?

[runner231]

Can be done with code. But why would you want a payment record if there is no payment?

How can it be done? Can guide me? Thank you.

[davegri]

Add this code to the form that adds records to table A.

Option Compare Database
Option Explicit

Private Sub Form_AfterInsert()
    Dim sSQL As String
        sSQL = "Insert Into tblB (SalesOrderID,PaymentAmt,PaymentDate) " _
        & "Values (" & TempVars!tvSalesOrderID & ", " & 0 & ", #" & Date & "#);"
        Debug.Print sSQL
        CurrentDb.Execute sSQL, dbFailOnError
    TempVars.Remove "tvSalesOrderID"
End Sub

'---------------------------------------------------------------------------------------
' Method : SalesOrderID_AfterUpdate
' Author : davegri
' Date   : 6/23/2018
' Purpose: Two things here:
' 1. Check to see if the SalesOrderID already exists in tblA, if so warn and no update.
' 2. The values in the form are not available after the insert into tblA, so save the value
'    of SalesOrderID in a tempvar as soon as it is entered into the form.
'---------------------------------------------------------------------------------------
Private Sub SalesOrderID_AfterUpdate()
    Dim nID As Long
    nID = Nz(DLookup("SalesOrderID", "tblA", "SalesOrderID=" & [SalesOrderID]), 0)
    Select Case nID
        Case 0
            TempVars!tvSalesOrderID = SalesOrderID.Value
            Exit Sub
        Case Else
            MsgBox "SalesOrderID already exists", vbOKOnly, "    D A T A   E N T R Y   W A R N I N G   "
            Me.Undo
    End Select
End Sub