I want to write an Update Query but have it ask the user for the current field value and then the updated field value. Sort of like "Find all records with "38" in FieldX and replace it with "39". Is this possible?

Thanks!

Bluez

This is doable. To test it (in Northwind) I added
an additional employee (11; Bunny, Bugs; Janitor)
to the Employees table.

To avoid data corruption, test this on a copy of
the Orders table!

Supposing that we wanted to show Bugs Bunny as
as the responsible Employee in the Orders table, replacing
one of the other employees (1-9), the following would do it:

UPDATE Orders SET Orders.EmployeeID = [enter new ID]
WHERE (((Orders.EmployeeID)=[enter employeeID]));

When prompted for the new ID, enter 11. When prompted
for the EmployeeID, enter the current employee ID (1-9).

Thanks Raskew!

Could this code be attached to a command button as a switchboard item perhaps? Like as a "On Click" event?

Bluez

Sure, although the approach would be a little different. Once again:

To avoid data corruption, test this on a copy of the Orders table, not the original!

Here's the code I'd use:

Private Sub Command0_Click()
Dim db As Database
Dim msg As String
Dim strSQL As String

Set db = CurrentDb
strSQL = "UPDATE Orders SET Orders.EmployeeID = "
msg = "Enter new ID"
strSQL = strSQL & InputBox(msg) & " WHERE " _
& "(((Orders.EmployeeID)="
msg = "Enter old ID"
strSQL = strSQL & InputBox(msg) & "));"
msg = "Warning! You are about to permanently alter your data. Are you sure you want to continue?"
If MsgBox(msg, vbOKCancel) = vbCancel Then Exit Sub
'db.Execute strSQL
End Sub


For safety's sake, the next to last line is commented-out, since this is the one that will cause the changes to occur. Remove the apostrophe and the procedure will execute without any further warnings.

Absolutely what I needed. I experimented a little while waiting for your repsonse and I was close but your code is much easier to read and follow. I'll insert it in the next few minutes and run it, although it looks like you already have so you know it works. :-)

Thanks again Raskew for all your help!!

Bluez

Hi again Raskew,

I'm having 1 problem with the code you posted, and would like to also pick your brain on 1 other possibility.

When I go to run the code you posted, it tells me that the line:

Dim db As Database

is not defined. Have I missed making a necessary reference?

Found what was wrong here. It's fixed now. Still need help with the situation below though.

--------------------------------

On the form where I have placed this button, I would like to place 2 text boxes, one for the New number and one for the Old number. I'd like to have the contents of these text boxes serve as the criteria for this button, as well as other buttons on the same form. How would I change your code to have it look to the text boxes??

Thanks for all your help!

Bluez