 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / July 2005How to update only 1 table
Hi All, I am sort of new to this, thus bear with me if my explanation to my problems sounds confusing. Anyway, here it is: 1) I've created 2 tables ( Employees & Leaveinfo) - In the employee table, I've got the details of the employees name, datejoined & the employee id - In the Leavinfo table, I've got information like empid, entitlement, balance leave, type of leave and so forth. I've managed to create a link between the 2 tables ( via the EmpID). I've also created a form which record source is from the link tables. Basically, what I would like to do is using the Employee table as a reference ( no updates to this table is needed) I would like the user to select their name and the date they last applied for leave accordingly and with it retrieve the needed information from the Leaveinfo table. When the user makes changes to the record, it should be saved as a new record not overwrite the existing record i.e. the updates should only be to the Leavinfo table. I'm not sure if my structure of my tables are correct but conceptually I know what I want but can't seem to get it right practically. Appreciate the help. Hi, You ok with programming? To do that you have combo box on the form in which you have user name and let the user select the name from and you can show a calendar control or datepicker control to let the user select the date. Once he updates, using a query retrive the employee id using the employee name from the employe table and again usign a query add to a new record to the second table. Have a command button in which you would write this code. Hope you found this helpful Anand  Signature"Who will guard the guards?" > Hi All, > [quoted text clipped - 21 lines] > > Appreciate the help. Hi, Thanks for the reply. Sorry to say, not too good with programming :-( Is there a faster way of say making use of the form wizard to do this? Any further help on this is much appreciated. Thanks. > Hi, > [quoted text clipped - 36 lines] > > > > Appreciate the help. >Basically, what I would like to do is using the Employee table as a >reference ( no updates to this table is needed) I would like the user to [quoted text clipped - 3 lines] >overwrite the existing record i.e. the updates should only be to the Leavinfo >table. The simplest way to do this would be to use a Form based on the employees table (with all of its controls Locked if you want to prevent updating of employee data), with a Subform based on the Leave table. It's not necessary to use a query joining the two tables - the subform's master/child link field properties maintain the link. John W. Vinson[MVP] Hi, Thanks for the reply. Not too sure what you mean by using a subform but will look it up and give it a try. Thanks again. > >Basically, what I would like to do is using the Employee table as a > >reference ( no updates to this table is needed) I would like the user to [quoted text clipped - 11 lines] > > John W. Vinson[MVP] >Hi, > >Thanks for the reply. Not too sure what you mean by using a subform but will >look it up and give it a try. If you use the Form Wizard and select both tables, one of the options is to use a Subform. Or, you can create a Form based on the "one" side table, and use the toolbox Subform tool (with the magic wand icon selected) to create a Subform based on the "many" table. John W. Vinson[MVP] Hi, Thanks for the reply. Will give it a try. Btw, sorry as I'm still trying to grasp Ms Acess I've managed to create my form with a combo box, however each time I select the info I want e.g. say a particular employee's name, I can't seem to populate the rest of the fields which I've created as textboxes. I used the wizard to create the combo box but can't seem to get it to work. Sorry if this question has been answered / asked before. Always appreciate the help. Thanks. > >Hi, > > [quoted text clipped - 7 lines] > > John W. Vinson[MVP] >Hi, > [quoted text clipped - 8 lines] >Sorry if this question has been answered / asked before. Always appreciate >the help. Thanks. Combo boxes have multiple different functions. The simplest to build is one which selects a value (a CustomerID say) and updates a field in the table, allowing you to select which customer will be linked to this particular record. This type of "bound" combo will not cause any other fields to get populated! Which wizard option did you use, or - perhaps more to the point - what are the combo's properties? In particular: - What is the Recordsource of the form? - What is the Control Source of the combo? - What is the Rowsource of the combo? Post the SQL, or the table definition. - What is the Bound Column of the combo? - Is there anything in the AfterUpdate event of the combo ([Event Procedure] most likely)? If so, please click the ... icon by it and post the code. John W. Vinson[MVP] Hi, Thanks for the explanation and info. Ok, got more or less the gist of what you mean. I'll have to figure out further on what has to be done. Thanks again. > >Hi, > > [quoted text clipped - 28 lines] > > John W. Vinson[MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.