S
Sammie
I use a form called TakeaNumber to create a new record with an
auto-number ID field. After I create the new record, I want to
automatically add a new record to the shipments table, and copy the
fields from the TakeaNumber form to the shipments table. My problem is
that the [invoice #] field in the shipments table is indexed as unique,
and in this case, I don't want to create a new record; I only want to
update the fields of the existing record. Here is my code on the
TakeaNumber form to create the new record in the shipments table. What
do I need to add to this procedure to check for an existing [invoice #]
in the shipments table before adding a record, and then instead of
adding new, I want to update the fields in the shipments table.
Thank you!
Sammie
Private Sub Form_AfterUpdate()
On Error GoTo Err_Form_AfterUpdate
'Create new Invoice for this client.
Dim dbsSuperdatabase As Database
Dim rstshipments As DAO.Recordset 'table to be updated
Set dbsSuperdatabase = CurrentDb
Set rstshipments = dbsSuperdatabase.OpenRecordset("Shipments")
rstshipments.AddNew
rstshipments![INVOICE #] = Me![INVOICE #]
rstshipments![Client] = Me![Client]
rstshipments![Sold To] = Me![Client's Client]
rstshipments![Vessel Name] = Me![Vessel Name]
rstshipments![OrderNo] = Me![OrderNo]
rstshipments![EmployeeID] = Me![EmployeeID]
rstshipments![Disposition] = Me![Disposition]
rstshipments![Status] = Me![Status]
rstshipments![Item Summary] = Me![Item Summary]
rstshipments![Supplier] = Me![Supplier]
rstshipments.Update
Debug.Print "dataerr = "; DataErr
Response = acDataErrAdded 'Requery the list.
Exit_Form_AfterUpdate:
Exit Sub
Err_Form_AfterUpdate:
MsgBox Err.Description
Resume Exit_Form_AfterUpdate
End Sub
auto-number ID field. After I create the new record, I want to
automatically add a new record to the shipments table, and copy the
fields from the TakeaNumber form to the shipments table. My problem is
that the [invoice #] field in the shipments table is indexed as unique,
and in this case, I don't want to create a new record; I only want to
update the fields of the existing record. Here is my code on the
TakeaNumber form to create the new record in the shipments table. What
do I need to add to this procedure to check for an existing [invoice #]
in the shipments table before adding a record, and then instead of
adding new, I want to update the fields in the shipments table.
Thank you!
Sammie
Private Sub Form_AfterUpdate()
On Error GoTo Err_Form_AfterUpdate
'Create new Invoice for this client.
Dim dbsSuperdatabase As Database
Dim rstshipments As DAO.Recordset 'table to be updated
Set dbsSuperdatabase = CurrentDb
Set rstshipments = dbsSuperdatabase.OpenRecordset("Shipments")
rstshipments.AddNew
rstshipments![INVOICE #] = Me![INVOICE #]
rstshipments![Client] = Me![Client]
rstshipments![Sold To] = Me![Client's Client]
rstshipments![Vessel Name] = Me![Vessel Name]
rstshipments![OrderNo] = Me![OrderNo]
rstshipments![EmployeeID] = Me![EmployeeID]
rstshipments![Disposition] = Me![Disposition]
rstshipments![Status] = Me![Status]
rstshipments![Item Summary] = Me![Item Summary]
rstshipments![Supplier] = Me![Supplier]
rstshipments.Update
Debug.Print "dataerr = "; DataErr
Response = acDataErrAdded 'Requery the list.
Exit_Form_AfterUpdate:
Exit Sub
Err_Form_AfterUpdate:
MsgBox Err.Description
Resume Exit_Form_AfterUpdate
End Sub