Update record in another table unless it already exists

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
 
J

Jeff Boyce

Sammie

One approach would be to add an unbound combo box (say, in the header of the
form). Base this combo box on a list of all the existing Invoice numbers.

Base the form on a query that "points" to the combo box as the selection
criterion for the Invoice#.

In the combo box's AfterUpdate event, requery the form with something like:

Me.Requery

With this approach, when you open the form, there's nothing in the combo
box, so the form comes up empty. If the user selects an existing Invoice#
from the combo box, the form requeries and loads that record. If your form
has controls that are bound to the underlying data fields, you'll see the
values and can update them.

I believe you can also use the "empty" form to add a new record.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
D

Douglas J. Steele

You could check whether INVOICE # already exists, and update the record
rather than add it if it does:

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
Dim strSQL As String

If IsNull(DLookup("[Invoice #]", "Shipments", "[Invoice #] = " &
Me![INVOICE #])) Then

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.
rstshipments.Close
Set rstShipments = Nothing
Else
strSQL = "UPDATE Shipments SET [Client] = " & Me![Client] & _
", [Sold To] = " & Me![Client's Client] & _
...
"WHERE [Invoice #] = " & Me.[Invoice #]
CurrentDb.Execute strSQL, dbFailOnError
End If


Exit_Form_AfterUpdate:
Exit Sub

Err_Form_AfterUpdate:
MsgBox Err.Description
Resume Exit_Form_AfterUpdate

End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Sammie said:
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
Click to expand...
 
D

Douglas J. Steele

If Invoice # is a text field, you need quotes around the value you're using:

If IsNull(DLookup("[Invoice #]", "Shipments", "[Invoice #] = '" &
Me![INVOICE #] & "'"))

and

"WHERE [Invoice #] = '" & Me.[Invoice #] & "'"

(you do realize, I hope, that I didn't give you the entire SQL for the
UPDATE query as I didn't know the data types of the various forms. That's
supposed to be an ellipsis between the lines ", [Sold To] = " & Me![Client's
Client] & _ and "WHERE [Invoice #] = " & Me.[Invoice #]. If you didn't
realize that, that would explain the Syntax Error you got for the WHERE
line.)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Sammie said:
Dear Doug,

Sorry if you are receiving this reply multiple times. I am not seeing my
reply to you posted on the newsgroup, so I am trying a different approach.

Thank you for this idea. 2 snags:

This line returns error: "Data type mismatch in criteria expression":
If IsNull(DLookup("[Invoice #]", "Shipments", "[Invoice #] = " &
Me![INVOICE
#])) etc.
The field [invoice #] in both tables is a text field, indexed with no
duplicates.

This line returns error: "Syntax Error"
"WHERE [Invoice #] = " & Me.[Invoice #]

Can you help?
Thanks
Sammie


Douglas J. Steele said:
You could check whether INVOICE # already exists, and update the record
rather than add it if it does:

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
Dim strSQL As String

If IsNull(DLookup("[Invoice #]", "Shipments", "[Invoice #] = " &
Me![INVOICE #])) Then

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.
rstshipments.Close
Set rstShipments = Nothing
Else
strSQL = "UPDATE Shipments SET [Client] = " & Me![Client] & _
", [Sold To] = " & Me![Client's Client] & _
...
"WHERE [Invoice #] = " & Me.[Invoice #]
CurrentDb.Execute strSQL, dbFailOnError
End If


Exit_Form_AfterUpdate:
Exit Sub

Err_Form_AfterUpdate:
MsgBox Err.Description
Resume Exit_Form_AfterUpdate

End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Sammie said:
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

----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World!
120,000+
Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption
=----
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update the value a field in another table 1
Filling in fields to a partially complete record in a table using a form 0
Adding detail to record in a table in a secondary table 1
Append record to another table 4
Access crashes with report preview command button 1
How to populate selection from combo box in a table 2
automatically create new record in lookup table if data does not e 9
GoTo a record from a combo in the form 4

Top