none
Hyperlink on different values in one field to open different Forms RRS feed

  • Question

  • In an Access 2016 Form, in a field called Status, I have 3 different values (Step1, Step2, Step3). My challenge is using a hyperlink, to make each value (Step) through a click, open a respective table (Table1, Table2, Table3). Could this be done? Greatly appreciate any help!

    Wednesday, February 20, 2019 1:32 PM

Answers

  • Hyperlink? Use a combobox with the 3 values, then in its AfterUpdate event open the corresponding form. Something like:

    select case Me.myCombobox
      case "Step1"
        docmd.openform "frmStep1"
      case "Step2"
        docmd.openform "frmStep2"

    etc.


    -Tom. Microsoft Access MVP

    • Marked as answer by Access_fan Monday, February 25, 2019 3:39 PM
    Wednesday, February 20, 2019 2:10 PM

All replies

  • Hyperlink? Use a combobox with the 3 values, then in its AfterUpdate event open the corresponding form. Something like:

    select case Me.myCombobox
      case "Step1"
        docmd.openform "frmStep1"
      case "Step2"
        docmd.openform "frmStep2"

    etc.


    -Tom. Microsoft Access MVP

    • Marked as answer by Access_fan Monday, February 25, 2019 3:39 PM
    Wednesday, February 20, 2019 2:10 PM
  • Thank you very much for the advice! Code works great! One additional question that I should have included initially: the  data in the Base Form contains a multitude of IDs (key var is ID). This key var is also present in the forms Step1, Step2 and Step3.

    Currently, applying the code, opens exactly the form I want (for example, Step1), but it has the records for all IDs on file (in Columnar format). How can I modify the code such that, for example for ID 007, form Step1 only has the information for that particular ID? Thanks again for your help!

    Wednesday, February 20, 2019 3:42 PM
  • docmd.OpenForm "frmStep1",WhereCondition:="ID=" & Me.ID


    -Tom. Microsoft Access MVP

    Wednesday, February 20, 2019 4:31 PM
  • Thank you Tom for following up again! I applied immediately with the last addition to the code, and the correct form opens up for the selected ID. The only issue I am having, is that any form that opens (Step1, Step2 or Step3), has all fields blank, as if it is open to add a new record. What I thought it would show is the history of each ID (by Step). The code seems very logical and I thought it should be showing exactly this. However, it looks like either I am missing something, or possibly placing it in the wrong spot ... If you have a chance to look at it again, I would be most grateful! Here is the code as used by me:

    Private Sub STATUS_Click()

    Select Case Me.Status

       Case "Step1"

         DoCmd.OpenForm "frmStep1",  WhereCondition:="ID=" & Me.ID

       Case " Step2"

         DoCmd.OpenForm " frmStep2",  WhereCondition:= "ID=" & Me.ID

        Case " Step3"

         DoCmd.OpenForm " frmStep3", WhereCondition:= "ID=" & Me.ID

         End Select

    End Sub

    Thursday, February 21, 2019 12:47 PM
  • A few more things to check:

    1. you have Option Explicit at the top of every module.

    2. frmStep1 and its cousins have Form.DataEntry property set to False.

    3. Set a breakpoint on the DoCmd.Openform line and confirm that the Where-condition resolves to something like "ID=5".

    4. "ID" is in fact the name of a control on the main form, and it has a numeric value.


    -Tom. Microsoft Access MVP


    <style></style>
    Thursday, February 21, 2019 1:09 PM
  • On 1., I do not see Option Explicit anywhere (code looks exactly as I pasted on top)

    On 2., in the Property Sheet, under Form (for frmStep1 and its cousins, I had Data Entry = 'No'. I changed to 'Yes', but just that did not make a difference

    On 3., I am not exactly sure how setting a breakpoint is done (I am sorry, I am quite new to coding) and neither how to confirm that Where-condition resolves to like "ID=5"

    On 4., ... this may be the key to the error; ID is on both the main and the step forms, but is not numeric. The reason is that it has to be in the format "2019-05", and there is no format yyyy-mm, so I had set is as text. Could that be the reason for the issue?

    Thursday, February 21, 2019 2:07 PM
  • Thank you Tom again for your support on this! It moved things a lot for me; I wasn't however able to figure out the last part ..., the where condition  "ID=" & Me.ID, would take me to a blank form; with the correct fields, but no history. Still trying to resolve this to finalize the projects. In case you have any other ideas, I'd appreciate it!
    Monday, February 25, 2019 3:43 PM
  • > ID 007

    > ID is on both the main and the step forms, but is not numeric. The reason is that it has to be in the format "2019-05"

    That seems a contradiction.

    What is the data type of the ID field? Depending on your answer the WhereCondition will have to be adjusted.


    -Tom. Microsoft Access MVP

    Tuesday, February 26, 2019 3:47 AM
  • Re #1: I want you to type "Option Explicit" as the second line in EVERY module, and also set it to be the default for new modules by checking Code Window > Tools > Options > Require Variable Declarations.

    Re #2: It should be set to No. See help file for details.

    Re #3: Then you need to learn to do this asap. Maybe my videos will help: https://www.youtube.com/results?search_query=stiphout+mvp+hour+debugging


    -Tom. Microsoft Access MVP

    Tuesday, February 26, 2019 3:50 AM
  • You are right, I provided the example ID 007 just to make the point that there are a number of IDs and each has the steps, but I was not thinking about the importance of the type, hence this example was misleading. ID is indeed in text format, in the form 2019-XX, with XX standing for the number in sequence (not month). For example, the first ID for 2019 would be 2019-01, the second 2019-02, etc. , in 2020, the IDs would start form 2020-01 again. You suggest that the WhereCondition could be possibly adjusted to account for the text type? Maybe that would resolve the issue and provide the solution to this particular task?

    Many thanks!

    Tuesday, February 26, 2019 10:26 AM
  • For text data type, we wrap the value in single-quotes:

    WhereCondition:="ID='" & Me.ID & "'"

    FYI: for date values we wrap in #-signs.


    -Tom. Microsoft Access MVP

    Tuesday, February 26, 2019 1:29 PM
  • Thank you very much! This is exactly what I needed!!! I'll be sure to watch the videos as well!

    Cheers!

    Tuesday, February 26, 2019 4:05 PM