how to connect a form to many tables

[currentdb]

Are you running ALL 3 of those at the same time or just selecting one and then running it and selecting another and running it, etc.?

ALL 3, you mean the renewals ?
No, not running 3 at the same time, not even selecting another and running it.

Here's the logic:

- user selects renewal 1 and the language of its choice
-data is moved to table Tb_1stRenewalTMP
-at the same time, another query moves data to Tb_data which is another table
-merge data from Tb_1stRenewalTMP into Word
-empty table Tb_1stRenewalTMP

If user want to run second renewal, it cannot be run on the same day like the 1st renewal. So if the 1st renewal was run today and tomorrow user will only run the second renewal, then
-data is moved to Tb_2ndRenewalTMP but query will not look in the ASSURVAR1 table, but in the Tb_data. Why ? Table tb_data contains the same fields as the 3 tables (Tb_1stREnewalTMP, etc), but it also contain an additional field, field Dt_renewal. It's a date field with todays date.

In other words, when user executed the query for the 1st renewal, same data was moved to table Tb_data and stamped with todays date.

So for the second query, the query will look into Tb_data and move records to table Tb_2nd renewal.

Same logic applies for 3rd renewal.

you can't run both on the same day. 1st renewal is the first as it select records in the past 30 days, moves them to Tb_data. Then 2st renewal which look for records in the past 15 days will look in the table Tb_data because it needs to use the EXP_DATE field and the field date_renewal so it can determine which records needs to be moved to Tb_2ndRenewalTMP.

i hope you understand better now.

Thanks

 

[boblarson]

What I did is to create a quick little sample in your database on how you can pass parameters and use the option group to create a different sql string.

See if this doesn't help you (the frmSample should load automatically but if not, open it) Then make a selection from the option group and click the button. You will see how it changes depending on the selection you make. Look at the code behind the form to see how. You can then use something similar to change a form's Recordsource to use the appropriate table once the sql string is built.

 

[currentdb]

Hi boblarson,

I reviewed the code begind Function Fusion and it's great. I figured that I can incorporate the entire code from frm merge 3x.

I noticed that you used only one option group instead of 2 options group as it's on frmCriteria.

In this case if I take the code from frmCriteria2 and call Function Fusion at its end lik:

If optCriteria = 1 And optLanguage = 1 Then
xSQL = db.QueryDefs("QryDate30_exp_insertF").Sql
xSQL = Mid(xSQL, InStr(xSQL, "Select"))
Set rs = db.OpenRecordset(xSQL)
If rs.EOF Then
xRecord = 0
Else
rs.MoveLast
xRecord = rs.RecordCount
End If
MsgBox "Il y a " & xRecord & " lettres françaises pour le 1er rappel"
DoCmd.SetWarnings False 'enlever le message d'avertissement d'Access
DoCmd.OpenQuery "QryDate30_exp_insertF"
Call Function Fusion(strTableName)

Is that the right way ?

The thing is that every query like QryDate30_exp_insert_F is already bound to its own table as well as QryDate15 (Tb_2nd_renewalTMP)and QryDate5(Tb_3rd_renewalTMP).

Because when data is transferred to the appropiate table, also it uses its own template as well. In fact there are 6 word templates and in the pathFichierWord = path + "\" + "IN\" + "E_1st_renewal.docx" line, here's only one template. You can see this line in the form merge 3x at the top.

I figured I could use several pathFichierWord like pathFichierword2,3,4,5,6 so I can use all 6 templates.

But how to call them from Function Fusion?

I don't know if I'm really close or still out, so I hope you can still help.

Thanks for your patience.