New Record New Table

[goslincm]

I'm working with a table that is linked to another database. Within this linked table I created form and it has a calculated field. This field calculates if audits were filed timely or not. If timely, no further action in needed. However, if late, I need to collect more information as to the history of the now delinquent audit.

So, I don't know which direction to go if I am required to collect more information. If that calculated field tells me they did not file timely, can I have a new record created in a new table based on the result of my calculated field or am I way off?

 

[Flippertje]

If i'm correct you have made a form based on a table. This form holds a calculated field wich tells you if the person filed too late.
When late you want to gain more information. Can you tell if that information should be entered by you or that person. Also describe how much information will be asked.

Grtz Flippertje

 

[goslincm]

Flippertje, if the audit is filed late, the additional information will need to be collected:

1. If they already have a CPA
2. If we will have to assign them a CPA
3. Date 1st late notice letter was sent to them
4. Date certified mail receipt was received back at office
5. Date revenue withholding letter was sent out
6. If revenue withholding was put in place
7. CPA ID number

The information would be entered by this office.

Does that help you?

 

[Flippertje]

What you want to do is to build a code; leave the calculatedfiled for what it is. It's for show.

1. Create a table 'Tab_ExtraInfo' with you're seven fields in it but also a Autonumbering Id_Field and a field wich holds the Id number of the appropiate Audit.

2. create a Subform which you hide in you're form that holds you're calculatedfield. The subform will get the furthernamed strSQL recordset.

When opening the appropiate form (Load event) you should code something like this:

dim db as databse
dim rst as recordset
dim strSQL as string

set db=currentdb
strSQL="SELECT Tablename.IdfieldAudit, iif([Auditstartdate]<dateadd('m';-3;now()),1,0) as expr1, Tablename.[AuditFiledDate] FROM Tablename WHERE (((If([Auditstartdate]<DateAdd('m';-3;now()),1,0)=0) AND (([AuditFiledDate]) is null))"
set rst =db.openrecordset(strSQL, dbopendynaset)
'the recordset contains all audits that have not been filed for the last three months. You can alter the amount of months by changing teh '-3' to what you like.

======

>> i'm out of time here... hope to help you soon.
What's next is that you connect the subform to you're mainform. It opens only to you're accountname and on load.
The subform shows all current late audits. Pressing a button in the subform creates an entry in the table Tab_ExtraInfo.

I hope you're a bit further with this.. sorry that my time is running out here....

Flippertje

 

[goslincm]

Flippertje, thanks for the instruction. In step #2 creating the subform, I'm a little confused as to what the subform is holding? Is its control source the new Tab_ExtraInfo table that I just created with the extra fields?

And if so, then I take this new subform, and connect it to my main form, yes?

 

[goslincm]

One last question, you indicated "when opening the appropriate form...

Were you referring to the new subform just created or the form that the subform is in?

Thanks so much