none
Button to open "Next Step" form, following the previous one?

    Question

  • Dear all, I have the following challenge in Access (I use version 2016):

    I have a form that I have created using a query, which query was produced by selecting two variables ( ID and CR_Date) from 6 tables (Step1, Step2, …,Step6). The query also assigns a new variable, called STEP, which takes the values Step1_Created, Step2_Created, etc. if these steps have been already completed.

    Here is the syntax:

    SELECT Step1.ID, Step1.CR_Date, 'Step1 Completed' as STEP

     FROM  Step1

    UNION

    Step2.ID, Step2.CR_Date, 'Step2 Completed' as STEP

     FROM  Step2

    UNION

    …………………….

    …………………….

    …………………….

    Step6.ID, Step6.CR_Date, 'Step6 Completed' as STEP

     FROM  Step6

    ORDER BY ID, STEP;

    This produces the following result for a particular ID (which I open in a form):

    ID               CR_Date             STEP

    2019-1        2/17/2019        Step1 Completed

    2019-1        2/22/2019        Step2 Completed

    2019-1        3/10/2019        Step3 Completed

    2019-1        3/12/2019        Step4 Completed

    (other IDs to follow which will have some or all STEPS completed)

    I have hyperlinked the variable STEP to get into the form with more detail for that step (the forms are created from the respective tables Step1, Step2, ect.) What I REALLY NEED to achieve is to have a button (for example once I am in the form Step4), which on click, would automatically open the form Step5 to be filled in, and if possible, automatically populating the ID field with the respective ID (2019-1 in the example above). Please let me know if this makes sense and if you this is possible?

    Thank you in advance for looking into this problem!

    Friday, March 29, 2019 3:07 PM

Answers

All replies

  • Hi. If each step uses its own form, then opening the next form should be trivial. For example, if you're on Form4, you already know the next form should be Form5. So, just code it so it opens the form for Step5.
    Friday, March 29, 2019 5:14 PM
  • Thank you for this! It makes sense, but in practice, how do I code it so that it opens Step5 (per this example). For example, I can place a button that opens a form, but how should I code it so that it can follow the sequence? It seems that the logic should be something like: if Step1 is complete, then go to Step2, etc. , but I am lost as I need the exact syntax (I had a work around, when I placed and called a button Next Step, which lead to a form where each of the 6 steps had its own button, so the user picks manually the next step - but this solution is not good enough for my boss). So what should I do so that for example, Next Step button opens exactly the next step in the sequence? Thank you again! if you have a chance to elaborate, I'd appreciate it!
    Friday, March 29, 2019 8:17 PM
  • Hi. In the sample data you posted earlier, steps 1 to 4 are completed. So, if step 5 is not yet completed, is there no link to open it? Meaning, you want the user to click the step 4 completed link and from the step 4 form to click a button to complete step 5? Also, if steps 1 to 4 are completed, can the user click on any of the links to open its form? For example, can they click on step 2 completed to view its form? If so, what step form should open if they click Next in the step 2 form, if all steps all the way to 4 are already completed?

    Friday, March 29, 2019 8:23 PM
  • On the first question, there is no link to open Step5 (a link only exists and appears if the respective step form is filled in for each ID). Yes, I meant the user to click on Step4 and from Step4 to click a button to open Step5. Yes, if 1 to 4 are completed, the user can click on the respective link and open its form. To the last question, I have a button in each of the step forms called NEXT STEP, which when clicked, takes the user to a form with all six steps forms, and the user selects the form (this is my work-around). But this is not a good solution, as they may be in step4 but open step3 instead of step 5 and start filling it by mistake.... Let me know if I can clarify further?

    I am not stuck up on the solutions so far and am open any suggestions that come up with the result I am seeking or at least a more optimal one

    Friday, March 29, 2019 10:55 PM
  • One more thing to add; for each ID the number of steps completed could be different; for example for 2019-1 there are four steps completed, but for 2019-2, there maybe only 2 steps, etc. Regarding your first question, it would be good if there are links for each step - six links for every ID, but have the ones that are already filled (the first 4 in my example)appear in solid color, and the remaining 2 steps dimmed, to indicate that they are not filled yet. Not sure if that's possible at all, just got the idea from your first question...

    Again, I am open to any workable solution!

    Friday, March 29, 2019 11:03 PM
  • I was able to make some progress on my issue, by adding a button to this form, that would open the new Step5 form to be filled in (I assigned the button to open the Step5 form). When it is filled in, it registers as Step5_Completed. My problem is how to code the Next Step button to open Step6 form from there?

               ID               CR_Date             STEP                             "Next Step" button here             

    2019-1        2/17/2019        Step1 Completed

    2019-1        2/22/2019        Step2 Completed

    2019-1        3/10/2019        Step3 Completed

    2019-1        3/12/2019        Step4 Completed

    Monday, April 1, 2019 2:00 PM
  • Hi. I'm not sure I am following your situation without seeing what you got. You originally said you have created a separate form for each step. I took it to mean for step 1, you may have created form1, for step 2, form2, and so on. If so, then in form1 to go to step 2's form, then you can simply open form2. So, if the user opened form4, for step 4, then to go to step 5, simply open form5. It might get easier to help you if you could share a copy of your db, so we can better understand the problem.
    Monday, April 1, 2019 3:43 PM
  • Thank you for getting back to me! I understand it is not straight forward, or at least could be better explained. I will try to put together a sample and make it as clear as possible. I tried pasting a picture but the forum format would not allow it.... I'll be back with more info shortly.

    Thank you again - I appreciate it!

    Monday, April 1, 2019 3:54 PM
  • I have 6 tables (Step1, ... , Step6). Each of them has 5 to 8 fields, and the only common key is ID, in format

    yyyy-00. I currently have 14 registrants, so the ID's go from 2019-01, 2019-02, 2019-03, etc.

    For each of the 6 tables, I have created a form (FStep1,FStep2, ..., FStep6). The forms need to be filled with registrants' data in sequence (for ex: one should not be able to fill FStep3 before filling FStep2).

    I have created another form with selected few fields from the original Step tables, which contains only fields that provide a general overview, which looks like this:

    ID             Name       CR_Date                   Agent         Status

    2019-01      Sam B.     21.01.2019         JD            Open

    2019-02      Ian P.       29.01.2019         XP            Prospect

    2019-03      Cam F.     13.02.2019         JD            Open

    .etc........

    The Name field values are hyperlinked, such that on Click, it produces Steps history for every ID:

       ID               CR_Date             STEP                                           

    2019-01        2/17/2019        Step1 Completed

    2019-01        2/22/2019        Step2 Completed

    2019-01        3/10/2019        Step3 Completed

    2019-01        3/12/2019        Step4 Completed

    In this form, Step is also hyperlinked (to provide detail if clicked). What is important for me, however, is to find a way from here to open the form that follows the sequence (in this case - FStep5) to a new record to be filled in for this ID (2019-01). Once filled, it will automatically appear as Step5_Completed on the list above(because of the SQL code that I shared at the very beginning of my question, using UNION between the Steps in the Select statement).

    I hope this is clearer (although it may still sound complicated), but please let me know if you have any questions.

    In a nutshell: I need to open a form, conditional on another form been filled, for a particular ID. Does that seem possible? Thanks for looking into it again!

     

    Monday, April 1, 2019 4:53 PM
  • Hi. What you want is probably possible but wouldn't it be easier to let the user click on the link Step4 Completed to open FStep4, which should have a button to open FStep5? I mean, on your form listing the completed steps, where would you want the user to click?
    Monday, April 1, 2019 7:47 PM
  • That's what I thought too; place a button on the form with the completed steps; but the challenge for me is how to program the button to open exactly on the next form that needs to be filled out for the particular ID?

    To your first point, placing the button in the opened form would be equally acceptable to me; but the next step needs to vary depending on the step history of each ID. And it would be great if upon opening, the ID is pre-filled in the form to be filled as next step. I know, it's kind of tricky, but maybe you have an idea?

    Monday, April 1, 2019 8:18 PM
  • That's what I thought too; place a button on the form with the completed steps; but the challenge for me is how to program the button to open exactly on the next form that needs to be filled out for the particular ID?

    To your first point, placing the button in the opened form would be equally acceptable to me; but the next step needs to vary depending on the step history of each ID. And it would be great if upon opening, the ID is pre-filled in the form to be filled as next step. I know, it's kind of tricky, but maybe you have an idea?


    Hi. When the user clicks on the Step4 Completed link, does it open fStep4 with the correctID? If not, then you should probably not use a hyperlink (or try to modify it to include the ID). Can you show us the address property of the hyperlink? Thanks.
    Monday, April 1, 2019 8:25 PM
  • Yes it does open FStep4 completed with correct ID. The problem for me is when I create the button in design mode to open FStep5, I only manage to open a blank new record for FStep5, without the ID being populated (populating it manually has big potential for human error). The other problem is that when I create the button, I "hard code" it to open specifically form FStep5, because I don't know better, so hoping for a possibility to program the button to open the needed next step (not necessarily FStep5 as in the example).

    Monday, April 1, 2019 8:44 PM
  • Hi. Can you post the code you're using to open the next form? For example, in FStep4, please post the code you're using to open FStep5. Thanks.
    Monday, April 1, 2019 9:03 PM
  • I did not use specific code, but rather built a button in design mode where I chose Form Operations  and Open Form, then selected my FStep5, and called the button "Next Step". Obviously, not good enough, but a blank FStep5 does open up...
    Monday, April 1, 2019 9:18 PM
  • I did not use specific code, but rather built a button in design mode where I chose Form Operations  and Open Form, then selected my FStep5, and called the button "Next Step". Obviously, not good enough, but a blank FStep5 does open up...

    Okay, try this, go to design view of form and select the "Next Step" button. In the Click event, replace [Embedded Macro] with [Event Procedure] from the dropdown box and then click on the three dots next to it. Then, enter the following line of code:

    DoCmd.OpenForm  "FStep5", , , "ID=" & Nz(Me.ID,0)

    • Edited by .theDBguy Monday, April 1, 2019 9:24 PM
    Monday, April 1, 2019 9:23 PM
  • Great - thank you very much! I will try this first thing in the morning and will share outcome with you!

    I don't have Access in front of me right now, but isn't the DoCmd.OpenForm  refer to FStep5 only? In any case, eager to give it a try and will get back to you first thing tomorrow (I am on Central Europe time)

    Monday, April 1, 2019 9:38 PM
  • Great - thank you very much! I will try this first thing in the morning and will share outcome with you!

    I don't have Access in front of me right now, but isn't the DoCmd.OpenForm  refer to FStep5 only? In any case, eager to give it a try and will get back to you first thing tomorrow (I am on Central Europe time)

    The code I gave you does open FStep5 specifically, but if since we're putting this code in FStep4, then it does exactly the job you wanted, doesn't it? From Step 4, click a button to open the next step (Step 5). Since you have separate forms for each step, when you click the button on each form, then this code will open only the form for the next step. Make sense? Please give it a try and let us know how it goes. If you want to try it out on Step 3, then change the name of the form to open to FStep4, and so on.
    Monday, April 1, 2019 9:43 PM
  • Makes sense! Thank you!
    Monday, April 1, 2019 10:02 PM
  • Now, it would have been a different story if you told us you only had one form, which could display any of the steps and would like a button to dynamically determine the next step and open the for it. However, I am guessing the reason for separate form for each step is because each step requires a different set of data/input. If so, then I think this is the simplest solution for you. Again, if we had a copy of your db, we would have worked out a good solution by now.
    Monday, April 1, 2019 10:09 PM
  • So, the code opens a blank FStep5 form for this particular ID (2019-01) (however does not populate ID). I filled the form and created another one for FStep6 and that works too. The problem is that, for the next ID  I open the Step history (2019-02), it is at Step3, so I need to create the button again to open FStep4. And every time a new ID is entered, buttons need to be created for every step that needs to be filled. You are correct that each step (form) requires different set of data. I guess it would be best to send you a sample DB which I started putting together, which mirrors exactly the original db. How could I share the DB with you, as I don't see an option for attachment?

    Thanks!

    Tuesday, April 2, 2019 8:04 PM
  • You can email it to me (email address should be in my profile).
    Tuesday, April 2, 2019 8:14 PM
  • PS. I thought you might say the form will open up blank (since it's been opening blank before using a macro). If so, go to the design view of the form and make sure Data Entry is set to No.
    Tuesday, April 2, 2019 8:18 PM
  • OK, will do - thank you! I will check that and will send DB first thing tomorrow! Hopefully it would make it clearer where I am at and what I am asked to produce; but I understand I might need to tweak the design to get there ...
    Tuesday, April 2, 2019 8:31 PM
  • Hello, I sent you an email to the address in your profile. Did it reach you?
    Wednesday, April 3, 2019 6:20 PM
  • Hi. Yes, I got it. But since you didn't send me your database, to make things easier, it will take me some time to create a database for you to duplicate what you already have. I'll let you know when I am done.
    Wednesday, April 3, 2019 6:35 PM
  • Thank you very much - appreciate it! I don't have Access at home to create it on my end, unfortunately ...
    Wednesday, April 3, 2019 7:09 PM
  • Hi. I created a demo for you today. I hope it's close enough to what you wanted. Cheers!
    Sunday, April 7, 2019 2:13 AM
  • Thank you very much - I did see your email! Should be able to look at it fist thing tomorrow morning. I also hope it's close enough to what I need :)  ... I tried to upload and send you my DB again, but not sure it you could open; could you please try, just in case. But in any case, I will get back to you tomorrow to let you know if your solution covers my need!

    Cheers!

    Sunday, April 7, 2019 4:40 PM
  • I got your db and sent it back to you. Let me know if it doesn't work.
    • Marked as answer by Access_fan Monday, April 8, 2019 12:10 PM
    Sunday, April 7, 2019 7:23 PM
  • Thank you very much DBguy! I appreciate your effort and figuring out what I need, even if I may not have always been clear explaining the steps!

    I have one more question, if I may: Is it possible for the button "Next Step" to be coded such that it means "Next Not Completed Step", taking the user directly into the next step that needs to be completed? For example, on the Form Landing Page, if you click on Sam B, it takes you to the history for ID=2019-01; from there, no matter where the cursor is, clicking the green Next Step button to always take you to the next step to be completed for the particular ID (in this case for 2019-01, into Step 5)? I understand this may be somewhat unconventional and more complicated to code if at all possible, but asking jus in case.

    Thanks in advance for letting me know!

     
    Monday, April 8, 2019 12:25 PM
  • Hi. Sure, almost anything is possible with VBA. If the form showing the list of completed steps will only ever be opened using the landing page, then it should only contain a single ID. From there, you should be able to figure out which step is the next one and code the button to open the form for it. As it stands right now, it is code to the ID matching the currently selected ID and step because I thought the list of completed steps might show all the IDs at the same time.
    Monday, April 8, 2019 3:08 PM
  • Thanks for this - I understand what you're saying. For the user experience, the Step History Query Form will never show up entirely; it will only show the steps completed for the particular ID selected; that's why if I have Next Step programmed such that it opens the Next Step that is currently empty and needs to be completed , that would be great! The other question is what do I do when Step 6 is completed, and if user still clicks Next Step, then there is a Run-time error (which would confuse the user). Are there options to get rid of the error showing?
    Monday, April 8, 2019 3:28 PM
  • Yes, you have a couple of options. You could check if the current/last step completed is equal to 6, then don't open any more form (you can issue a MsgBox instead), or you could add an error handler in the code to simply trap the error and either ignore it or show a message to the user "there's no more next step." Personally, I would probably check if all steps were completed and just disable the button, so the user can't click it.
    Monday, April 8, 2019 3:34 PM
  • Great - thank you! I'll give it a try...
    Monday, April 8, 2019 9:09 PM
  • I have another question, closely related: when I have to add a new user (new ID), it should start from filling FStep1. When I open the form FStep1 (using DoCmd.GoToRecord , , acNewRec on Load) , it does open it for a new blank record to be filled in. How could I in addition have the next in line ID auto-populated? I tried adding "+1" as you showed me for completing Next Steps, but can't get the syntax right, and since this is the first step, I wonder what would be the correct command? 

    Please let me know if I need to submit this as a new question instead?

    Thank you as always!

    Monday, April 15, 2019 1:34 PM
  • Hi. How/where exactly are you opening the step 1 form? Your list of completed steps wouldn't show the new user, right?
    Monday, April 15, 2019 4:02 PM
  • No, it wouldn't. But opening form FStep1 I consider to be the starting step for a new user. I will plan to have a button called New User Entry which will point to FStep1. Currently, I am at ID 2019-04. I would like on opening FStep1 form, the ID field to be populated as 2019-05 (in this case), and to increase by 1 with every new user...
    Monday, April 15, 2019 9:43 PM
  • Okay, that should be easy to do with DMax(). Look up some demos on "custom autonumbers" to see how to use DMax() to get the next value to assign.
    Monday, April 15, 2019 9:45 PM
  • Dear DBguy, one more question from me if you could help me. Your code in the db that you last sent me works great! It opens the Next Step form for the selected ID and also places that ID in the ID field, which is exactly what I needed. Here is the code: 
     DoCmd.OpenForm "FStep" & Val(Me.STEP) + 1, , , "ID='" & Me.ID & "'", , , Me.ID

    The DB that I sent you and you added that code has 6 steps (FStep1 through FStep6). I created a new step, FStep7. But when an ID that has reached Step 6 and Next Step is Step 7, the form FStep7 opens, but the ID field is blank. Why is this happening, do you know? Is it something in the forms FStep1 ...FStep6 that you have enabled, and it is not enabled in FStep7? Hope you can shed light on this, as it will come again as I would need to add additional steps.

    Also, let me know in case it is not quite clear what I am asking and I can give more detail.

    Best regards!

    Wednesday, April 24, 2019 9:31 PM
  • Hi. Can you resend me the latest copy of your db with the Step 7 included? I just want to make sure nothing broke.
    Wednesday, April 24, 2019 9:39 PM
  • Yes of course! Just sent it to you, along with the questions. I tried hard to solve myself, but am not successful to figure out what is causing the ID field to populate for all 6 steps, but not for the newly created step 7.

    Please let me know if you received it and managed to open? Best regards,

    9 hours 51 minutes ago
  • Did my email and db reach you?
    40 minutes ago
  • Did my email and db reach you?
    Hi. Yes. I will look at it tonight and let you know.
    37 minutes ago
  • Yes of course! Just sent it to you, along with the questions. I tried hard to solve myself, but am not successful to figure out what is causing the ID field to populate for all 6 steps, but not for the newly created step 7.

    Please let me know if you received it and managed to open? Best regards,

    Hi. Just go to the design view of FStep7 and add the following code in its Open event.

    Private Sub Form_Open(Cancel As Integer)
    'thedbguy@gmail.com
    '4/7/2019
    
    Me.ID.DefaultValue = """" & Me.OpenArgs & """"
    
    End Sub
    

    32 minutes ago
  • Thank you - that's great! Will give it a go when I'm at work in a few hours and will let you know, but seems like it was immediately clear to you what the issue was - appreciate it!
    7 minutes ago