Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Don't want to execute UPDATE query if combo box was NOT selected

    Experts:




    Good morning -- I have a question about determining the value for an **unselected** combo box. Allow me to expand on the current process:


    Process:
    - I have a form that includes two combo boxes.
    - User must select a value for the 1st combo box. If so, a record update routine will execute. If user did NOT select value for the 1st combo box, a msg box is thrown to remind user to "select a value".
    - Now, selecting a value for the 2nd combo box is **optional**. If a value was selected, another update routine will be executed. However, if no value for 2nd combo was selected then only 1st update routine will be executed.

    Controls:
    - Both combox boxes are unbound controls
    - Both combos use SQL statements as their row source



    Current VBA:

    Code:
    Private Sub cboMoveStaffMember_Click()
    On Error GoTo cboMoveStaffMember_Click
    
        'If user presses "Yes", then Staffmember will be reassigned to a different organization
        If MsgBox("Are you sure you want to move " & Auto_Title0 & " into the new organization?", vbYesNo + vbQuestion) <> vbNo Then
            'Moves Staff Member to a New Organization
            CurrentDb.Execute "UPDATE T01_StaffMembers INNER JOIN (T01_Organization INNER JOIN (T01_Billets INNER JOIN T00_JunctionTable_OBS " & _
                              "ON T01_Billets.BilletIDpk = T00_JunctionTable_OBS.BilletIDfk) ON T01_Organization.OrganizationIDpk = T00_JunctionTable_OBS.OrganizationIDfk) " & _
                              "ON T01_StaffMembers.StaffMemberIDpk = T00_JunctionTable_OBS.StaffMemberIDfk SET T00_JunctionTable_OBS.OrganizationIDfk = " & OrganizationID_New & " WHERE (((T00_JunctionTable_OBS.RecordIDpk)=" & RecordIDpk & "));"
        
            'Update Record Modified Date-Time_Stamp
            CurrentDb.Execute "UPDATE T00_JunctionTable_OBS SET T00_JunctionTable_OBS.Record_Modified_Date = Now() WHERE (((T00_JunctionTable_OBS.RecordIDpk)=" & RecordIDpk & "));"
            
            'Throw message upon successful move
            MsgBox Auto_Title0 & " has been successfully moved into the new organiation!", vbInformation, "Information"
            
            'Requery current organization
            Me.N_Code_Current.Requery
            Me.OrgName_Current.Requery
            Me.OrganizationID_Current.Requery
                
            If Me.BIN_New.Value = Null Then
                Msgbox "Do nothing (for testing only right now)!", vbInformation, "Information"
            Else
                MsgBox "Billet ID will be updated!", vbInformation, "Information"
            End If
    
    
        End If
    Exit_cboMoveStaffMember_Click:
        Exit Sub
    cboMoveStaffMember_Click:
        MsgBox Auto_Title0 & " was *NOT* moved into new organization!" & vbCrLf & vbCrLf & "Please select a new organization from the 'New N-Code' drop-down and then click on 'Move Staffmember'.", vbCritical, "Warning"
        Resume Exit_cboMoveStaffMember_Click
        
    End Sub
    My question:

    Now, in the above VBA, all works great except the IF/THEN/ELSE for the **Me.BIN_New.Value = Null**. Regardless whether or not I chose a value from the 2nd (optional) combo box, I always get the message "Billet ID will be updated!"

    Why is it what I don't get the message box "Do nothing! if I did NOT select a value? Ultimately, I want to place the secondary update query into the Else statement but I don't want to update the records if nothing was selected.

    Thank you,
    EEH

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    would have to say that it's because the expression is not true (combo value <> null). In these cases, you put a break point in the code and step through and check values as you go. Note that where variables are concerned, a line has to execute before you can examine the expected state of the variable. You don't have that situation here. Even if I have guessed correctly, I'd say there is a potential problem with your approach. If a user selects a value from the 2nd combo by accident and doesn't want that to affect the outcome, your code will run as if their choice was intentional and they want to proceed on that basis. But first things first, perhaps. Test to see if the expression evaluates to null or not.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Nothing is ever EQUAL to Null not even another null

    Try this instead:
    Code:
    If Nz(Me.BIN_New,"") = ""
    The .Value isn't needed as its the default property

    In fact as the above line is only for testing purposes, you just need:
    Code:
     If Nz(Me.BIN_New.,"")<>"" Then  MsgBox "Billet ID will be updated!", vbInformation, "Information"
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    I agree with Colin.

    Try:
    Code:
    if Null=Null then MsgBox "Null"
    if "a" <> Null then MsgBox "not null"
    if 1 <> Null then MsgBox "not null"
    You have to use the IsNull([value]) function or the NZ() function, as Colin suggested, or the Len([value] & "") expression, which returns 0 if the [value] is Null or an empty string.

    So, you have to say:
    Code:
    If isnull(Me.BIN_New) Then

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Some time ago I did a comparison of various methods of handling nulls and how quickly each was processed by Access.
    If interested, see http://www.mendipdatasystems.co.uk/s...sts/4594424200
    Not a lot in it but Nz was the fastest of the methods I tested
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    combo value <> null)
    That was a boo boo that happened when I was in a rush to go out. I know that nothing ever = null and shouldn't have used those arithmetic operators in my answer.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    My comment about nulls was intended for the OP
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    I'd like to thank everyone for chiming in... appreciate all comments. My apologies for my late response... busy, busy day.

    Anyhow, I tried the recommendations above. First one that worked (for me) was: "If isnull(Me.BIN_New) Then".

    Again, I want to thank everyone for providing some good feedback and working solution(s).

    Cheers,
    Tom

  9. #9
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Experts:

    I have a follow-up question on this thread...

    As part of process modification, I ended up making both combo required fields (vs. combo #2 being optional). So, once both combos have values, an update routine is executed, including the update of a "StaffMemberID = "Vacant".

    My question... how can I go to the actual staff member (record) for whom information was changed?

    Pseudo code:
    DoCmd.GoToRecord acDataForm, "F11_Reassign_StaffMember_Different_Org", acGoTo, WHERE StaffMemberIDpk = " & StaffMemberIDpk & "

    I appreciate any additional feedback on this topic.

    Cheers,
    Tom

  10. #10
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi Tom!

    If you have available the StaffMemberIDpk, then try:
    Code:
    Forms("F11_Reassign_StaffMember_Different_Org").Recordset.FindFirst "StaffMemberIDpk=" & StaffMemberIDpk
    Cheers,
    John

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    seems you want the FindRecord method, not GoToRecord. There are a few default settings so if I present an example I might be making incorrect assumptions. Instead, visit
    https://docs.microsoft.com/en-us/off...cmd.findrecord

    You also have the option of creating a recordset clone and using one of the FindFirst, FindLast etc. options along with the bookmark property but FindRecord may work for you and is a bit simpler.

  12. #12
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    John:

    Thank you... the "FindFirst" will be executed *after* two update queries. Here's the problem though.

    Prior to the update query, the staff member's ID equals, e.g., "1120". That's his/her correct ID number. As part of the update routine, however, I place a "dummy employee" (i.e., "Vacant" into the form). Thus, at this time, my staff member ID = 1 and and thus "Vacant" as the current record. So, at this time, I have "lost" the ID=1120 reference.

    My question: Is there a way I can add an unbound control and cache the 1120 so I can, e.g. reference "StaffMemberID_Cache" in the Recordset.FindFirst statement? If so, what's the method for temporarily caching a value and w/o losing "1120" after the two update queries have processed and 1120 now shows 1? I hope this makes sense...


    //

    Micron:

    There are duplicate last names, so finding first occurrence of lastname will not work for me. If you have recommendations for finding value based on record ID number, that may suffice. But then, same issues applies as stated above... i.e., I'm losing the original ID # as part of the update queries.



    Thank you,
    EEH

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    By ID I assume you do not mean an autonumber field. If your table has that, you are not updating that value because you cannot, so use it rather than some volatile value. If you are not using autonumber field, here's another reason for doing so, even when it seems to be of no value at first. Then you don't need to try to store some temporary value on the form. Or am I misunderstanding the process?

  14. #14
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by skydivetom View Post
    My question: Is there a way I can add an unbound control and cache the 1120 so I can, e.g. reference "StaffMemberID_Cache" in the Recordset.FindFirst statement? If so, what's the method for temporarily caching a value and w/o losing "1120" after the two update queries have processed and 1120 now shows 1? I hope this makes sense...
    This is the main task of variables.

    For example:
    Code:
    Dim lngOldID As Long
    lngOldID  = StaffMemberIDpk
    CurrentDb.Execute "UPDATE..."
    Forms("F11_Reassign_StaffMember_Different_Org").Recordset.FindFirst "StaffMemberIDpk=" & lngOldID
    If you want to have the "lngOldID" available while the form is open, you have to declare the "lngIldID" outside of each procedure, on module level.
    Of course, you can use also an unbound textbox to keep a value, as you mentioned. Sometimes, I prefer this method because works as a "Locals Window" on the form's level.

  15. #15
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    John:

    I added the following to the VBA:
    Dim lngOldID As Long
    lngOldID = StaffMemberIDpk


    Then, I added a new unbound control (textbox) to the form.

    And, yes, upon form opening, I'd like to show the value = 1120 in both StaffMemberIDpk AND lngOldID

    Unfortunately, lngOldID is empty when I open the form. I know you mentioned something to this regard at the bottom of your last post. Maybe I didn't fully understand it... how can I display/keep the value = 1120
    in lngOldID once the form is opened?

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 10-22-2014, 09:44 AM
  2. Running Update Query On Selected Items Only
    By sdrmason in forum Access
    Replies: 4
    Last Post: 09-10-2014, 08:32 AM
  3. Replies: 11
    Last Post: 08-05-2014, 11:59 AM
  4. Replies: 18
    Last Post: 02-14-2014, 05:06 PM
  5. Query/Update what I selected
    By vtaurusv in forum Queries
    Replies: 10
    Last Post: 06-27-2013, 07:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums