By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,479 Members | 2,239 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,479 IT Pros & Developers. It's quick & easy.

Store Old Job Index

P: n/a
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?

Feb 15 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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
Feb 16 '07 #2

P: n/a
On Feb 15, 7:07 pm, Smartin <smartin...@yahoo.comwrote:
eric.nguyen...@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

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.

Feb 19 '07 #3

P: n/a
er************@gmail.com wrote:
On Feb 15, 7:07 pm, Smartin <smartin...@yahoo.comwrote:
>eric.nguyen...@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


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
Feb 19 '07 #4

P: n/a
On Feb 19, 5:41 pm, Smartin <smartin...@yahoo.comwrote:
eric.nguyen...@gmail.com wrote:
On Feb 15, 7:07 pm, Smartin <smartin...@yahoo.comwrote:
eric.nguyen...@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
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.
Feb 20 '07 #5

P: n/a
er************@gmail.com wrote:
On Feb 19, 5:41 pm, Smartin <smartin...@yahoo.comwrote:
>eric.nguyen...@gmail.com wrote:
>>On Feb 15, 7:07 pm, Smartin <smartin...@yahoo.comwrote:
eric.nguyen...@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
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
Feb 21 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.