I use the following code to update data in a table (Address) based on another table (Customer):
The update process works except that the order that it does is not what I expected when more than one UPDATE is needed for one record.
For example:
Let's say that the Address table looks like this:
CustomerID AddressType PostalCode
001 Customer 10000
and I update it with the following Customer table (three updates for the same record on the table):
CustomerID AddressType PostalCode
001 Customer 20000
001 Customer 30000
001 Customer 40000
I end up with and address table looking like this:
CustomerID AddressType PostalCode
001 Customer 20000
When I need to end up with this:
CustomerID AddressType PostalCode
001 Customer 40000
I guess I expected the code to go through the tables from top to bottom, but that does not seem to be the case. How can I tell it to do so?
Thanks
mafhobb
Code:
'Look at the table "Customers", select all "UPDATE" and edit the table.
CurrentDb.Execute "UPDATE Customer INNER JOIN Address ON (Customer.addressType = Address.addressType) AND (Customer.customerId = Address.customerId) " & _
"SET Address.addressNr = [Customer].[addressNr], Address.firstname = [Customer].[firstname], Address.lastname = [Customer].[lastname], Address.companyName = [Customer].[companyName], Address.postalcode = [Customer].[postalcode], Address.country = [Customer].[country], Address.workphone = [Customer].[workphone], Address.email = [Customer].[email], Address.notes = [Customer].[notes], Address.streetaddress = [Customer].[streetaddress], Address.city = [Customer].[city], Address.contactTypeId = [Customer].[contactTypeId]" & _
"WHERE (((Customer.function)='UPDATE'));"
The update process works except that the order that it does is not what I expected when more than one UPDATE is needed for one record.
For example:
Let's say that the Address table looks like this:
CustomerID AddressType PostalCode
001 Customer 10000
and I update it with the following Customer table (three updates for the same record on the table):
CustomerID AddressType PostalCode
001 Customer 20000
001 Customer 30000
001 Customer 40000
I end up with and address table looking like this:
CustomerID AddressType PostalCode
001 Customer 20000
When I need to end up with this:
CustomerID AddressType PostalCode
001 Customer 40000
I guess I expected the code to go through the tables from top to bottom, but that does not seem to be the case. How can I tell it to do so?
Thanks
mafhobb