adding/checking records via vbcode

[Craig]

Hi again,

I hope this is a good way of doing what i want to achieve...

I have a table thats going to record all new invoice amounts and payments...
it looks like this..

table.ID
table.CustomerID
table.invoiceNumber
table.recordDate
table.invoiceAmount (defaulted to 0 unless changed)
table.customerPayment (defaulted to 0 unless changed)
table.paymentType


The idea is that when producing an invoice, it records the invoice in this
table and later when someone pays (even if its not the correct amount, i can
record it here)..

I have a button on the invoice form which normally saves the record.. I
would like to make it check to see if there is an entry for that invoice
number in the table above and if needed either modify or add the new record.

The idea is that when creating an invoice entry in this table only certain
fields are used and when recording payments (which would be in a seperate
record) the other fields would be used with some being related to both. I
dont need to directly link the payment with the invoice number as the
payment might be for more (or less) than the current balance.

so the record entry would look something like this...

ID CustomerID invoiceNumber recordDate invoiceAmount
custmerPayment paymentType
1 1 1 10/3/2007 100.00
0
2 2 2 11/3/2007 50.00
0
3 1 3 12/3/2007 75.00
0
4 1 13/3/2007 0
100.00 Cash
5 2 13/3/2007 0
50.00 Cheque

....and so on...

Hopefully this way i can get a running statement for any customer between
and dates...

I havent looked at the vb side of access before (even though i have played
with vb a lot over the years). How would I get access to check then add (if
needed) with vbcode?

thanks

 

[storrboy]

I personally would separate payments and invoices into two tables and
have them connected by the invoice number and require the customer to
refernce the invoice in thier payment. In my limited experience, few
people pay more than the invoice, and spearating them should allow you
to keep a balance for each invoice, and each customer by summing the
invoice amounts and payment amounts.