Query Update Question - Current Record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good evening all,

This is my situation:

I have a db that keeps track of machine settings. Occasionally, settings
change due to a variety of factors. Usually only one or 2 small fields
changes out of 50ish available. Using the famous button wizard, I created a
button that duplicates the current record and pastes it in the table. This
is all fine and well. But what I would like to do is increment the rev (2 to
3, 1 to 2...). I can write a simple update query, but the problem is I will
update every single rev in my table. What I need is a way to update the rev
on a single record aka the current record.

Thanks in advance for your help.

Rich S.
 
Good evening all,

This is my situation:

I have a db that keeps track of machine settings. Occasionally, settings
change due to a variety of factors. Usually only one or 2 small fields
changes out of 50ish available. Using the famous button wizard, I created a
button that duplicates the current record and pastes it in the table. This
is all fine and well. But what I would like to do is increment the rev (2 to
3, 1 to 2...). I can write a simple update query, but the problem is I will
update every single rev in my table. What I need is a way to update the rev
on a single record aka the current record.
Click to expand...

I'm not clear what you want this button to do: do you want to *update*
just the current record, or do you want to create a *new* record with
one field incremented?

If the latter, your query could be - not an Update query - but an
Append query, appending only one record to the table. You would use

=Forms![YourFormName]![PrimaryKeyFieldName]

as a criterion to select only the record currently on display, and put

NewRev: [Rev] + 1

as a calculated field, appending this value to the Rev field.

John W. Vinson[MVP]
 
John,
This is where I am at:

tblAutoMold
AutoID 100 (AutoNumber)(Primary Key)
PartNumber 12345 (text)
Revision 1 (number)

frmAutoMold
AutoID
PartNumber
Revsion

I hit a button on frmAutoMold and I get this from:

AutoID 100
PartNumber 12345
Revision 1

AutoID 101
PartNumber 12345
Revision 1

The record is copied and pasted into the original table with a new
PrimaryKey (AutoID)

This part works just fine.

But what I would like do do is run an Update Query that adds 1 to the
Revsion of the latest part, ie change Revsion from 1 to 2 in AutoID 101.

I hope this is clearer.

John Vinson said:
Good evening all,

This is my situation:

I have a db that keeps track of machine settings. Occasionally, settings
change due to a variety of factors. Usually only one or 2 small fields
changes out of 50ish available. Using the famous button wizard, I created a
button that duplicates the current record and pastes it in the table. This
is all fine and well. But what I would like to do is increment the rev (2 to
3, 1 to 2...). I can write a simple update query, but the problem is I will
update every single rev in my table. What I need is a way to update the rev
on a single record aka the current record.
Click to expand...

I'm not clear what you want this button to do: do you want to *update*
just the current record, or do you want to create a *new* record with
one field incremented?

If the latter, your query could be - not an Update query - but an
Append query, appending only one record to the table. You would use

=Forms![YourFormName]![PrimaryKeyFieldName]

as a criterion to select only the record currently on display, and put

NewRev: [Rev] + 1

as a calculated field, appending this value to the Rev field.

John W. Vinson[MVP]
Click to expand...
 
John,
This is where I am at:

tblAutoMold
AutoID 100 (AutoNumber)(Primary Key)
PartNumber 12345 (text)
Revision 1 (number)

frmAutoMold
AutoID
PartNumber
Revsion

I hit a button on frmAutoMold and I get this from:

AutoID 100
PartNumber 12345
Revision 1

AutoID 101
PartNumber 12345
Revision 1

The record is copied and pasted into the original table with a new
PrimaryKey (AutoID)

This part works just fine.

But what I would like do do is run an Update Query that adds 1 to the
Revsion of the latest part, ie change Revsion from 1 to 2 in AutoID 101.
Click to expand...

Why put in an incorrect value, and then correct it later?

Don't copy and paste. That's good for spreadsheets, but not a good
approach in databases! Instead, run an Append query:

INSERT INTO tblAutoMold
(PartNumber, Revision)
SELECT PartNumber, Revision + 1
FROM tblAutoMold
WHERE PartNumber = Forms!YourForm!PartNumber
AND Revision = Forms!YourForm!Revision;

Store this query and execute it in your command button code.
John W. Vinson[MVP]
 
Back
Top