ADODB.Connection & Recordset

[bowlerdave]

I have a form that shows policy information for an insurance brokerage. When I enter the effective and expiration date of a policy, it calculates and updates a field called DaysInsured.

If a policy cancels, I enter a cancellation date. This updates another field called DaysInsuredBeforeCancellation.

On the After Update part of the cell called CancellationDate, I want the database to look at another table, called ProRata, in the same database. The other table has two colums, the first is Days and the second is ProRataAmount.

I want the database to take the value in the DaysInsuredBeforeCancellation cell, look in the 2nd table (ProRata), find the record under Days equals the value in DaysInsuredBeforeCancellation and then take the amount in the ProRataAmount field and enter it into the original form/table in a cell called ProRataFactor.

I know I need Connection and Recordset to do this but cannot get it done right. Any ideas on how to write the code?

Windows XP
Access 2003

[Zwoker]

I have a form that shows policy information for an insurance brokerage. When I enter the effective and expiration date of a policy, it calculates and updates a field called DaysInsured.

If a policy cancels, I enter a cancellation date. This updates another field called DaysInsuredBeforeCancellation.

On the After Update part of the cell called CancellationDate, I want the database to look at another table, called ProRata, in the same database. The other table has two colums, the first is Days and the second is ProRataAmount.

I want the database to take the value in the DaysInsuredBeforeCancellation cell, look in the 2nd table (ProRata), find the record under Days equals the value in DaysInsuredBeforeCancellation and then take the amount in the ProRataAmount field and enter it into the original form/table in a cell called ProRataFactor.

I know I need Connection and Recordset to do this but cannot get it done right. Any ideas on how to write the code?

Windows XP
Access 2003

Hi,

Are the tables you are talking about contained in the same database that you have the form etc... in? If so, you don't need a connection string, you can just open the tables and read the data into a recordsets. From there you can find the records you want and process them.

If this is the case let me know and I'll give you some example code to open and read the data. If they are in a different database, is it an MS Access database? Or something different?

Also, in your question you talk about a "cell". I'm not sure what you mean by that. Are you referring to a field in the table? Or a text box on the form?


Regards,
Zwoker.