Append Query

[Guest]

Hi! I have an append query that updates information from one table to
another. Is there a way to run that append query multiple times, and instead
of adding new records to overwrite the existing record? For example, I write
an invoice amount to my table, if that invoice amount changes can I overwrite
my first append query?

Thanks

 

[John Webb via AccessMonster.com]

Yes it is possible, you would need to follow these steps:

1. Check for the invoice number in your table (where you append the records
to)

2. If does not exist, run the append query as normal

3. If exists, run update query instead

I don't know of a way to do this without using macros or VBA, and I don't
know how comfortable you are with these; so I've included a snippet of code
below:

This code assumes several things:
1. you pass the invoice number to the procedure as a long number
2. Your table is called "Invoices"
3. Your invoice number field is called "InvoiceNumber"
4. You have two queries, one called "appInvoiceUpdate" with is the append
query, the other "updInvoiceUpdate", which is the update query

***CODE BEGINS***
Sub AppendOrUpdate(plngInvNum as long)

Dim lngInvoiceNumber as long

lngInvoiceNumber = DLookUp("[InvoiceNumber]", _
"Invoices","[InvoiceNumber] = " & plngInvNum)

If IsNull(lngInvoiceNumber) Then
CurrentDb.Execute "appInvoiceUpdate"
Else
CurrentDb.Execute "updInvoiceUpdate"
End If

End Sub

***CODE ENDS***