Using DAO for the first time

[Joy M]

Hi -

Here's what I want to do.

I have created a file of Invoices for insurance policies. Each Invoice
record has one or more Invoice Details records.
An invoice record has 1 of 4 transaction types: New Policy, Renew Policy,
Change Policy, Cancel Policy.

Let's say the transaction type of the first record is New Policy. This
means I have to write a new Policy record, the key will be the policy no.
(I have already validated the policy number to be unique, so that is not a
problem.) I also have to write a Policy Transaction record.
So one Invoice record begets new Policy and Policy Transaction records,
according to transaction type.

(Also the Invoice record updates the Policy record, when a policy is changed
or cancelled.
So that means I have to go to the Policy recordset and find a policy using
the policy number from the Invoice record.)

There are also Transaction records and Ledger records created from
Invoices. So there is lots of I/O. You get the picture by now.
It sounds just like a 3GL program to me - read a record, write a record
according to the transaction code.

Q: How do I do this in VBA? I have never used DAO and recordsets before.
So far I can open the Invoice file and do a Select Case by transaction type
using DAO.

I guess what scares me is I will have all these recordsets open, and the
code will become huge from equating all these fields for the copy function.
Q: Is writing one very huge procedure the way to go? I am scared it will
become unwieldy.
Q: Can I call other procedures or do you have to stay in the same procedure
that you opened the recordsets in?

I had the whole thing working perfectly with Append, Update and Delete
queries, but this was just processing each record as the user entered it on
the form.
But I don't think action codes are the way to go, since I have to process
the invoices as a batch, (the current month decides whether an invoice is
posted or deferred.)

Q: Do I use DAO to open all the recordsets and Edit and Update methods to
change values in fields and save the new records?
Let me know and I will start writing the code; some of it should be straight
forward.

Well, if you can answer those questions, or tell me a different way of doing
it, I will be happy to follow your advice.
I have lots of Access books here, (they don't help much) and I need a push
in the right direction.
If, by any chance, you can point me towards any code examples, it would be
wonderful.

Thanks a lot for your help.

Joy

 

[Yanick]

Hi Joy,

Unfortunatly I don't know how DAO is working but I know that you can pass a
recordset to a other Function/Sub.

Ex:

Call MySub(Myrecordset) 'Function call

Private MySub(MyRecordset as recordset) 'Definition
end sub

I do it all the time and it is working fine. Do not forget that more your
functions will but specific to do something, more you will be able to import
them in an other database later.

Yanick

 

[Joy M]

Hi Yannick -

Thank you for taking the time to write.

You have really helped me a lot, showing me that I can pass a recordset in a
function.
This will really help me organize my code.

Joy