How do you create a form that is not tied to one single record source? In other words, I want to be able to select the record source that it updates. I have a bunch of tables that have the same data structure but are separated due to geographical nature among other reasons. Is there a way to do this?
Create form that will update multiple tables
- Thread starter WLC
- Start date
- Local time
- Today, 04:12
- Joined
- Jan 23, 2006
- Messages
- 15,574
You can change a form's record source using vba.
see http://msdn.microsoft.com/en-us/library/office/ff821093.aspx
see http://msdn.microsoft.com/en-us/library/office/ff821093.aspx
MarkK
bit cruncher
- Local time
- Today, 01:12
- Joined
- Mar 17, 2004
- Messages
- 8,472
If you have many tables that have the same structure, then you have a design problem. In Excel it makes sense, for instance, to have a sheet for businesses in Alberta, and a sheet for businesses in BC, and a sheet for businesses in Saskatchewan.
In Access, you would not put these groups of information in separate tables, which is what you may have done. In an Access table you would add a field called Province, and put all the businesses in a single table. Then if you wanted to only work with a subset of your data, you write a query, or filter the records returned from your table using criteria.
In a relational database system the boundary between tables exists in order to model how types of things are related to other types of things in one-to-many relationships (relational), like a Class might have many Students, or a Transaction may have many Posts, or an Account may have many Transactions. Tables can also be related to themselves so a Child may have many Children.
But if you have a table for Shipped orders and Pending orders and Back orders and Cancelled orders, you have a design problem. Merge all those orders into a single table, and introduce a Status field.
I think you probably have many tables that should be merged, and then handling them with the single for will be a snap.
hth
In Access, you would not put these groups of information in separate tables, which is what you may have done. In an Access table you would add a field called Province, and put all the businesses in a single table. Then if you wanted to only work with a subset of your data, you write a query, or filter the records returned from your table using criteria.
In a relational database system the boundary between tables exists in order to model how types of things are related to other types of things in one-to-many relationships (relational), like a Class might have many Students, or a Transaction may have many Posts, or an Account may have many Transactions. Tables can also be related to themselves so a Child may have many Children.
But if you have a table for Shipped orders and Pending orders and Back orders and Cancelled orders, you have a design problem. Merge all those orders into a single table, and introduce a Status field.
I think you probably have many tables that should be merged, and then handling them with the single for will be a snap.
hth
- Local time
- Today, 04:12
- Joined
- Jan 23, 2006
- Messages
- 15,574
As lagbolt said, that many tables with the same structure throws up an immediate red flag.
Tell us in plain English simple business terms the issue that suggests X tables with the same structure.
We're not saying there is necessarily anything wrong; but we are saying that it would be rare. And, we'd like to ensure that any focused responses are based on an analyzed need.
We are trying to help but guessing at the business issue is not a good strategy.
Good luck.
Tell us in plain English simple business terms the issue that suggests X tables with the same structure.
We're not saying there is necessarily anything wrong; but we are saying that it would be rare. And, we'd like to ensure that any focused responses are based on an analyzed need.
We are trying to help but guessing at the business issue is not a good strategy.
Good luck.
I have 22 tables that have the exact same structure. Each table represents a customer that has imports and exports. One customers imports may be imports to my company while another customers exports may be imports to my company. We wanted to keep each customer's imports and exports in tact and the simplest way to do that was to have each customer reside in a separate table.
Another reason is that I have some additional data that is being appended to each of these customer tables. They are individual Excel files with no identifier on them other than the file name. Red flag yes! But it's what I have to work with.
Later on down the road in my processing, I do make one Master table where I append all of the tables and append the imports to the imports or the imports to the exports whichever the case may be.
I understand you're seeing red flags. But this is how this was designed, and I'm in too deep now to go back and redo a bunch of things. All I can do is work with what I've got. I'm not very strong with VBA and I need all the help I can get.
Another reason is that I have some additional data that is being appended to each of these customer tables. They are individual Excel files with no identifier on them other than the file name. Red flag yes! But it's what I have to work with.
Later on down the road in my processing, I do make one Master table where I append all of the tables and append the imports to the imports or the imports to the exports whichever the case may be.
I understand you're seeing red flags. But this is how this was designed, and I'm in too deep now to go back and redo a bunch of things. All I can do is work with what I've got. I'm not very strong with VBA and I need all the help I can get.
- Local time
- Today, 04:12
- Joined
- Jan 23, 2006
- Messages
- 15,574
Your application/set up is designed - for lack of a better term - as a "spreadsheet".
Relational database is built on a whole different set of concepts. So designing a database to work with a relational database management system is different than a spreadsheet.
Can you put "spreadsheets" into separate tables -- sure but you have to continuously be working against the system. You will have work around and retrieval issues at every turn. I would not recommend it. And you are not the first to simply make tables from spreadsheets - many posters jumped into the "easy to use, friendly Access" without being aware of "database concepts".
For your own interest, whether you change your set up or not, you should work through this tutorial to get an appreciation of what relational database concepts are.
I wish you good luck in whatever you decide.
Relational database is built on a whole different set of concepts. So designing a database to work with a relational database management system is different than a spreadsheet.
Can you put "spreadsheets" into separate tables -- sure but you have to continuously be working against the system. You will have work around and retrieval issues at every turn. I would not recommend it. And you are not the first to simply make tables from spreadsheets - many posters jumped into the "easy to use, friendly Access" without being aware of "database concepts".
For your own interest, whether you change your set up or not, you should work through this tutorial to get an appreciation of what relational database concepts are.
I wish you good luck in whatever you decide.
Thanks for your response. I am fully aware of and appreciate the concept of relational databases. I had worked on a mainframe version of one for well over 20 years. However I am not as familiar with Access and would appreciate the help there. I thought that's what this forum was about. Not ridiculing someone for their lack of design decisions.
- Local time
- Today, 04:12
- Joined
- Jan 23, 2006
- Messages
- 15,574
I gave you an honest answer and some suggestions. I'm sorry if you felt there was ridicule involved. Certainly not on my end.
Now, since you have told us what you have and why, and are determined to work with it, I'll give you a best guess answer to your original question since I haven't tried to have 1 form for 22 tables.
If all fields have the same name and data type.
Create a form bound to one of your tables.
Use a routine/procedure to modify the record source of that form when you load or open the form.
You might even consider another form that has a combobox of all of your table names. Open this form first, then select the table you want the form to be associated with; then click a button to open the first form I mentioned above.
Have a good day.
Now, since you have told us what you have and why, and are determined to work with it, I'll give you a best guess answer to your original question since I haven't tried to have 1 form for 22 tables.
If all fields have the same name and data type.
Create a form bound to one of your tables.
Use a routine/procedure to modify the record source of that form when you load or open the form.
You might even consider another form that has a combobox of all of your table names. Open this form first, then select the table you want the form to be associated with; then click a button to open the first form I mentioned above.
Have a good day.
