Connecting Mutiple Tables with One Form

I have used MS Access database. How can I connect different tables by using only single form?

I am able to connect only 1 table with the form. In my project,
the design (structure) of the form is same, only the data will be different in all the tables.

If anyone can help me or have any example similar to it. Please let me know.

Thanks.

Hi,

If the data is different and form design is the same, then you can make the table related actions conditional. For example.

private sub form_load()
----
----
'Mod1.frmType A variable to show what form is being called
Select Case Mod1.frmType

case "CSB"
ssql = "Select * from CashBill"

case "CRB"
ssql = "Select * from CreditBill"

case "INV"
ssql = "Select * from Invoice"

end select
rs.open ssql, cnn,.......
----
end sub

If you look at the code, onload of the form, i have opened tables based on the variable Mod1.frmType. Mod1 is a module and frmType is a Public variable of type String. Assume that this form is being called from the various bills menu, like Cash, Credit, Invoice. On click of the each menu, i assign "CSB", "CRB", "INV" to Mod1.frmType variable and then i call this form. In the form_load i check which bill is being selected and i open that specific table, as i have shown above.


Hope it is useful to you.


Gs

As you said that you have assigned "CSB", "CRB", "INV" to Mod1.frmType variable and then i call this form. How do we assign this in module?

And one more thing, I want that title (caption) of the form should appear according to the Table Name.

Hi,

A Sample Code would be..


MDIMain: 'An MDI which shows the Menu (just for the case)

private sub mnuCSB_Click()
Mod1.frmType = "CSB"
frmCommon.Caption = "Cash Bill" 'Caption for your form
frmCommon.Show
end sub

private sub mnuCRB_Click()
Mod1.frmType = "CRB"
frmCommon.Caption = "Credit Bill" 'Caption for your form
frmCommon.Show
end sub

frmCommon: 'the Common Form for all your tables.
private sub form_load()
Select Case Mod1.frmType

case "CSB"
ssql = "Select * from CashBill"

case "CRB"
ssql = "Select * from CreditBill"

case "INV"
ssql = "Select * from Invoice"

end select
rs.open ssql, cnn,.......
----
end sub

Mod1: a Module.
Public frmType as String. 'Only the declaration.

frmType is just a Global Variable, by which we can keep track which form is being called.

Gs

Thanks it is very helpful for me.

Now how can I do the same thing in reports. I have used DataEnvironment. And I have created 15 reports to diplay the data of each table. Now I want to use only 1 report "report.dsr" to display the data of each table separately.

For example if I click "Show Report" on the form of "Cash Bill" it should show me the report of Cash Bill, and if I click "Show Report" on form of "Credit Bill" it should display me the report of Credit Bill using only 1 report "Report.dsr".

Hi,

You can follow the same logic.

Regards



Gs

Now this is a little bit different:

Before: I wanted to call only one form (frmCommon) using by menu "Menu1" i.e. "mnuCSB_Click", "mnuCRB_Click()"

Now: The form (frmCommon) which appears after clicking mnuCSB_click. From this particular form, I want to call only one "Report.dsc". This form have only one command button with the name "cmdReport". How can I use only 1 command button "cmdReport" to show data from different tables.

I have one command button one the form with the name "cmdReport". How can I use only this command button "cmdReport" to display different reports using only one "DataReport1.dsr" report.

You would have been better off using a public procedure to reinitialize the form to take into consideration instances where the form isn't unloaded either accidentally or intentionally.

Better You search Database Section of vbforums.com