How to update only 1 table

[Guest]

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.

 

[Guest]

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

 

[John Vinson]

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.

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]

 

[Guest]

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.

 

[Guest]

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.

 

[John Vinson]

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]

 

[Guest]

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.

 

[John Vinson]

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.

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]

 

[Guest]

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,

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.

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]