Looking to add the ability to link to another form using search

[idle]

Hey! First post here! I've been using Access for about 2 years now and my work has requested that I update our old database (from 2004). I have done a number of things, and most importantly, I've added a search function that searches a table.

Here's the background information: I currently am using a search function that searches a table(it works somewhat correctly). As you type into the search bar, results appear in a subform. There is an ID number attached to each record that appears, but the records have 1 of 4 categories, we will call them A, B, C & D. The ID# is a hyperlink, which when clicked, opens that record in it's appropriate form. For example: When you click ID#25, it will open Form A, regardless of it's category.

Here's what I've done: I added this 'search form' myself. As you type, it will match your entry with the 'DESCRIPTION' column of each record and add or remove them from the subform. Then, as I've said before, you are able to click on the ID# and open the form.

The issue: The way I have programmed/coded the subform and the hyperlink, is that no matter the 'type' of form (remember A, B, C or D), it will only open the 'A' type form. This brings an issue that if I click on a 'B' type record, it will open this 'B' type record in an 'A' form and then show no data.

My suggestions: I need (or I assume I need) to have some sort of VBA code that checks the 'type' of each record, so that it opens the appropriate form. For example, clicking on an A-type record opens A but then clicking on an B-type record, opens the B form; so I need to check the type of record before I open the form.



Video: I recorded a video of the error. The error is not in plain site, but I used "thumbs up" and "thumbs down" after each click to signify the issue. in reference to what I said above, the "record type" is the same as the "record category".

Code: This is the code I use now, but needs to be able to open the "Photometric Form", "Other Form", "Electrical Form", "Returns Form", and "View Ceiling Form" as well, not just the "Heat Form". It should check the TYPE of form, THEN open it; not just default to HEAT.

Private Sub idNum_Click()
On Error GoTo myError
Dim varWhereClause As String
varWhereClause = "[id#] = " & Me!idNum
DoCmd.OpenForm "Heat Form", , , varWhereClause
leave:
Exit Sub
myError:
MsgBox Error$
Resume Next
End Sub

In the video, you can see me open Access, then click on the "search" tab on the Main Form. Then, I type "E3" and results begin appearing in the subform. I then click on an ID# where it's category is HEAT; and the record opens in the HEAT form (as it should, since it is a HEAT category). I then click on a record of category "PHOTOMETRIC" and it also opens in the HEAT form, which it should not (but I want it to) since it is a PHOTOMETRIC category.

Thank you guys so much!

I am uploading a video right now to Youtube for a visual explanation. It's HD so it may take a second.

EDIT: Video link... http://youtu.be/A6h6NKIGn6U

[burrina]

Your going to need to filter by your category!

[idle]

Okay. I just don't know how to do that!

[June7]

Private Sub idNum_Click()
On Error GoTo myError
Dim varWhereClause As String, Dim strForm As String
varWhereClause = "[id#] = " & Me!idNum
Select Case Me.Category
Case "A"
strForm = "Heat Form"
Case "B"
strForm = "Photometric Form"
Case "C"
strForm = "Other Form"
Case "D"
strForm = "Electrical Form"
Case "E"
strForm = "Returns Form"
Case "F"
strForm = "View Ceiling Form"
End Select
DoCmd.OpenForm strForm, , , varWhereClause
leave:
Exit Sub
myError:
MsgBox Error$
Resume Next
End Sub


BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be IDNUM or ID_Num.

[idle]

Thank you!

When I clicked the ID# hyperlink, I get this prompt: "The action or method requires a Form Name argument."

I will google and check the code for errors.

EDIT: SOLVED! So, I needed to change the "A", "B", etc. to "Heat", "Photometric", etc.

Thank you so much! HUGE help and my multi-million dollar business thanks you!

[June7]

Maybe don't even need the conditional code. Consider:

DoCmd.OpenForm Me.Category & " Form", , , "[id#] = " & Me!idNum

Also, advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be IDNum, ID_Num, ID_No.