Store Old Job Index

[eric.nguyen312]

I have an amend button which when clicked puts the Job form into
edit. When saved, Access backs up old job information into
'AmendedJobBackUp' table. What I want is to add a new column
'OldJobIndex' in the 'Job' table and have all amended 'JobIndex'
stored in there. How would I go in doing this?

[Smartin]

er************@gmail.com wrote:

I have an amend button which when clicked puts the Job form into
edit. When saved, Access backs up old job information into
'AmendedJobBackUp' table. What I want is to add a new column
'OldJobIndex' in the 'Job' table and have all amended 'JobIndex'
stored in there. How would I go in doing this?

Hello,

You could do this pretty easily by writing an UPDATE query to copy the
job number to OldJobIndex.

However, this will only store the last (and current) job number. If you
update again, the previous old # will be lost. If you want to keep some
history on this number you should store it in a separate table.

HTH

--
Smartin

[eric.nguyen312]

On Feb 15, 7:07 pm, Smartin <[e-mail]:

[e-mail] wrote:

I have an amend button which when clicked puts the Job form into
edit. When saved, Access backs up old job information into
'AmendedJobBackUp' table. What I want is to add a new column
'OldJobIndex' in the 'Job' table and have all amended 'JobIndex'
stored in there. How would I go in doing this?

Hello,

You could do this pretty easily by writing an UPDATE query to copy the
job number to OldJobIndex.

However, this will only store the last (and current) job number. If you
update again, the previous old # will be lost. If you want to keep some
history on this number you should store it in a separate table.

HTH

--
Smartin

Smartin, thats exactly what i want to do. Ive added the new
'OldJobIndex' field to the 'Job' table, buy not sure how to use the
UPDATE in the query.

[Smartin]

er************@gmail.com wrote:

On Feb 15, 7:07 pm, Smartin <[e-mail]:

>[e-mail] wrote:

>>I have an amend button which when clicked puts the Job form into
edit. When saved, Access backs up old job information into
'AmendedJobBackUp' table. What I want is to add a new column
'OldJobIndex' in the 'Job' table and have all amended 'JobIndex'
stored in there. How would I go in doing this?

Hello,

You could do this pretty easily by writing an UPDATE query to copy the
job number to OldJobIndex.

However, this will only store the last (and current) job number. If you
update again, the previous old # will be lost. If you want to keep some
history on this number you should store it in a separate table.

HTH

--
Smartin

Smartin, thats exactly what i want to do. Ive added the new
'OldJobIndex' field to the 'Job' table, buy not sure how to use the
UPDATE in the query.

"thats exactly what i want to do" -- which one? I'm guessing you just
want to keep current and previous number, with no prior history.

The SQL would look something like this:

UPDATE
Job
SET
OldJobIndex = JobIndex
WHERE
<not sure what criteria you want here*>
;

*Probably the criteria will reference the current record specified by
the form. E.g.,

WHERE
Job.JobID = [Forms]![JobForm]![JobID]
^^^^^ ^^^^^^^ ^^^^^

You will need to substitute the correct table field, form, and form
field names above ^.

But I caution again, the OldJobIndex will be bumped out and lost on the
record every time this query executes.

Hope that helps!
--
Smartin

[eric.nguyen312]

On Feb 19, 5:41 pm, Smartin <[e-mail]:

[e-mail] wrote:

On Feb 15, 7:07 pm, Smartin <[e-mail]:

[e-mail] wrote:
I have an amend button which when clicked puts the Job form into
edit. When saved, Access backs up old job information into
'AmendedJobBackUp' table. What I want is to add a new column
'OldJobIndex' in the 'Job' table and have all amended 'JobIndex'
stored in there. How would I go in doing this?
Hello,

You could do this pretty easily by writing an UPDATE query to copy the
job number to OldJobIndex.

However, this will only store the last (and current) job number. If you
update again, the previous old # will be lost. If you want to keep some
history on this number you should store it in a separate table.

HTH

--
Smartin

Smartin, thats exactly what i want to do. Ive added the new
'OldJobIndex' field to the 'Job' table, buy not sure how to use the
UPDATE in the query.

"thats exactly what i want to do" -- which one? I'm guessing you just
want to keep current and previous number, with no prior history.

The SQL would look something like this:

UPDATE
Job
SET
OldJobIndex = JobIndex
WHERE
<not sure what criteria you want here*>
;

*Probably the criteria will reference the current record specified by
the form. E.g.,

WHERE
Job.JobID = [Forms]![JobForm]![JobID]
^^^^^ ^^^^^^^ ^^^^^

You will need to substitute the correct table field, form, and form
field names above ^.

But I caution again, the OldJobIndex will be bumped out and lost on the
record every time this query executes.

Hope that helps!
--
Smartin

Yes, I want to keep the current JobIndex and previous JobIndex under
OldJobIndex with no prior history. I used the sql statement that you
gave me, but Im not sure if Im doing it correctly. Heres what I have:

UPDATE Job
SET OldJobIndex = JobIndex
WHERE Job.OldJobIndex = [Jobs]![JobIndex]![JobID]

When I execute it, I get the enter parameter value box and afterwards
no data is being filled in the tables. Could you tell me what Im
doing wrong? Thanks.

[Smartin]

er************@gmail.com wrote:

On Feb 19, 5:41 pm, Smartin <[e-mail]:

>[e-mail] wrote:

>>On Feb 15, 7:07 pm, Smartin <[e-mail]:
[e-mail] wrote:
I have an amend button which when clicked puts the Job form into
edit. When saved, Access backs up old job information into
'AmendedJobBackUp' table. What I want is to add a new column
'OldJobIndex' in the 'Job' table and have all amended 'JobIndex'
stored in there. How would I go in doing this?
Hello,
You could do this pretty easily by writing an UPDATE query to copy the
job number to OldJobIndex.
However, this will only store the last (and current) job number. If you
update again, the previous old # will be lost. If you want to keep some
history on this number you should store it in a separate table.
HTH
--
Smartin
Smartin, thats exactly what i want to do. Ive added the new
'OldJobIndex' field to the 'Job' table, buy not sure how to use the
UPDATE in the query.

"thats exactly what i want to do" -- which one? I'm guessing you just
want to keep current and previous number, with no prior history.

The SQL would look something like this:

UPDATE
Job
SET
OldJobIndex = JobIndex
WHERE
<not sure what criteria you want here*>
;

*Probably the criteria will reference the current record specified by
the form. E.g.,

WHERE
Job.JobID = [Forms]![JobForm]![JobID]
^^^^^ ^^^^^^^ ^^^^^

You will need to substitute the correct table field, form, and form
field names above ^.

But I caution again, the OldJobIndex will be bumped out and lost on the
record every time this query executes.

Hope that helps!
--
Smartin

Yes, I want to keep the current JobIndex and previous JobIndex under
OldJobIndex with no prior history. I used the sql statement that you
gave me, but Im not sure if Im doing it correctly. Heres what I have:

UPDATE Job
SET OldJobIndex = JobIndex
WHERE Job.OldJobIndex = [Jobs]![JobIndex]![JobID]

When I execute it, I get the enter parameter value box and afterwards
no data is being filled in the tables. Could you tell me what Im
doing wrong? Thanks.

Hello, I'm guessing a little, but I think maybe you want this for your
WHERE clause:

WHERE Job.JobIndex = [Forms]![Jobs]![JobID]

that is,

WHERE
Job.<table field I want to match to the form=
[Forms]! <-- this is a CONSTANT
[<name of form>]!
[<name of field on form containing the current JobID>]

Let us know!
--
Smartin