Append Query

[mespa]

Hello
My task for creating an append query was put on hold.. Now a month later I am attempting it again.

It is possible for someone to just give me some sample code on: the task at hand.
I have it where information will be entered on Form
Subform shows History

I need to Update the "Status" in the History Table if
Status changed in the Employee Table; and move Status to Previous Status in the History Table.

Any tips would be highly appreciated.

Thanks

 

[dpimental]

Try this.
Use the "On Change" event to call an update query.

The query would update History table and update the History.previous_status with the History.current_status and the History.current_status with the Employee.status.

Of course, there needs to be some relationship between the Employee and the History table, like some id field that you can use for both updates.

What do you think?

David Pimental
(US, Oh)
dpimental@checkfree.com

 

[mespa]

i will certainly give this\a try

 

[mespa]

Good Morning Dave,

Please bear with me... Self taught with Access

When I build this event. Will I do this at the properties of Status for the Employee Table and in the main form.

This what I keyed in on the property of Previous Status and it came back with syntax errors

Private Sub Change
[Tbl_MeEval_History].[PreviousStatus] = [Tbl_MeEval_History].[Status]

 

[dpimental]

Personally, to make it the easiest, I would do the following.

1. Yes, work with the Status field.

Is the History information contained in the Employees table or in a separate table?

Your answer will determine the best suggested course of action.

David Pimental
(US, Oh)
dpimental@checkfree.com

 

[mespa]

History information is in a separate table

Tbl_MeHistory.. has everything that is in the Employee Table, I just added item Previous Status and Datemodified

 

[dpimental]

I don't know how you are linking the tables together; but here is one way to do it.

1. on After Update for the Status Field, you will need to ...

1.Some how set the previous status in the History Table to the current status.

And

2. Set the current status to the Status field of your form.

Perhaps the Easiest way is simply to run 2 Queries.
(Both of which are using the "id" of the current record you are on in your form.

The First query updates previous_status to the current_status.

The Second Query updates current_status to the form field value of status.

Depending on how much VBA you know, you can get the current and previous status after the update, setting variables to their values, then do a single sql update using the DoCmd.RunSQL command.



David Pimental
(US, Oh)
dpimental@checkfree.com

 

[mespa]

Thanks for the input.

I know very little VB, I will figure something out.
again Thanks

 

[dpimental]

Did you get this stuff taken care of, or do you still need some help?

David Pimental
(US, Oh)
dpimental@checkfree.com

 

[mespa]

Hi David

This project keeps stopping and re-starting, wish it would be more consistent .


Now that I have the green light again I will try your suggestion. All I have is a VB book ):<