Page 3 of 3 FirstFirst 123
Results 31 to 39 of 39

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

  1. #31
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    231
    John:



    First, the product as you designed it looks great. At the risk of repeating myself, I thank you for all of the continued assistance you've given me not only in this thread but also in previous threads.

    The challenge is that my dumbed-down example database (for proprietary reasons) makes it a bit challenging the convey the ins/out of all of the underlying processes.

    As is, while the standa-alone version works fine, I think I will experience difficulty to implement it into the existing architecture. Also, as mentioned, I already have a process/form that assigns staff member vacant once an employee leaves. And, yes, after long deliberations, I decided to add the dummy employee "Vacant" to the staff members table.

    So, right now, the closed one that could be implemented is actually version 031 (vs. 04). I just need to figure out how to not keep the positions unique vs. duplicating them (as mentioned in the previous thread).

    Again, your process works great. I will go back and continue to play with vs. 031 and see if I get this somehow to work tomorrow.

    Thank you, John!

    Cheers,
    Tom

  2. #32
    accesstos's Avatar
    accesstos is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    225
    Hi Tom!

    You're most welcome!

    I will rollback to the previous sample and I'll try to help you on your existing architecture as you wish. I have to reverse the process of movements from "change member's position" to "change position's members", keeping in mind the traps of this path.

    A plain describe of this process could be this:
    - Find the target-record with the desired organization and desired billet if exists. If not exists, create a new.
    - Check if the job has already assigned to a member. If so, leave the user to decide what will happen to the existing member.
    - Set the selected member as the staff member of the target-position.
    - Set as "vacant" the current position by setting the staff member's field to 1.

    Replace the cmdApply_Click() procedure of the sample v.031 with the code that follows and I hope that will work as you wish:
    Code:
    Private Sub cmdApply_Click()
        Dim rs As Recordset
        Dim lngNewRec As Long
        'Move the selected staff member into a new organization and/or position.
        'Assign the current position to a "vacant" member.
        With Me
            If IsNull(.cboNewOrg) Then
                Beep
                MsgBox "You have to choose the new organization!", vbInformation
                .cboNewOrg.SetFocus
            Else
                .Dirty = False
                On Error GoTo ErrH
    
                Set rs = CurrentDb.OpenRecordset("Q001_SYS_Billets_Extended", dbOpenDynaset)
                'Try to find the desired position to move the current staff member.
                rs.FindFirst "OrganizationIDfk=" & .cboNewOrg & " AND  BilletIDfk=" & Nz(.cboNewRA_BIN, Nz(.BilletIDfk, "Null"))
                If rs.NoMatch Then
                    'Not found. Create a new.
                    rs.AddNew
                    rs!OrganizationIDfk = .cboNewOrg
                    rs!BilletIDfk = Nz(.cboNewRA_BIN, Nz(.BilletIDfk, "Null"))
                    rs!StaffMemberIDfk = 1
                    lngNewRec = rs!RecordIDpk
                    rs.Update
                    rs.FindFirst "RecordIDpk=" & lngNewRec
                Else
                    If (Not IsNull(rs!StaffMemberIDfk) And rs!StaffMemberIDfk <> 1) Then
                        'The position has already assigned to other member.
                        'So, the user has the chance to move her/him to an other position or just overwrite her/him with the selected member.
                        If MsgBox("This job has already asigned to " & Trim(rs!All_LastName & " " & rs!FirstName) & "." & vbCrLf _
                                  & "Whould like to move her/him to an other position first?", vbQuestion + vbYesNo) = vbYes Then
                            'Set the target record as current on the form.
                            Me.cboStaffMembers = rs!StaffMemberIDfk
                            cboStaffMembers_AfterUpdate
                            'And cancel the assignment.
                            GoTo ExitHere
                        End If
                    End If
                End If
                'Edit the existing record.
                rs.Edit
                'Try to move employee.
                rs!StaffMemberIDfk = Me.cboStaffMembers
                rs!Record_Modified_Date = Now()
                rs.Update
                'Movement completed.
                'Set the current position as vacant.
                Me!StaffMemberIDfk = 1
                'Refresh the data on form.
                Me.cboStaffMembers.Requery
                Form_Load
            End If
        End With
    ExitHere:
        On Error Resume Next
        rs.Close
        Set rs = Nothing
        Me.cboNewOrg = Null
        Me.cboNewRA_BIN = Null
        Exit Sub
    ErrH:
        MsgBox Err.Description, vbExclamation, "Move Staff Member Error(" & Err & ")"
        Resume ExitHere
    End Sub
    I just need to figure out how to not keep the positions unique vs. duplicating them
    I'm sorry, I didn't understand the above.
    Do you want to allow duplicates or not? If you do, you have to create a new record into the OBS's table for the current staff member if the target-position is not "vacant" and the user choose "No" at the relevant message.

    Cheers,
    John

    Edit: The code has updated because I had messed up the "AddNew" block.
    Last edited by accesstos; 09-17-2019 at 03:04 PM. Reason: Code correction

  3. #33
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    231
    John:

    Your continued assistance is much appreciated. Allow me to clarify (pseudo process).

    Step 1 - Person Leaves the Organization:
    1. A person who occupies, e.g., "Marketing Analyst" position has left the organization.
    2. Through a "Staff Member Form" (not included in the sample database), the incumbent employee is archived. That is, in the staff member table, a boolean value (field "Onboard") is changed from "true" to "false".
    3. At the same time, the archiving process updates the OBS table and places a "1" into the StaffMemberIDfk. This indicates the position is vacant.

    Step 2 -- Refilling the Position (or Moving an Employee):
    4. Employees either laterally move, get promoted, and/or internally fill vacant positions. This is where the "Move Form" comes into play.
    5. In my example, "Richard Roundtree" is slated to fill the position for "John Wayne" (who left the company).
    6. Positions are tied to BINs (job IDs). So, while there could be, e.g., two Marketing Analysts, they are distinct based on the BIN (e.g, BilletIDfk 1234567; 1234568).
    7. Whatever takes place, the two entities BIN + JobDescription are like "Social Security #" and "LastName"... they go hand-in-hand, so to speak.
    8. I believe in the example v40, JobID and BINs were mismatched given that I ended up with two Marketing Analysts when they were only 7 distinct jobs in the **sample** database.

    Ultimately, I merely want to replace an vacant position with an employee which then in turn leaves that position now vacant.

    Today was extremely busy at work and I only had a short period of time to further review your last sample database. I hope I'll find some time tomorrow to continue the development.

    Thank you,
    Tom

  4. #34
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    231
    John:

    I have been dabbling with your new proposed method/VBA and I like the overall process. I have integrated it into my process (still using a *sample database* for demo purposes).
    At this time, however, the current VBA logic violates the the actual business process. Attached is the latest database also with an ink-depth description (in Word) which outlines the logic and provides
    some snapshot for illustration purposes.

    Any final help you might offer me on this topic would be greatly appreciated!

    Cheers,
    Tom

    P.S. As mentioned in the Word doc, I merely need the VBA to achieve the "swapping" of the StaffMemberID. I really do NOT need (or want to) any additional queries, forms, subforms etc. (e.g., for archiving purposes).
    Attached Files Attached Files

  5. #35
    accesstos's Avatar
    accesstos is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    225
    Hi Tom!

    Your ability to describe is commendable.

    The process that you asking, is beyond doubt the most easy, but, from a "database view", not the best. You loose a lot of information by just swapping values directly into the table "manualy". Normaly, should create a new record into the OBS's table for each event of assignment, either if is a new hire or just a movement, with the corresponding details. You can take several informations from stored data without changing them. There are many ways to consider a position as vacant, occupied or recent, or, an employee as "OnBoard", and that's the task of queries and relationships.
    However, I will respect your current project conditions and I will help you on this because I appreciate the work that you have already done.

    Unfortunately, can't open the "Version 01.accdb". So, I worked on "MoveStaffMember V03" that I attach as "MoveStaffMember V032" with the updated version of "cmdApply_Click()" procedure and without any additional componets. The "Go to" combobox provides a list of all staff members from tblStaffMembers, the "New Organization" a list of all available organizations and the "New BIN" a list of vacant positions of the selected "New Organization".

    I think that this is closer to your needs than any other version.

    I wish you all the best with your project.

    Cheers,
    John

    Edit:
    In Greece, we say: The day laughed when she saw the works of night.

    Add this IF...THEN block between the "Movement completed." and "Refresh the data on form." comments in the code of "cmdApply_Click()" procedure to manipulate properly the case of staff members that not exists in the OBS's table yet:
    Code:
    'Movement completed.
    If Not IsNull(!RecordIDpk) Then
        'Set the current position as vacant.
        !StaffMemberIDfk = 1
        'Keep the date/time of "swapping".
        !Record_Modified_Date = Now()
    End If
    'Refresh the data on form.
    Also, I have replaced the attachment too. ...
    Attached Files Attached Files
    Last edited by accesstos; 09-20-2019 at 02:18 AM. Reason: Code correction in attachment

  6. #36
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    231
    John:

    Simply put... you're awesome! I appreciate the kind words and, as always, the dedicated/continued help.

    I have done plenty of testing in the sample database and all works as expected. Once I integrated the updated query and form into the actual database, all works well too. Great!

    Well, there's one small thing that's a bit of annoying (and you may say "I told you so..."). That is, out of the thousands of billets currently in the system, there are roughly 100 vacant positions. As I bring up my "Move Form" via a "Search Billet Form" I ended up adding **<>"Vacant" as criteria in the LastName field.

    At first, everything seemed to be ok but I then realized that the ELSE statement (i.e, "MsgBox "There is no such vacant position with these characteristics.", vbInformation, "Information") always kicked in; thus no moves were executed.

    I don't know if this (meaning seeing all of the vacant positions in the dropdown) is something I just have to accept. That is, ultimately, I only move actual staff members into vacant positions. Naturally, I would never move "Vacant" into a vacant postion.

    I welcome any final thoughts on this... maybe there's a simple tweak where I can exclude the vacants from the strSQL source.

    Thanks,
    Tom

  7. #37
    accesstos's Avatar
    accesstos is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    225
    Thank you Tom! I'm glad that worked as you expected!

    If you want a list in "Go to" combobox with only the staff members that appears in the OBS's table, change its SQL definition with this below:
    Code:
    SELECT DISTINCT StaffMemberIDfk, [All_LastName] & (", "+[FirstName]) AS [All], All_LastName, FirstName
    FROM Q001_SYS_Billets_Extended
    WHERE StaffMemberIDfk<>1 
    ORDER BY All_LastName, FirstName;
    But, now, you have also to change the code of the "cboStaffMembers_AfterUpdate()" event as follows:
    Code:
    Private Sub cboStaffMembers_AfterUpdate()
        Dim strSQL As String
    
        strSQL = "SELECT * FROM Q001_SYS_Billets_Extended WHERE StaffMemberIDfk"
        If IsNull(Me.cboStaffMembers) Then
            strSQL = strSQL & "<>1"
        Else
            strSQL = strSQL & "=" & Me.cboStaffMembers
        End If
        Me.RecordSource = strSQL
    End Sub
    I hope now that, all your troubles seems so far away.

    Cheers,
    John

  8. #38
    yandemir is offline Novice
    Windows 8 Access 2003
    Join Date
    Sep 2019
    Location
    Istanbul/Turkiye
    Posts
    1
    Code:
    if isnull (me.combo1) then  
       msgbox("Select pls")
       me.combo1.setfocus
       me.combo1.dropdown
       exit sub
    end if

  9. #39
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    231
    John -- that did the "trick". MILLIONS THANKS FOR THE CONTINUED HELP!!!

    Cheers,
    Tom

Page 3 of 3 FirstFirst 123
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
  •  
Tech Forums: Microsoft Office Forums