Update Query Based on IF Statement

[QandAdam]

I have a button which runs an update query. When the user clicks on the button, a pop-up appears asking the user to enter the missing info (e.g. [ENTER ID NUMBER] and [ENTER STAFF NAME] - This will update records with the Staff name they enter based on the ID they enter)I want to change the query based on some sort of IF statement.

The button currently just runs the query and the pop-up appears because in the 'Update To' and 'Criteria' fields, I've entered [ENTER STAFF NAME] and [ENTER ID NUMBER].Below is the idea but I don't know how to get it to work.

IF [KeepSeprate] = False 'KeepSeperate is a Yes/No Checkbox Field'
THEN UPDATE
[Staff Name] & [Staff Name2] = [ENTER SAFF NAME] 'This is where the user enters he Staff Name'
[DateAllocated] & [DateAllocated2] = Date()
WHERE [ID] = [ENTER ID] 'This is where the user enters the ID'

IF [KeepSeprate] = True 'KeepSeperate is a Yes/No Checkbox Field'
THEN UPDATE
[Staff Name] = [ENTER STAFF NAME] 'This is where the user enters he Staff Name'
[DateAllocated] = Date()
WHERE [ID] = [ENTER ID] 'This is where the user enters the ID'

 

[bobsan42]

first of - isn't Staff name connected to an ID ?
If it is (as it should be) then you only ask for the ID or for the name - the other you get from the database.

on the subject:
you need a procedure (e.g. in VBA) to collect the necessary information and store it in variables.
Then based on the outcome of the IF statement you have to build your query/SQL using the values of the variables/
a good resource here: https://www.w3schools.com/sql/sql_update.asp
and then you have to execute it.


At least TIHIWDI.

The other way is to make an update query which is asking for parameters, but I would not recommend this approach.

 

[UncleCatwoman]

As a rule, I try not to have users manually enter values that need to be used for updates. Miskeys, which WILL happen far, far too often, will either crash your query (if you're lucky) or run but give you bad data (if you're not).

What is the ID number? Is the record/order/document/sale/whatever you're looking at or is it the staffperson's ID number?

What I always do for things like these is run them from a form. I build drop-downs or option buttons for the user to select the values to use. It seems like you'd only need two or three - a Yes/No pair of option buttons for "Keep Seperate?", and a drop down for staff/employee names and, if the ID is the OrderID, a drop-down (or maybe list box) with those. The user clicks a button that verifies they've chosen/selected everything they need to and then it does the update based on that. Depending on how many different tables the data is actually stored in, I'd either pull it into a recordset, make the changes and then flush them back or you can just build the SQL statements, using the selected parameters, and execute them.

Hope this helps!