One Form, Multiple Buttons, Multiple Tables

[Robson8112]

Just designing my first database and im having problems. I need to make some sort of form template that when i click a button to goto the form it opens selects the data from a certain table and puts it into the correct fields.

all the tables have the same field heading.

i think i need to add some code to the button, which opens up the template form, but in that code it defines which table.

Each button will select a different table, but use the same form..

The reason i want to do this is so i dont have 80+ forms, which will be the same, but reading different tables

Can anyone help??

 

[grnzbra]

My problem was along the same lines; I needed to create an input box for a large number of reports with differing parameters for each and wanted to avoid a new form for each.

I created a reportname table, a parameters name table and a required parameters table. The required parameters table contained the keys from each of the other 2 tables and had one record for each parameter for each report, along with its tab order, the textbox size, etc.

The form had textboxes for all the parameters (textbox names matched the names of the parameter fields in the reports. They were small, invisible and crammed together on one side of the form.

I created a public sub which took the name of the report, opened a recordset based on the required parameters table and WHERE reportname = reportname supplied when sub was called. The sub then looped through the resulting recordset and set the properties (visible, width, tabstop number etc and placed them centrally on the form).

The call to the sub was placed in the on open event of each report and supplied the name of the calling report.

You could do something similar based on the calling button instead of the report name, and place the call in the on click event of the button.

 

[grnzbra]

Oh, yes. I got a good bit of help here with some problems I ran into in that project. The subject line of that thread was:
How do I refer to a global variable?

 

[Robson8112]

is there a way of changing the value of Row Source in the properties every time you open that particular form?

if u can, then the i could set the button to change the Row source on each text box/list box etc to aim at a particualr table?

 

[grnzbra]

Interesting question. Seems like you could, since it is a property, but it might be easier to do it with unbound forms (still haven't been able to create an unbound for successfully) or by keeping the form bound to the same query and have the code delete and create the query def to pick up the tables and fields you need. You could keep the query sql statements in one of the used as the source for determining what is needed in the form.

 

[Robson8112]

im not using any query's.. The form just reading the table any dislaying the data in the txt/list boxes..

All the txt/list boxes will be unbound on the form, but i need it to "bound" them to a particular table when the appropiate button is pressed.

How do you change the properties in the code?

 

[nberryman]

A question springs to mind. Why have you got so many identical tables?

Say you have a table for each department

If all the data was in a single table with an extra field to identify the department and then you could filter the form from a combo box. All this could be doen using the in-built Wizards

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[Robson8112]

all the fields are the same on each table, but the data is different. (basically, each table contains info about servers and all there clients, ip's , software etc)

separate tables make it easier to update, and you select a particular server by a button, which opens up a form and the form then reads the table for that selected server. However theres 150+ servers

I've tried to use drop down combo box, but i cannot link all the info together.


any suggestions????

 

[Robson8112]

or do you know how i can have a form where i use a combo box with the list of servers, then i select a particular one, which then reads the fields from a table corresponding to the that server?

 

[grnzbra]

What you are describing sounds like one table in a single database for each server and each table contains only one record. Is that it?

 

[Robson8112]

Its a single database. One table for each server, each table contains 10-15 fields.

 

[grnzbra]

How many records in each table?

 

[nberryman]

If it fits in 1 MDB then they should all fit into one table

Unless the amount of data is vast you should be able to put it all into 1 table with a server No field and the filter the data on that

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[Robson8112]

each server has various numbers of servers none have the same number, the way your sugesting seems like i should have a row/record for each server, which would be ok, but theres about 2 - 100 clients on each server. Then i have the IP's of each client, surley that cannot be done on one table?

 

[Robson8112]

All i need is some code that, when i click a button, it points to a particular table, and a template form, otherwise im gonna have to create lots of forms for each table. The field lengths in each table are not even, ie the first field contains the server name, second field its IP, third field contains the list on clients, forth list contains there IP's. If it was in record style, it would pick up the blanks below

 

[nberryman]

At the moment all you need is the code to switch tables. What happens later?

Based on waht you say above the design would go something like this

TblServers

SrvServerID
SrvServerName
SrvServerIP
SrvServerLocation

TblClients

CliCLientID
CliServerID
CliIPAddress

Ensure that the ServerID fields are the same data size and Type and create a One to Many relationship from Servers to Clients

If you use this design all Access wizard will be able to create any forms and subforms you require

This reply is based on the fact that youare designing your first database.


If you don't understand what I mean e mail me the info you want to store and I will create the basic design for you.

Trust us, this is the way to do it


Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[Robson8112]

i've sent you my tables.... many thanks

 

[Robson8112]

can i do it by having one form, with a combo box, in the combo box is the list of Master Servers(from master servers table), when i select one, all the other fields then read a different table, containing the the list of clients etc?