What kind of database is this? Access?
Create Form to Update Table
Hi All,
I'm looking to create a form that will update rows in a table. The form is a user entry form so X person can fill in the necessary information and then hit "Save" which will add a new line into the table.
I'm not particularly well versed with Forms, I've historically avoided doing them. Can anybody provide a 101, links or suggestions on how I can create the form?
Thanks in advance,
Ross
I'm looking to create a form that will update rows in a table. The form is a user entry form so X person can fill in the necessary information and then hit "Save" which will add a new line into the table.
I'm not particularly well versed with Forms, I've historically avoided doing them. Can anybody provide a 101, links or suggestions on how I can create the form?
Thanks in advance,
Ross
- Status
- Not open for further replies.
1 - 12 of 12 Posts
Yeah, apologies. Running MS 201o
Is it updating rows or appending rows? You mention both in the original post.
To get started, 101 style, I would create a query with the desired fields from table, run the 'form Wizard', first step (in Wizard) would be to point at that query. I don't recommend pointing the form directly at the table. You may later want to add a table based dropdown or something and starting with a query gives you more flexibility going forward.
Note the basic Save your refer to is in reality a 'non factor' in access. When you type into a field the change focus the change is saved. going to a new record save the previous record.
Make a backup of your database and don't be afraid to try a few different 'forms' till you get it like you want it.
I don't like some of the ways the wizard builds thing. I usually start with a blank form then add a data source, then drag and drop Text boxes from the design control tab (assigning data fields to each box as I make them). Data integrity can be controlled by table level validation or form level validation or a combination of both.
OBP may join this thread at some point. He has several template type databases that may already have the form you looking for...He will need to know the basic function of the database.
To get started, 101 style, I would create a query with the desired fields from table, run the 'form Wizard', first step (in Wizard) would be to point at that query. I don't recommend pointing the form directly at the table. You may later want to add a table based dropdown or something and starting with a query gives you more flexibility going forward.
Note the basic Save your refer to is in reality a 'non factor' in access. When you type into a field the change focus the change is saved. going to a new record save the previous record.
Make a backup of your database and don't be afraid to try a few different 'forms' till you get it like you want it.
I don't like some of the ways the wizard builds thing. I usually start with a blank form then add a data source, then drag and drop Text boxes from the design control tab (assigning data fields to each box as I make them). Data integrity can be controlled by table level validation or form level validation or a combination of both.
OBP may join this thread at some point. He has several template type databases that may already have the form you looking for...He will need to know the basic function of the database.
Ross, you could just use the Form Wizard, but as draceplace says they can produce some odd Record Sources.
Can you give us some information on what type of data the table holds?
Can you give us some information on what type of data the table holds?
Thanks Draceplace, it's both appending (create new lines) and updating (search/ edit) - apologies!
Agree with you on the wizard front - I'm self taught on access and I've only learned what I need to know (as opposed to starting on page 1 in a book) and haven't required forms so far. I prefer to build things like queries via SQL rather than using the wizard because the wizard just doesn't give you anything beyond basic functionality.
Interesting you mention that, I did look at unbound text boxes, then trying to update the source so that the text entered feeds directly into a new line.
The database is holding information on 2 things: Employee Working Hours (monthly, yearly etc) and Demand on that resource. The employee data is absolutely fine, and feeds in from another access database. The tricky part for me is the Demand. Demand is based on new projects we receive and this isn't held anywhere else. I need to be able to create the table (which I have done) and then create this form that will allow me to update that table. I would like to create a "Enter New Project" button if you will, to update this record. As I mentioned I also need to create a search/ edit button.
The data from the table will then be used to produce graphs of demand based on team and project type. It will also be combined with the supply data to do a simple supply vs demand.
Agree with you on the wizard front - I'm self taught on access and I've only learned what I need to know (as opposed to starting on page 1 in a book) and haven't required forms so far. I prefer to build things like queries via SQL rather than using the wizard because the wizard just doesn't give you anything beyond basic functionality.
Interesting you mention that, I did look at unbound text boxes, then trying to update the source so that the text entered feeds directly into a new line.
The database is holding information on 2 things: Employee Working Hours (monthly, yearly etc) and Demand on that resource. The employee data is absolutely fine, and feeds in from another access database. The tricky part for me is the Demand. Demand is based on new projects we receive and this isn't held anywhere else. I need to be able to create the table (which I have done) and then create this form that will allow me to update that table. I would like to create a "Enter New Project" button if you will, to update this record. As I mentioned I also need to create a search/ edit button.
The data from the table will then be used to produce graphs of demand based on team and project type. It will also be combined with the supply data to do a simple supply vs demand.
A simple form can be used for Input/edit/search and does not require you to hit a button to "save the data" as Access does it automatically.
If you provide a copy of the database with No Data or a couple of dummy records I can easily create you a functional form, or you could just use the form wizard and then check the record source afterwards. We can then talk you through adding some functionality.
How are the tables related?
If you provide a copy of the database with No Data or a couple of dummy records I can easily create you a functional form, or you could just use the form wizard and then check the record source afterwards. We can then talk you through adding some functionality.
How are the tables related?
Thanks OBP. I have a form that works, ish. It doesn't let me edit the unique ID. File attached for your perusal - your help is much appreciated.
Strictly speaking they aren't related - I'm going to have to find a way to amalgamate them (my little experience tells me to export the tables to XL and then manipulate./ reconcile the data in there and feed that back into the access db... I know that probably sounds ridiculous to somebody like yourself - at this point I'm just trying to get something that works, even if it's the long way round. Unfortunately I don't have time to learn this fundamentally!
Strictly speaking they aren't related - I'm going to have to find a way to amalgamate them (my little experience tells me to export the tables to XL and then manipulate./ reconcile the data in there and feed that back into the access db... I know that probably sounds ridiculous to somebody like yourself - at this point I'm just trying to get something that works, even if it's the long way round. Unfortunately I don't have time to learn this fundamentally!
Attachments
-
33.4 KB Views: 8
For this database to be really useful it does need to be related back to the employee and his working hours.
The relationship should be from the employee KeyID field to a foreign employee KeyID field in the demand table, which should be of the type Numeric Long Integer.
You should never be able to edit the Unique Id because that would destroy any relationships that might be set up using it.
I will add some functionality to form and post it back.
What fields would you like to search on?
I can also help with the queries for graphing purposes, although if you are advanced in SQL you may not need any help.
Your table may also be too simplistic to work well. You probably should have a "Projects" table as well as consultant and analysts tables.
The relationship should be from the employee KeyID field to a foreign employee KeyID field in the demand table, which should be of the type Numeric Long Integer.
You should never be able to edit the Unique Id because that would destroy any relationships that might be set up using it.
I will add some functionality to form and post it back.
What fields would you like to search on?
I can also help with the queries for graphing purposes, although if you are advanced in SQL you may not need any help.
Your table may also be too simplistic to work well. You probably should have a "Projects" table as well as consultant and analysts tables.
Is the A or B field really just a or b or are they words and can there be more than just a or b?
Do you only have one Record per Project, or will you be entering more than one to be summarised?
Do you only have one Record per Project, or will you be entering more than one to be summarised?
Thanks for coming back to me OBP.
For each employee there is a unique ID which I will keep in. The Supply will only be summarised, I.e. total number of hours and the demand table won't relate to any specific employee, only the total number of hours summed (supply) vs the total number of required hours (demand). Ideally, I'd like to be able to search on project name, the "A or B" field (which will be different type of projects, for example construction or design - so yes they can be more than just A or B). Thank you very much for offering to help. I will most likely need help with the queries though I'll have a first attempt so I can make the mistakes first and then know where I'm stuck rather than asking you to do them all, I'll be responsible for administering this short term so it's good for me to understand the underlying structure - I hope that's okay?
How would you suggest I reconciled the 3 tables? I.e, If I have projects, analysts, consultants, how could I sum the total supply from analysts and consultants vs the demand - I'm not very familiar with relationships and tables so I'm not sure how to relate them to each other. I've only ever built databases for reporting information so have only had to build simple tables which were all very similar so I could use union queries to get the necessary info whereas this is quite different.
Thanks again for your help on this. It's very much appreciated!
For each employee there is a unique ID which I will keep in. The Supply will only be summarised, I.e. total number of hours and the demand table won't relate to any specific employee, only the total number of hours summed (supply) vs the total number of required hours (demand). Ideally, I'd like to be able to search on project name, the "A or B" field (which will be different type of projects, for example construction or design - so yes they can be more than just A or B). Thank you very much for offering to help. I will most likely need help with the queries though I'll have a first attempt so I can make the mistakes first and then know where I'm stuck rather than asking you to do them all, I'll be responsible for administering this short term so it's good for me to understand the underlying structure - I hope that's okay?
How would you suggest I reconciled the 3 tables? I.e, If I have projects, analysts, consultants, how could I sum the total supply from analysts and consultants vs the demand - I'm not very familiar with relationships and tables so I'm not sure how to relate them to each other. I've only ever built databases for reporting information so have only had to build simple tables which were all very similar so I could use union queries to get the necessary info whereas this is quite different.
Thanks again for your help on this. It's very much appreciated!
The advantage of a Relational database like Access over easier to use packages like Excel is that it is easier to put in the data just once.
So you would have a projects table and the data stored in the Demand Table would be just the Project key ID number.
The same for the analysts and consultants, put the name in once in their table and then just select the one you want to enter their key ID number in the Demand table. It speeds up data entry and stops errors like duplication due to mispelling etc.
If you have One project that will have lots of seperate Demand records then you would have the Projects in a Main form and the demand records in a subform, inthat way you can see all the demand records for each project.
So would you like me to sketch out a relational database like that?
So you would have a projects table and the data stored in the Demand Table would be just the Project key ID number.
The same for the analysts and consultants, put the name in once in their table and then just select the one you want to enter their key ID number in the Demand table. It speeds up data entry and stops errors like duplication due to mispelling etc.
If you have One project that will have lots of seperate Demand records then you would have the Projects in a Main form and the demand records in a subform, inthat way you can see all the demand records for each project.
So would you like me to sketch out a relational database like that?
1 - 12 of 12 Posts
- Status
- Not open for further replies.
Tech Support Guy
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!
Full Forum Listing
Explore Our Forums
