Hyperlink on different values in one field to open different Forms

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!

Answer 1

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

Answer 2

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!

Answer 3

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

---

-Tom. Microsoft Access MVP

Answer 4

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

Answer 5

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>

Answer 6

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?

Answer 7

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!

Answer 8

> 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

Answer 9

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

Answer 10

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!

Answer 11

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

Answer 12

Thank you very much! This is exactly what I needed!!! I'll be sure to watch the videos as well!

Cheers!