Update Query Based on IF Statement

QandAdam

New Member
Joined
Jan 12, 2019
Messages
30
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.

Code:
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'
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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.
 
Last edited:
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top