Experts:
Several months ago, I developed a process which allows me to re-assign staff members (employees) across N-Codes (organizations/offices). Please find attached a sample db with only the pertinent objects (and dummy data).
Upon opening the database, please check out the following (this existing process works fine):
1. Open form "F134_N1S_MoveStaffmemberNCode"
2. From the "Go To" drop-down, pick any staff member (employee). Note: The yellow text boxes (storing ID numbers) are usually hidden but will help w/ the process modification.
3. The current information (e.g., "Current N-Code", "Current Organization", etc.) are displayed in the upper section.
4. Now, in the lower section, select any new N-Code.
5. Finally, click on command button "Move Staff Member" and then following the prompts. Note: Once the "move" has been executed the current information section is updated accordingly.
Again, the above process works well and does NOT need any modification.
Now, here's what I need some help with. First, allow me to provide some additional information though.
a. Open table "T102_N1S_Staffmembers". You will notice that my "first staffmember" = "[Vacant]". This is a "dummy" record utized for new and/or unfilled positions. I won't go into any further details about the significance of this process here.
b. Now, open table "T103_N1S_Billets"... please note records #8 and #9... both of them are labeled "New Position #".
c. Next, please review table "T101_N1S_JunctionOBS"... this is where I store the ID combinations across all 3 tables (staff members, billets, organization). Again, so far so good.
d. In the table "T101_N1S_JunctionOBS", I currently store staff member "[Vacant]" [ID # = 1] with billets 8, 9... and some organization ID.
Thus far in my project, I only had to "move assigned" staff members across N-Codes (organization). So, I used form "F134_N1S_MoveStaffmemberNCode" to execute the process.
Now, however, there's a requirement to also move "vacant" (dummy) staff members across N-Codes.
Here's what I've done so far but it doesn't work for the latter process:
1. Copied query "Q134..." and labeled it "Q135..."
2. Copied form "F134..." and labeled it "F135..."
3. Made some changes to the row source of both queries and form (Q135/F135).
4. Tried to follow the same process as I've done in moving an assigned staff member (F134); but now with F135, I cannot move a vacant billet from one N-Code to another.
Could anyone please assist me with identifying the missing pieces in my VBA so that I can also move vacant billets across organizations? Please note that a vacant billet (i.e., staff member = vacant) can/should be moved into any of the available organizations.
Thank you in advance,
Tom