how to connect a form to many tables

[currentdb]

Hi all,

I was wondering how to make my form look for other tables as well. Actually the form uses one table, but I want it to use 2 other tables, making it 3 tables in total.

Here's my code:

Public Sub Fusion_Click()
   Dim Sql As String
   Dim pathFichierWord As String
   Dim rs As DAO.Recordset
   Dim db As DAO.Database
   Set db = CurrentDb()
   Dim WordObj As Word.Application
   Dim docs As Word.Documents
   Dim doc As Word.Document
   Dim pb As New Form_frmProgBar
   Dim intK As Integer
   Dim lngRecCount As Long
 
   Dim path As String
   Dim path As String
   Dim path1 As String
   Dim path2 As String
   Dim path3 As String
   Dim path4 As String
   Dim path5 As String
   Dim path6 As String
   'path1 = path + "\" + "pathFR\" + "F_1st_renewal.docx"
   'path2 = path + "\" + "pathEN\" + "E_1st_renewal.docx"
   'path3 = path + "\" + "pathFR\" + "F_2nd_renewal.docx"
   'path4 = path + "\" + "pathEN\" + "E_2nd_renewal.docx"
   'path5 = path + "\" + "pathFR\" + "F_3rd_renewal.docx"
   'path6 = path + "\" + "pathEN\" + "E_3rd_renewal.docx"
   path = CurrentProject.path
   pathFichierWord = path + "\" + "IN\" + "E_1st_renewal.docx" 'chemin du template
   Sql = "SELECT * FROM Tb_1stRenewalTMP"
   Set rs = db.OpenRecordset(Sql)

for now it uses the Sql = "SELECT * FROM Tb_1stRenewalTMP" line,

But I want also tu use the tb Tb_2ndRenewalTMP and Tb_3rdRenewalTMP.

How I can do that?

Thanks

 

[Rx_]

Dim MyAnswer as String
MyAnswer = InputBox("What Table do you want to connect?")

Sql = "SELECT * FROM " & MyAnswer

In place of an InputBox, build a listbox, option buttons or what ever logic you need to provide the answer (i.e. provide the user with a list).

 

[currentdb]

Hi Rx,

The user has no idea which table to select, an hence that's why I'm trying to pass it only through VBA.

In fact, my first form, which is the main form, contains 2 group options with a command button. The user click select which renewal he wants, in this case, let's say he selects ''1st renewal'' and language is ''English''. The code of this form will then select records for the past 30 days, move them to table Tb_1stRenewalTMP, then, call the code from another form (you saw part of the code for the second form). And this second form then look into this table and merge the records into a pre-defined Word template. That works without any problem.

The problem is that my other queries select records for the past 15 days and also for the past 5 days. So, select the ''2nd renewal'' will run the query for the past 15 days, move data to Tb_2ndRenewalTMP, then again, call the code from the second form. But doing this means I have to change manually the fom's data source so it can look then for Tb_2ndRenewalTMP and not for Tb_1stRenewalTMP. Without having to do it manually, is there a way to make it work through VB code? (no need to add any other msgbox)

Thanks

 

[Rx_]

Your focusing on the InputBox, the emphisis is to provide the proper text into the SQL text statement with VBA after some business logic decision.
In place of the input box, you will need to provide either some logical condition (based on your business rules) that returns MyAnswer.

Business Rule logic varies for the situation. My client gave me a dozen conditions for a business situation. It will take a good part of today to develop the Select Case statements and If Then statements with the purpose of returning the correct SQL string with no user intervention.

You mentioned the "Forms Datasource" ( Me.RecordSource).
Just wondering if you might execute the business logic to determine what the RecordSource might be (in terms of SQL string) and then assign it to the form's Recordsource property as it loads?

To automate the process: At your main switchboard, you could set up a set of commands to open a form with one recordsource, close, then re-open the same form with your next recordsource, down the line so that you don't have to do this manually.

Bottom line, there are a lot of different solutions. But, the right answer probably depends on each individual's style and experience level.

 

[boblarson]

To use multiple tables on a form, the NORMAL way is to use SUBFORMS on a main form. Trying to update more than one table using a single form is not usually the best and winds up taking more work than using subforms. By the way you can format the subform so it looks like it is just a part of the main form.

 

[The_Doc_Man]

This smells suspiciously unnormalized. If you have different tables for different renewals, why would you NOT prefer to just have a code in a single table that TELLS you which renewal is in progress?

The part about "which language" also bothers me if your choice is to have separate data for a given language, because you will have a problem correlating anything from table to table. From the user end, one table holds what they want. But from the other end of your business, you now have multiple tables to be reconciled, one per language. This is making a lot of work for yourself.

Here is a crazy line of thought, but it might actually do what you want and yet simplify the matter tremendously. If all renewals are in the same table but they are flagged with different language codes, you can filter that table for the selected language code before you open the form (or after, for that matter, if you do a requery and refresh).

You want to avoid segregation of data in a way that "over-normalizes" what you are doing, because that has the side effect of DEnormalizing your tables and giving you more work, not less.

 

[currentdb]

The_Doc_Man,

This smells suspiciously unnormalized. If you have different tables for different renewals, why would you NOT prefer to just have a code in a single table that TELLS you which renewal is in progress? > I do have a single code in a single form for the three renewals. But I cannot use the same table for other renewals as well.

The part about "which language" also bothers me > actually there are only 2 language, french and english. FYI, the queries I created, for exemple, the one for the past 30 days, return records that belong to french and the other query, records that belong to English. Results for both languages are moved to a single table and that's ok. And there's no any neee to filter these tables because the end result is merged into a pre-defined word template. Users don't have to open tables and look inside. All they have is to select the renewal they want, click and the end result is in word.

But the code that will allow me to use more than one table in the second form, I'm having difficult time how to figure it.

 

[currentdb]

BobLarson,

To use multiple tables on a form, the NORMAL way is to use SUBFORMS on a main form > if you say the normal way is to use SUBFORMS, I agree with you, but the subform does not need to be visible on the main form. Just remain hidden. But will that solve the problem ? I mean make this subform connect to 3 tables ? FYI this form is used only to merge data from Access into Word (pre-defined template).

 

[currentdb]

Rx,

You mentioned the "Forms Datasource" ( Me.RecordSource).
Just wondering if you might execute the business logic to determine what the RecordSource might be (in terms of SQL string) and then assign it to the form's Recordsource property as it loads? > Yes that's correct.

 

[currentdb]

Better is to add a sample db so all of you understand better the logic. But where in the post I can add the db ?

 

[currentdb]

I added finallya sample db so you can see how it works. It also contains a sample word template. For this you need to create 2 folders on your computer in the same directory as the db, folders named IN and OUT. Place the sample word doc in the IN folder.

Then open database and click on form frmCriteria2, select renewal1 + language french. The process will move data to Tb_1stRenewalTMP + call form merge 3x which then looks into the table Tb_1stRenewalTMP and merges data into the sample template (look in the OUT folder for the records).

This process works with query 30 (query for the past 30 days). But so it can work with query for pat 15 days or even 5 days, the form merge 3x has to look into Tb_2ndRenewalTMP (records for 15 days) or Tb_3rdRenewalTMP (records for 5 days). Queries are already created and set in the frmCriteria, but only I have to figure out how to make it look in the tables I mentioned.

 

[boblarson]

Better is to add a sample db so all of you understand better the logic. But where in the post I can add the db ?

Follow these instructions.

 

[currentdb]

It's already up at the top of the post as explained in post #11

 

[boblarson]

It's already up at the top of the post as explained in post #11

Yes, I posted that while I still had the window open and hadn't seen your post yet about doing so. My suggestion is to not post it in the first post but to post it in the post that you are currently on. It helps people to see it because they usually don't go back to the previous posts since they've already read them.

 

[currentdb]

BobLarson,
I posted the db at the same time you posted your instructions. That's why. I looked if I could post the db on the post I was currently on, but I did not see the ''GoAdvanced'' button. That's why I went up and posted it there. Now that I see the ''GoAdvanced'' button, I'll remember next time to post the db in the same post, not at the top. For now, scrolling to the top of the post just takes a mere seconds, not minutes )

 

[boblarson]

For now, scrolling to the top of the post just takes a mere seconds, not minutes )

Yes, and having posted a notice to us you did let us know it was there. I was just pointing out this for the future.

I've taken a look and the simple matter is this. If you move the code from your button to a sub on the form, like this (and if you put it in the Form module you can still use the ME keyword:

Public Sub Fusion(strTableName As String)

and then in the code you change to this:

Sql = "SELECT * FROM " & strTableName

Then in your btnData_Click() event you would call it like this:

Call Fusion(Me.SomeControlOnYourFormwithTheName)

personally, I would not use the option box but use a combo box to select it and you can include the name of the table in it but hidden. Then you can refer to the combo like this (as long as the ColumnCount is right on the combo):

Call Fusion(Me.ComboNameHere.Column(1)

where column(1) is the second column (it is zero based so 0 is the first column, 1 is the second and so on).

Does that help?

 

[currentdb]

boblarson,

I'm a little confused here.
What you suggest is move the code from form merge 3x into a sub, but hidden, on form frmCriteria ?

If it's like that, ok, but next this line

Sql = "SELECT * FROM " & strTableName

where strTableName needs to be replaced by the three tables?
Exemple:

Sql = "SELECT * FROM " & Tb_1stRenewalTMP
Sql = "SELECT * FROM " & Tb_2ndRenewalTMP
Sql = "SELECT * FROM " & Tb_3rdRenewalTMP

 

[boblarson]

boblarson,

I'm a little confused here.
What you suggest is move the code from form merge 3x into a sub, but hidden, on form frmCriteria ?

If it's like that, ok, but next this line where strTableName needs to be replaced by the three tables?
Exemple:

Sql = "SELECT * FROM " & Tb_1stRenewalTMP
Sql = "SELECT * FROM " & Tb_2ndRenewalTMP
Sql = "SELECT * FROM " & Tb_3rdRenewalTMP

No, you're not understanding.

The one line fits for all of them. It is just a matter of what you PASS To the SUB. The line

Public Sub Fusion(strTableName As String)

means that you are going to pass the name of the table you want to use at the time you want to use it. So, let's say you want to still use your option buttons. You can use something like this on your command button:

Dim strTableName As String
 
Select Case OptCriteria
Case 1
    strTableName = "Tb_1stRenewalTMP"
Case 2
    strTableName = "Tb_2ndRenewalTMP"
Case 3
    strTableName = "Tb_3rdRenewalTMP"
End Select 
 
Call Fusion(strTableName)

And that passes the table name by reference to the Fusion sub which then uses the

Sql = "SELECT * FROM " & strTableName

 

[currentdb]

Here's what I did (correct me if I'm wrong)

-opened the form frmCriteria
-added a sub form
- the subform wizard asked me what sub form I wanted to add, I selected the merge 3x form
- in the OnClick properties of the merge button I changed it for ''Public Sub Fusion(strTableName As String)''
-in the merge 3x sub, I removed the line Sql = "SELECT * FROM " & Tb_1stRenewalTMP'' and replaced it with ''Sql = "SELECT * FROM " & strTableName''

Am I on the right way ?

 

[boblarson]

Nope, sorry not even close.

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.?