Update Query Based on IF Statement
Results 1 to 3 of 3

Thread: Update Query Based on IF Statement
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2019
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Update Query Based on IF Statement

    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'

  2. #2
    Board Regular bobsan42's Avatar
    Join Date
    Jul 2010
    Location
    Bulgaria, GMT+2 (42.891813,25.313594)
    Posts
    1,281
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update Query Based on IF Statement

    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 by bobsan42; Jan 25th, 2019 at 09:42 AM.
    "...it's sad that in our blindness we gather thorns for flowers..."
    mostly using:
    windows 7 +10 (64-bit) / excel 2013 +2016 (32-bit) / access 2013 +2016 (32-bit) / some imagination & Google of course
    You don't need to read between the lines - just read them all!

  3. #3
    New Member
    Join Date
    Nov 2016
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update Query Based on IF Statement

    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!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •