Import Field Data on Add only

[Jim K. - SGB]

I have a DB called Ship-01 with the following Tables:
SR01
ARCUST

SR01 is the main DB where the user inputs data. It has several fields
including the following:
CUST_NO
CUST_NAME

ARCUST is an ODBC Linked DB with several fields including ones that are the
same as the 2 listed above in the SR01 DB. This Linked DB has a 1 to many
relationship to SR01 with the Linked field being CUST_NO.

I want to automatically populate the CUST_NAME field in SR01 with the data
from the CUST_NAME field in ARCUST when adding a new record only. After the
record is added, the SR01 should retain the original imported text whether or
not the CUST_NAME and/or the CUST_NO in the ARCUST DB is changed or deleted.
This preserves the history for said record. Is this possible with simple
coding or macros and if so, how? I am very new to Access and have no
experience with VBA.
Jim K.

 

[Doug M]

I don't know about the ODBC issue, but if both tables were in the same
database you could do this by having a form (called say frmSalesOrderEntry)
for the user to enter data into ARCUST, and put code into the AfterUpdate of
the fields for CUST_NO and CUST_NAME (called say txtCUST_NO and
txtCUST_NAME) to check if a corresponding record in SR01 exists. Like...


Private Sub txtCUST_NAME_AfterUpdate()

Dim db1 As DAO.Database
Dim rs1 As DAO.Recordset
Dim strSQL As String

Set db1 = CurrentDb()
strSQL = "select * from SR01 where CUST_NO = '" &
Forms("frmSalesOrderEntry")!txtCUST_NO & "'"
Set rs1 = db1.OpenRecordset(strSQL)
If rs1.EOF Then
' Customer number not entered yet!
Else
' put customer name into SR01 if not already filled in.
If IsNull(rs1.Fields("CUST_NAME")) Or rs1.Fields("CUST_NAME") = ""
Then
strSQL = "update SR01 set CUST_NAME = '" &
Forms("frmSalesOrderEntry")!txtCUST_NAME & "' where CUST_NO = '"
strSQL = strSQL & Forms("frmSalesOrderEntry")!txtCUST_NO & "'"
db1.Execute (strSQL)
End If
End If

End Sub

Private Sub txtCUST_NO_AfterUpdate()

Dim db1 As DAO.Database
Dim rs1 As DAO.Recordset
Dim strSQL As String

Set db1 = CurrentDb()
strSQL = "select * from SR01 where CUST_NO = '" &
Forms("frmSalesOrderEntry")!txtCUST_NO & "'"
Set rs1 = db1.OpenRecordset(strSQL)
If rs1.EOF Then
' new customer, create customer record in SR01
strSQL = "insert into SR01 (CUST_NO) values ('" &
Forms("frmSalesOrderEntry")!txtCUST_NO & "')"
db1.Execute (strSQL)
Else
' pre-enter customer name
rs1.MoveFirst
Forms("frmSalesOrderEntry")!txtCUST_NAME = rs1.Fields("CUST_NAME")
End If

End Sub