Changing a forms output source
- Thread starter Jason613
- Start date
Maybe that's not the right terminology but basically I have a form I made with a wizard linked to a seperate table..I have another table I want the form to be linked to instead so when I add things to this form the data will be inserted into my new table and not the old one..really it's just changing the output of that form to another table I created...how do I do this??
ghudson
Registered User.
- Local time
- Today, 05:17
- Joined
- Jun 8, 2002
- Messages
- 6,187
I bet you are wanting to change the forms Record Source. It is in the "Data" Properties of the form. You should use a query or build the SQL.
Use a query as the record source for a form or report
Use a query as the record source for a form or report
CraigDolphin
GrumpyOldMan in Training
- Local time
- Today, 02:17
- Joined
- Dec 21, 2005
- Messages
- 1,570
I think what you want is to change the record source property of your form.
I am assuming, of course, that all the relevant fields etc are in the 'new' table. And if so, and you have two tables with the same field names etc in the one database, then I strongly advise you to read up on the subject of table normalization and database design.
I am assuming, of course, that all the relevant fields etc are in the 'new' table. And if so, and you have two tables with the same field names etc in the one database, then I strongly advise you to read up on the subject of table normalization and database design.
Thanks for the replies..Craig is it a problem if i have two tables (actually 5 tables) with the same fields +/- a few that correspond to 5 different forms? Will it mess anything up? Honestly, i did ti this way because i got tired of trying to figure things out on access (like trying to add a new record without having to add and push a command button on a form everyone time i want to add new data for a new record...)..???
boblarson
Smeghead
- Local time
- Today, 02:17
- Joined
- Jan 12, 2001
- Messages
- 32,025
Must have a low threshold for pain.
But seriously, if you don't have your database table structure set properly then you may find that it actually gets HARDER when trying to get meaningful information out. And the purpose of putting data into a database is to be able to get meaningful information back out.
So, you really should work through the table design issues FIRST before trying to work on the interface.
Low threshold for pain?! Access made me totally crazy..I hate this program lol..No offence but it sucks..Should of used SQL but too late now..Really what i'm doing here is working of a template survey database..My problem now is how do i relate the right fields in the tables in order to link up the forms..Right now i have like 6 different forms from 6 differernt tables all linked together by a command button (ie: access doesn'[t know they are related yet) so i can see how queries (which i have no idea how to do) will be a problem in the future...To start, how would i relate several tables together and what is this table normalization thing the guy above was talking about????
Damnit Craig, i'm a doctor not a computer engineer--why does microsoft need to make this shit so difficult!?!
Damnit Craig, i'm a doctor not a computer engineer--why does microsoft need to make this shit so difficult!?!
CraigDolphin
GrumpyOldMan in Training
- Local time
- Today, 02:17
- Joined
- Dec 21, 2005
- Messages
- 1,570
Jason, I sympathise with your situation.
I'm a biologist and was educated and trained at college with data storage and display via Excel. I'm really good at Excel. And that, unfortunately, taught me a lot of ways of doing things that are exactly the wrong way to store data in a relational datbase program like Access.
And Bob is 100% right in that the shortcuts you can take when creating your tables will actually end up causing you far more work overall than they will save you in the long run.
Believe me. Been there done that.
I've spent a lot of time figuring out how to correctly design tables in a database. You WILL bend your brain figuring it out especially if you have to unlearn years of using excel. But there's an enormous amount of information and examples on the web to help you. If you search this forum, or via google, for terms like data normalization, or table normalization you'll find some very dry reading that will, if studied and applied, save you endless frustration down the road. For a start, there are some links from this thread (http://www.access-programmers.co.uk/forums/showthread.php?t=100211&highlight=normalization+tutorial)
Some (hopefully) helpful principles can be summed up:
-Every row of every table has to have a unique identifier.
Autonumber fields are your friend for this purpose. The field that contains this identifier are usually referred to as primary key fields.
-If you store a reference to a row from one table in another table, this is called a foreign key field.
So, if I have an 'Orders' table with an OrderID field as its primary key. And I have a related table called 'OrderDetails' which stores multiple rows of data for each order, I will likely include an OrderID foreign key field in it so that I know which particular Order each of the rows in the OrderDetails table is associated with.
-Never store more than one item of data in a 'cell'
(So no more entries like '5,4,4,5,6,8' etc, these should each be stored in a separate but related table, one per row)
-Never store repeating information in a column of data (except for foreign keys).
So, for example, you would never store the name of the clam species multiple times for each of several size measurements from the same individual. Instead, you would store the clam species once for each individual. And you would store each measurement on a separate row of a separate table.
-Never have several columns that store essentially the same information.
A classic example of this error is a table with a column called '2008 sales' and another column called '2009 sales', and yet another column called '2010 sales' etc. Instead, you should have one column called 'SalesAmounts', and another column (SalesYear) that indicates which year the value in the SalesAmount column belongs to.
-Similarly, never have multiple tables that store exactly the same kind of information as another table.
-Never use special characters (like spaces or % etc) in field or table names.
-Never use reserved words for field names (http://support.microsoft.com/kb/286335)
-Choose and apply a naming convention for all objects in your database
-You can specify the relationships between tables in the database's relationships view (Tools>Relationships)
-Backups: have a plan and always ensure that there is a relatively recent backup available in case of file corruption or some other disaster.
And to respond to one issue you mentioned, you can have a form in continuous form view which allows you to add multiple records without need for buttons etc. I advise you to learn about forms and subforms. Often, the parent form is in single form view and the subform is in continuous form view.
Also, if you have 6 forms that each are bound to one of 6 'similar' tables, then it is likely you really only need one form (with or without subforms) and a different table structure.
It is far beyond the scope of any post to teach someone how to design databases in Access. But I hope that this post will assist you finding some of the starting points that you will need to cover to be succesful. But more generally, take advantage of the information that already exists on this forum and the web. Almost everything I have learned about Access I learned from some of the genuinely brilliant and generous individuals here that have donated their time to help others: sometimes by answering my questions, and sometimes by answering questions from other people facing a similar issue. The search function is invaluable.
One truism I have found helpful is that tables in Access, should be narrow and tall (ie, few columns, lots of rows) rather than wide and short (lots of columns, few rows). This is the polar opposite of how people tend to work in Excel. If you find yourself needing more columns, think long and hard about whether you really should be storing the information in a separate table altogether. And remember that data from separate but related tables can be combined and summarized using queries anytime you want the wide/short summary tabular data output. (You'll want to learn about 'inner join' and 'outer join' types to be sure you get the results you want from queries btw.)
And remember, the functional file size limit for access is not much larger than 1GB. Beyond that, you'll get in trouble. Correct data normalization reduces the file size massively and you'll likely come nowhere near that. Incorrect normalization could bloat your database file far beyond the necessary amount and end up corrupting the file permanently.
Good luck. The learning curve is somewhat steep but well worth the time investment.
I'm a biologist and was educated and trained at college with data storage and display via Excel. I'm really good at Excel. And that, unfortunately, taught me a lot of ways of doing things that are exactly the wrong way to store data in a relational datbase program like Access.
And Bob is 100% right in that the shortcuts you can take when creating your tables will actually end up causing you far more work overall than they will save you in the long run.
Believe me. Been there done that.
I've spent a lot of time figuring out how to correctly design tables in a database. You WILL bend your brain figuring it out especially if you have to unlearn years of using excel. But there's an enormous amount of information and examples on the web to help you. If you search this forum, or via google, for terms like data normalization, or table normalization you'll find some very dry reading that will, if studied and applied, save you endless frustration down the road. For a start, there are some links from this thread (http://www.access-programmers.co.uk/forums/showthread.php?t=100211&highlight=normalization+tutorial)
Some (hopefully) helpful principles can be summed up:
-Every row of every table has to have a unique identifier.
Autonumber fields are your friend for this purpose. The field that contains this identifier are usually referred to as primary key fields.
-If you store a reference to a row from one table in another table, this is called a foreign key field.
So, if I have an 'Orders' table with an OrderID field as its primary key. And I have a related table called 'OrderDetails' which stores multiple rows of data for each order, I will likely include an OrderID foreign key field in it so that I know which particular Order each of the rows in the OrderDetails table is associated with.
-Never store more than one item of data in a 'cell'
(So no more entries like '5,4,4,5,6,8' etc, these should each be stored in a separate but related table, one per row)
-Never store repeating information in a column of data (except for foreign keys).
So, for example, you would never store the name of the clam species multiple times for each of several size measurements from the same individual. Instead, you would store the clam species once for each individual. And you would store each measurement on a separate row of a separate table.
-Never have several columns that store essentially the same information.
A classic example of this error is a table with a column called '2008 sales' and another column called '2009 sales', and yet another column called '2010 sales' etc. Instead, you should have one column called 'SalesAmounts', and another column (SalesYear) that indicates which year the value in the SalesAmount column belongs to.
-Similarly, never have multiple tables that store exactly the same kind of information as another table.
-Never use special characters (like spaces or % etc) in field or table names.
-Never use reserved words for field names (http://support.microsoft.com/kb/286335)
-Choose and apply a naming convention for all objects in your database
-You can specify the relationships between tables in the database's relationships view (Tools>Relationships)
-Backups: have a plan and always ensure that there is a relatively recent backup available in case of file corruption or some other disaster.
And to respond to one issue you mentioned, you can have a form in continuous form view which allows you to add multiple records without need for buttons etc. I advise you to learn about forms and subforms. Often, the parent form is in single form view and the subform is in continuous form view.
Also, if you have 6 forms that each are bound to one of 6 'similar' tables, then it is likely you really only need one form (with or without subforms) and a different table structure.
It is far beyond the scope of any post to teach someone how to design databases in Access. But I hope that this post will assist you finding some of the starting points that you will need to cover to be succesful. But more generally, take advantage of the information that already exists on this forum and the web. Almost everything I have learned about Access I learned from some of the genuinely brilliant and generous individuals here that have donated their time to help others: sometimes by answering my questions, and sometimes by answering questions from other people facing a similar issue. The search function is invaluable.
One truism I have found helpful is that tables in Access, should be narrow and tall (ie, few columns, lots of rows) rather than wide and short (lots of columns, few rows). This is the polar opposite of how people tend to work in Excel. If you find yourself needing more columns, think long and hard about whether you really should be storing the information in a separate table altogether. And remember that data from separate but related tables can be combined and summarized using queries anytime you want the wide/short summary tabular data output. (You'll want to learn about 'inner join' and 'outer join' types to be sure you get the results you want from queries btw.)
And remember, the functional file size limit for access is not much larger than 1GB. Beyond that, you'll get in trouble. Correct data normalization reduces the file size massively and you'll likely come nowhere near that. Incorrect normalization could bloat your database file far beyond the necessary amount and end up corrupting the file permanently.
Good luck. The learning curve is somewhat steep but well worth the time investment.
boblarson
Smeghead
- Local time
- Today, 02:17
- Joined
- Jan 12, 2001
- Messages
- 32,025
Well, I think you are in a situation where you don't like what you don't know. Forgive me for saying this but if you are venturing into "Access World" as an amateur, it is going to be painful. It isn't the program that "sucks" but it does have a lot to do with what knowledge you have about relational databases and how to properly construct a data model.
If I were to go try knee surgery, I think I'd be in a bit of a fix as I know nothing about it and, even it someone were to say - "oh, it is easy" - I'd have to say, "not for me," because I don't have any training in that area.
But the thing about it is that, unlike doing knee surgery, you can actually learn how to construct a robust database that will fit your needs and not be a pain. However, it really does come with some cost. You must be willing to put in the effort to learn; period. There is no "magic pill" which will get you from point A to point B here.
But as Craig has pointed out, many have come before you with similar circumstances. We've seen those who have been successful because they ask questions, they follow the advice of those who have gone before them, and they see it through. And on the reverse side we've seen those who have gotten frustrated, were only looking for the "band-aid" quick fix and who probably still hate Access today.
So the big question is - can you take the advice, start with the table structure (there have been many posts generated on a survey database) and then let the members of AWF (and any other forums you've chosen) to help you get through this?
And just so you know - while Microsoft advertises Access as a quick, business user type of tool, it is not simple. In fact, building something in SQL Server or Access should take the same path in table design first and then the user interface follows along. The user interface is not the starting point (although ideas for it can help you in figuring out what data you are needing to store, it isn't the start point).
Hope that helps, but if not - sorry.
Hi Craig and Bob,
First Craig thank-you very much for your great reply! Those do's and don'ts will certainly come in handy! The weird thing about my database is that although i have the same fields in my table each table represents a different follow-up appointment--so they are related but i guess i can try making subforms to put them in the same table (that is what they enable me to do right?)..
Bob, my appologies for the bashing of Acess..Unfortunately i have limited time to complete this and understand the program at the same time..Ask Craig, i'm sure he's been in similar situations
..Though you're right, learning more now will make everything easier later on so i'll do my best not to complain as much (though i can't help it in my head lol)..
Thanks again!!
Jason
First Craig thank-you very much for your great reply! Those do's and don'ts will certainly come in handy! The weird thing about my database is that although i have the same fields in my table each table represents a different follow-up appointment--so they are related but i guess i can try making subforms to put them in the same table (that is what they enable me to do right?)..
Bob, my appologies for the bashing of Acess..Unfortunately i have limited time to complete this and understand the program at the same time..Ask Craig, i'm sure he's been in similar situations
..Though you're right, learning more now will make everything easier later on so i'll do my best not to complain as much (though i can't help it in my head lol)..Thanks again!!
Jason
CraigDolphin
GrumpyOldMan in Training
- Local time
- Today, 02:17
- Joined
- Dec 21, 2005
- Messages
- 1,570
Glad to help. Sounds like you need one table with a column to keep track of which appointment/date the row belongs to. Perhaps if you showed an example of one or two of these tables (with dummy data), along with some guidance as to what you want to do with the database, you might get some more specific advice on your table structure.
Cheers,
Craig
Cheers,
Craig
CraigDolphin
GrumpyOldMan in Training
- Local time
- Today, 02:17
- Joined
- Dec 21, 2005
- Messages
- 1,570
How large is this file? You can post attachments of up to 1MB on this forum I believe....
boblarson
Smeghead
- Local time
- Today, 02:17
- Joined
- Jan 12, 2001
- Messages
- 32,025
2Mb is allowed for mdb, accdb, and zip files. First run Compact and Repair and then zip the file.
