Hello!
I have been sitting on this issue for quite some time, and I decided it is time to reach out and seek help. I am building an Access Database that keeps track of money donations made by Clubs/Individuals to my agency. data was previously stored in paper binders or excel spreadsheets. Here is a simple example of workflow I imagined:

> Donation is initiated
Records person finds a club or an individual on the list making that donation, OR adds a new club
New donation is added (Foreign Key points to ClubID)
>Reports/certificates are printed.

Because it could not possibly be that easy, here are the caveats:
The donation certificate contains a consecutive DonationNumber for this particular club/individual (multiple donations per club are common), this number should also account for the number of donations made previously (DonationHist), that were recorded in the binder. I do not envision transferring all the old data into database, but the number of last donation will have to be recorded and stored in database.
When new donation is being made, the DonationNumber = Max(DonationNumber) + 1 (that's easy enough), however, here are the different cases leading to establishing the Max(DonationNumber):

When New DonationID (a Primary Key, autonumber, different from DonationNumber) is initiated through a form, I want VBA to check if DonationHist IsNull; if it is, I'd like to force an entry there - either final DonationNumber from a binder, or 0 if it is a very first donation by that club. Then I would go back to New DonationID and calculated the DonationNumber as DonationHist + 1.
If DonationHist Is Not Null then go directly to New DonationID and calculate the DonationNumber as DonationHist + 1.

I suppose this code has to run only once - when it is a very first DonationNumber to be stored in a table for this club. After that I can use the DonationNumber = Max(DonationNumber) + 1.

I have a hard time with nesting these events. I imagine a series of IF-THEN-ELSE statements, or maybe CASE. But I could use some opinions on logic here. My current code is capable of calculating the DonationNumber for every new donation for each club, but it doesn't account for DonationHist.

Please Login or Register  to view this content.

I know this is a lot, but I was wondering if you had an idea of what would be the good way of going through this.

Any input appreciated!
Thanks!

Radek