Open a form based on a value associated with text in a field

[mindbenderz]

Ok, I have several different record types in a table which are differentiated by a field which assigns what each record belongs to. There are "equip" records and "eng" records.

On a form that displays a filename ("frm_hardcopies"), the field is called "Filename". The equip and eng files are maintained in 2 seperate forms, but both contain the filename value. If I double click on frm_hardcopies.Filename, would it be able to look at tbl_documents.Path (which is the linked filename field), and if that record's "doc_filter" value is "equip", it would open frm_equip_docs, or if the "doc_filter" value is "eng" it would open frm_eng_drawings and go to the appropriate record? The "doc_filter" field is only in tbl_documents, not in tbl_hardcopies.

This is the only code I have so far, which just opens 1 of the forms based on the filename. It doesnt look at the filter criteria yet.

Private Sub Filename_DblClick(Cancel As Integer)
 
On Error GoTo Err_Path_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
    stLinkCriteria = "Path = '" & Me.Filename & "'" 'Assuming that the value you want is in the Path field
 
 
    stDocName = "frm_equip_docs"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
 
Exit_Path_Click:
    Exit Sub
 
Err_Path_Click:
    MsgBox Err.Description
    Resume Exit_Path_Click
 
End Sub

[NeoPa]

I expect it can be done but it's so hard to know without a much better idea of what you're working with. I suggest some Meta Data would help this along. A full description of the data structure and form structure you're working within.

Here is an example of the sort of thing I'm talking about :
Table Name=[tblStudent]

Field           Type      IndexInfo
StudentID       AutoNumber    PK
Family          String        FK
Name            String
University      String        FK
Mark            Numeric
LastAttendance  Date/Time

[mindbenderz]

ok, frm_hardcopies.Filename contains the value that will call to and load either frm_equip_docs.Path OR frm_eng_drawings.Path based on the record value of tbl_documents.Path's corresponding value in the field "doc_filter". If doc_filter = equip Then it would open frm_equip_docs based on

stLinkCriteria = "Path = '" & Me.Filename & "'"

, ELSEIF doc_filter = eng Then it would open frm_eng_drawings based on

stLinkCriteria = "Path = '" & Me.Filename & "'"

[NeoPa]

So, if it's one way it does one thing, and if it's the other it does exactly the same thing?

I'm not sure this helps much to clarify the situation. I was expecting a clarification of the filtering - hence the requirement for meta data (which is still outstanding btw).

[mindbenderz]

no, if its 1 way it should open formA. if if the other way it should open FormB. the value in tbl_documents.doc_filter would tell it which form to open

[NeoPa]

Now I'm really confused. Are you telling me someting is different between the two lines of code posted in post #3, or am I to understand your way of clarifying the situation was to illustrate only the part where they are similar? Twice?

Still no meta data. Still miles from clarity.

[mindbenderz]

nevermind, I got it. I created a hidden dropdown linked back to tbl_documents that pulled up the Path and doc_filter info. The control source for the dropdown is [Forms]![frm_hardcopies]![Filename]. Then, based on the value in the 2nd column of the dropdown (doc_filter), the DblClick event opens the form accordingly. It works so far, however when I go to the next record on frm_hardcopies and try clicking on the field again, nothing happens. I have to figure out how to refresh the event or something so it works every time I use it

Private Sub Filename_DblClick(Cancel As Integer)
 
On Error GoTo Err_Path_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
    stLinkCriteria = "Path = '" & Me.Filename & "'" 'Assuming that the value you want is in the Path field
 
If filt_form.Column(1) = "equip" Then
 
    stDocName = "frm_equip_docs"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
ElseIf filt_form.Column(1) = "eng" Then
 
    stDocName = "frm_eng_drawings"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
End If
 
 
Exit_Path_Click:
    Exit Sub
 
Err_Path_Click:
    MsgBox Err.Description
    Resume Exit_Path_Click
 
End Sub

[mindbenderz]

got that too. I added Refresh before setting Dim in the VBA

[NeoPa]

Congratulations then I guess. I'm pleased you managed to find a solution, but I worry you won't get the best of help in future unless you work out how to ask your questions more clearly. We'll leave that for now though as you got a solution instead. Well done.