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

[skydivetom]

Realized I probably need to use the recordID (vs. StaffMemberID).

So, the following works in order to navigate to the first record:
DoCmd.GoToRecord acDataForm, "F11_Reassign_StaffMember_Different_Org", acGoTo, 1



However, the following does NOT work when attempting to move to a specific record ID:
Forms("F11_Reassign_StaffMember_Different_Org").Re cordset.FindFirst "RecordIDpk=" & RecordIDpk

What's missing? How should I rewrite the line so that I move to the RecordIDpk?

[accesstos]

Hi Tom!

You have to provide more info about the form "OrganizationID_New" and "RecordIDpk" (record source, values of unbound comboboxes etc).

I think that you ask for answers that you already have.
I see in your code snippet parameters like "Auto_Title0", "OrganizationID_New" and "RecordIDpk". How you retrieve those values?

Normally, initial values assignments takes part in the OnLoad event of a form. For example:

private mlngStaffMemberID as long 'Module level private variable

Private Sub Form_Load()
    'StaffMemberIDpk must exists in form's record source.
    mlngStaffMemberID = me!StaffMemberIDpk
    'Unbound textbox in the form.
    me.txtStaffMemberID = me!StaffMemberIDpk
End Sub

Maybe the "RecordIDpk" does not exist in the form's "F11_Reassign_StaffMember_Different_Org" record source but,
if the "RecordIDpk" refers to the "T00_JunctionTable_OBS" table, you can use a dlookup() to retrieve the desired value as below:

me.txtStaffMemberID = dlookup("StaffMemberIDfk","T00_JunctionTable_OBS", RecordIDpk=" & RecordIDpk)

and then:

If not IsNull(me.txtStaffMemberID) then
    forms("F11_Reassign_StaffMember_Different_Org").Recordset.FindFirst "StaffMemberIDfk=" & me.txtStaffMemberID
end if

Also, I think that you don't need all these inner joins to update a single field of a specific record of "T00_JunctionTable_OBS".
If I'm not missing somthing, the update query in your first post, could be as follow:

UPDATE T01_StaffMembers SET OrganizationIDfk = " & OrganizationID_New & " WHERE RecordIDpk=" & RecordIDpk

Cheers,
John

[skydivetom]

John:

Good morning... thank you for the additional support.

The particular form I'm working uses a query a rowsource. The query is linked to multiple tables. As you can imagine, for proprietary reasons, I won't be able to share the actual database with all tables/content.

I have attached a sample database. For testing purposes, I turned my data source query into a "Make Table" and generated "Table1" as the new source (that's the reason why there's duplicate information such as "File As", etc.)
Again, this is merely for demo purposes.

Now, the Form's operations do NOT work in this sample database. Yet, the form itself might provide sufficient information in order to "help you help me".

So, in my actual database, the following process takes place:
1. User brings up form
2. User then must select a "New N-Code" and -- optionally -- a new "BIN"
3. Upon selection of the two combos, the user then clicks "Move Staff Member" command button
4. Again, in this sample datbase, this process has been disabled thus the ID numbers in the yellow/orange controls won't update.
5. In my actual database, the control "StaffMemberIDpk" (top right corner in design view) will receive a value of "1" (equals "Vacant" record).
6. Based on step #5, the control "Auto_Title0" ("Nz([Contact Name], ...") will show "--- --- Vacant" afterwards.

I have also attached a JPG (with dummy data) with unhidden yellow control boxes. This illustrates how the ID numbers change once a staff member has been moved.

Now, while the StaffMemberIDpk changes from "790" to "1" (in this example), the RecordIDpk "931" does NOT change. Ultimately though, after the move, I want to display staff member "Mr. Mike Abraham" vs. "--- --- [Vacant]".

Again, I don't know whether or not the attached sample database (and JPG) is sufficient for providing you additional answers. If there's any way though you could offer additional thoughts navigating to the
same staff member once the N-Code/BIN have changed, I'd truly appreciate it. That's where the temp cache control (right above "View Current Organization" with no value should come into play.

Thank you in advance!
Tom

[accesstos]

Hi Tom!

I'm sure that is something simple but, unfortunately, unable to open your database (Unrecognized database format).
However, I try to understand why you lose the StaffMemberID, while the update procces updates only the organization into the current record. I suspect that something is going wrong with the current update proccess.

If you use the combobox "Go To" to navigate between the records of the form and after the update losing the current staff member, you can follow the steps below:

--Keep the current ID (790) in a variable just before update.
--Execute the update.
--Set the value of combobox to the stored in the variable.
--Call the AfterUpdate() event of the combobox to find the relevant record.

For example:

Dim lngID as long
lngID = StaffMemberID 'Hold the current staff member ID.
currentDB.Execute "Update..."
me.cboGoTo = lngID 'Set the value of the combobox.
Call cboGoTo_AfterUpdate() 'Go to the relevant record.
MsgBox "Bla bla..." 'Inform the user for the changes.

If you don't mind, please attach the SQL code and the VBA code of the form as .txt files to show me the current logic or create a new sample with backward compatibility.

Cheers

[skydivetom]

John:

I spent quite some time trying to figure out the update process (i.e., reassigning ID numbers). I finally got this to work... it is a somewhat complex process and describing it in detail is outside the scope of this thread. For general sync purposes, however, I will describe the general process:
1. The form describes a "Billet" (job) as shows the organization that currently "owns" the job + the billet ID (job #).
2. The "GoTo" combo box shows who (employee) currently in that billet/job.
3. At some point, an employee may gets promoted and moves into another position.
4. So, when I move the employee into a different branch and job, I need to put a "Vacant" (employee ID = 1) into the current position.

So, again, my update process works as envisioned and I do NOT need to make changes to it. However, when I move the employee, the name now shows "Vacant" given that the form shows the StaffMember ID.

So, once I moved, e.g., Mike Abraham, I want to follows his StaffMember ID in the form (vs. staying on ID = 1 or "Vacant").

Again, your process of caching the StaffMember's ID works fine. I simply can't figure out how to "follow" Mike Abraham... either via the StaffID or via the RecordID?

Private Sub cboMoveStaffMember_Click()
       
    Dim Old_StaffMemberIDpk As Long
    Old_StaffMemberIDpk = StaffMemberIDpk

    'If user presses "Yes", then Staffmember will be reassigned to a different organization and either a) receive new BIN or b) keep existing BIN
    If MsgBox("Are you sure you want to move " & Auto_Title0 & " into the new organization?", vbYesNo + vbQuestion) <> vbNo Then

        'PROCESS #1:  User only select new N-code but leaves BIN empty
        If IsNull(BilletIDfk_New) = True Then
            
            'If BIN is left empty (i.e., staff member keeps his current BIN), then only the organization value is updated.
            CurrentDb.Execute "UPDATE T00_JunctionTable_OBS SET T00_JunctionTable_OBS.OrganizationIDfk = " & OrganizationID_New & " WHERE (((T00_JunctionTable_OBS.RecordIDpk)=" & RecordIDpk & "));"
            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 organization!", vbInformation, "Information"
            
        Else
            'Do nothing
        End If


        'PROCESS #2:  User selected both new N-code and new BIN
        If IsNull(N_Code_New) Or IsNull(BilletIDfk_New) = True Then
            'Do nothing
        Else
            'Step 1: Insert "[Vacant]" (StaffMemberIDfk = 1) into the OBS table of staff member's current billet (and update date time stamp in the OBS record)
            CurrentDb.Execute "UPDATE T00_JunctionTable_OBS SET T00_JunctionTable_OBS.StaffMemberIDfk = 1 WHERE (((T00_JunctionTable_OBS.RecordIDpk)=" & RecordIDpk & "));"
            CurrentDb.Execute "UPDATE T00_JunctionTable_OBS SET T00_JunctionTable_OBS.Record_Modified_Date = Now() WHERE (((T00_JunctionTable_OBS.RecordIDpk)=" & RecordIDpk & "));"
            
            'Step 2: Insert StaffMemberIDfk into the OBS table of the new position (and update date time stamp in the OBS record)
            CurrentDb.Execute "UPDATE T00_JunctionTable_OBS SET T00_JunctionTable_OBS.StaffMemberIDfk = " & StaffMemberIDpk & " WHERE (((T00_JunctionTable_OBS.BilletIDfk)=" & BilletIDfk_New_Cache & "));"
            CurrentDb.Execute "UPDATE T00_JunctionTable_OBS SET T00_JunctionTable_OBS.Record_Modified_Date = Now() WHERE (((T00_JunctionTable_OBS.RecordIDpk)=" & BilletIDfk_New_Cache & "));"
            
            'Requery combo boxes for 'current organization'
            Me.N_Code_Current.Requery
            Me.OrgName_Current.Requery
            Me.Ra_BIN_Current.Requery
            Me.Ra_Billet_Title_Current.Requery
            
            'Empty combo boxes for 'new organization'
            Me.N_Code_New.Value = Null
            Me.BilletIDfk_New.Value = Null
               
            'Throw message upon successful move
            MsgBox Auto_Title0 & " has been successfully moved into the new organization and BIN has been updated!", vbInformation, "Information"
       
            'After update, move to first record in the form's record set
            DoCmd.GoToRecord acDataForm, "F11_Reassign_StaffMember_Different_Org", acGoTo, 1
        
        End If

    End If
   
End Sub

Right now, I simply navigate to the first record... (acGoTo, 1) but that's not the method I want to use in this process.

[accesstos]

Tom,

In your sample code, give a try to this:

   
            [...]

            Me.Recordset.FindFirst "StaffMemberIDfk=" & Old_StaffMemberIDpk 
            '-Or-----------------------------------------------------------
            With Me.RecordsetClone
                .FindFirst "StaffMemberIDfk=" & Old_StaffMemberIDpk
                Me.Bookmark = .Bookmark
            End With

            'Throw message upon successful move
            MsgBox Auto_Title0 & " has been successfully moved into the new organization and BIN has been updated!", vbInformation, "Information"
        
        End If

    End If
   
End Sub

But,
I don't think that you have to change the staff member in the current record of "T00_JunctionTable_OBS". You have to change only the IDs of organization and his/her billet. As seems, initially you change the organization of the current record and then you send the staff member to other record (WHERE BilletIDfk=BilletIDfk_New_Cache)(!) . What if you have more records than one whith the same BilletIDfk? You assign all these records to the current staff member!
I thing that all you need is a simple update as folows:

"UPDATE T00_JunctionTable_OBS SET " _
& "T00_JunctionTable_OBS.OrganizationIDfk =" & OrganizationID & ",  " _
& "T00_JunctionTable_OBS.BilletIDfk =" & Me.BilletIDfk_New & ", " _
& "T00_JunctionTable_OBS.Record_Modified_Date =  Now() " _
& "WHERE T00_JunctionTable_OBS.RecordIDpk=" & RecordIDpk &";"

...and then, just refresh the form to see the changes of the current staff member.

[skydivetom]

I think continue to think about your "BUT... recommendation". Appreciate your thinking outside the box.

WRT the navigation, neither or the two proposed approaches work.

Let's go back to square one... is there a way to record-navigate using the following SQL (i.e., tie in the SQL into the RecordFind).

SELECT T00_JunctionTable_OBS.StaffMemberIDfk, T00_JunctionTable_OBS.RecordIDpk FROM T00_JunctionTable_OBS WHERE (((T00_JunctionTable_OBS.StaffMemberIDfk)=1194));

where 1194 equals " & StaffMemberIDfk & "

[accesstos]

Hi Tom!

I made a form, similar to yours, based on a previous sample database that you have provided, that illustrates two different methods for the staff member "movements": Directly via the form's controls and via SQL command. In addition, I keep the history of updates of organization and BIN, independently, in a separate table.

I hope that helps.

Cheers,
John

[skydivetom]

John:

Wow... thank you so much for posting this example. I very much appreciate your continued assistance.

I will "play" explore this a bit further. What are the minimum # of queries (in the event I decide not to keep the history table/process)?

Again, thank you!!
Tom

[accesstos]

John:

Wow... thank you so much for posting this example. I very much appreciate your continued assistance.

I will "play" explore this a bit further. What are the minimum # of queries (in the event I decide not to keep the history table/process)?

Again, thank you!!
Tom

My pleasure Tom!

For the basic functionality of the form you need only the "qryOBSs" query.

Good luck!

[skydivetom]

Totally awesome.... I will (attempt to) integrate your form/process into my master file at work tomorrow! Again, I truly truly appreciate your help.

[skydivetom]

John:

I just returned from business travel on Friday. Over the weekend, I spent some time implementing your form-recommended procedures into my current system.

Unfortunately, given that the process is slightly different, I did NOT succeed.

I am hopeful that you're open to some additional help. If you prefer, I will gladly open a new thread. Please let me know either way.
Anyhow, here it is...
- Attached is the modified database (your version) with only the basic query.
- Also attached is a Word document with some screenshots that provides further information that I need to address.
- As I included screenshots and information in bulleted format (in Word), I won't post additional info in this thread.
Again, I do NOT take your help for granted. I very much appreciate it!!! Any additional assistance would be greatly appreciated!

THANK YOU!!!!
Tom

P.S. Lastly, ideally I have some cascading relationships for the two combo boxes on the form. So, when selecting, e.g., "Branch 3", I only want to show "Marketing Analyst" and "Vice President" (vs. showing all positions in the organization).

[accesstos]

Hi Tom!

You're welcome!
Don't feel obligated. I like to spend few hours a day to "sharpening my saw" and here's the best place and you give me the best chance for that work.

About your issue, I think that with a small change in the view of the current data structure you will get that you need.
Open the "qryOrgPositions" query of the attachment, make some "movements" with the form, return to the query and refresh it to see the changes.
The trick is to format the Null positions as "Vacant", on the fly. So, you don't have to take care for those positions.

Once you move a member to a new position or delete the record from the "T00_JunctionTable_OBS", the "position" in "qryOrgPositions" seems as "Vacant". (at this moment, you can't create/delete a record into "T00_JunctionTable_OBS" table from this form)

Let me know if I have missed something.

Cheers,
John

[skydivetom]

John:

Your continued help is absolutly incredible. Appreciate your kind words!

So, please allow me to clarify on the process:
- Ultimately, we "archive" a person who leaves the organization.
- So, when, e.g., "John Wayne" resign (or retires), we bring up the staff member form (not included for the purposes of this demo).
- On this form, we have a command button "Archive Staff Member". Upon clicking/confirming the action, two things are happening in the table. 1) Y/N field "Onboard" is set to false; and 2) an UPDATE query is executed which
sets StaffMemberIDpk = 1 in the OBS's table.
- So, archiving a person vacates his/her most recent position.

Next, the process of "internally filling" shall be achieved with the "Move Form".
- As illustrated earlier, we want to backfill John Wayne's position with current employee Richard Roundtree.
- Right now, based on the latest form that I saw in your most recent database, I ended up with duplicate jobs.

Please see attached Word document w/ additional information and screen shots.

Also, are the two additional queries needed? If yes, would you pls further expand on the purpose of the two queries. Ideally, I already have close to 100 queries in my system and I'm trying to limit the number of new queries at a minimum (if possible).

Thanks,
Tom

[accesstos]

Hi Tom!

About "archiving", a proper way is to keep the details in a separate table (e.g. date, reason, notes etc). So, the active employees will be all employees from OBS's table that doesn't exists in the "archived" table.
(See the new definition of the "Q001_SYS_Billets_Extended" query)

It's up to your business rules -as I have said in the past- if you will allow or deny duplicate jobs. Is not an "in principal" fault and you can control that with several ways. No hurts to assign the same job to more than one employees and then to remove the oldest.
By overwrite the current member of the target-position with the new member, you lose the information of the old staff member and you face the seeking records problems that you already know.

The "qryOrgBilletsCP" combines each organization with all the available billets. It's just a Cartesian product of organizations x billets.
Then, the "qryOrgPositions", using this CP query, illustrates the all possible organization's positions (virtual and actual), showing the word "Vacant" in place of Null positions.
No need to fill in the "vacant" positions "manually" with the value of 1 (or whatever) . Just set the format of the staff member's fields to @;@;[Red]"[Vacant]".

I attach a new sample with my last recommendations.
I hope that this will give an advance to our attempt.

Cheers,
John