Need to open one of two forms by clicking into a third form's field

[rc17wagg]

I have three forms: (1) A list of all CPARs in my system. Some are open, some are closed. The open ones are query-limited and show only in this form: "CPARsForm". The closed CPARs are query-limited and show only in this form: "CPARsForm_Closed".

When I open the list, I would like to be able to click the CPAR number (or ID) and have code find the right form to open and open to the right CPAR (the one I clicked on).

Here's my code:

Private Sub ID_Click()
On Error GoTo Errhandler
 
    With CodeContextObject
       DoCmd.Echo True, ""
        If (IsNull(.ClosedDate)) Then
            DoCmd.OpenForm "CPARSForm", , "", ""
 
            Dim strCriteria As String
            Dim rs As DAO.Recordset
 
            strCriteria = "[ID] = " & Me.[ID]
            Set rs = [Forms]![CPARsForm].RecordsetClone
 
            rs.FindFirst strCriteria
 
            If rs.NoMatch Then
                MsgBox "No match found"
            Else
                [Forms]![CPARsForm].Bookmark = rs.Bookmark
                [Forms]![CPARsForm].SetFocus
 
            End If
 
            rs.Close
            Set rs = Nothing
        Else
            DoCmd.OpenForm "CPARsForm_Closed", , "", ""
 
            Dim strCriteria2 As String
            Dim rs2 As DAO.Recordset
 
            strCriteria2 = "[ID] = " & Me.[ID]
            Set rs2 = [Forms]![CPARsForm_Closed].RecordsetClone
 
            rs2.FindFirst strCriteria2
 
            If rs2.NoMatch Then
                MsgBox "No match found"
            Else
                [Forms]![CPARsForm_Closed].Bookmark = rs2.Bookmark
                [Forms]![CPARsForm_Closed].SetFocus
 
            End If
 
            rs2.Close
            Set rs2 = Nothing
    End If
End With
Exit_ID_Click:
Exit Sub
 
Errhandler:
    MsgBox Err.Description
    Resume Exit_ID_Click
End Sub
 

Can someone please tell me why it only gives me the No Match found message and doesn't open the correct form.
Thanks

[zmbd]

rc17wagg:
Insert the command stop at line 3

Click into your event to start the code.

[F8] to step thru the code. Pay attention to the variable values by either hovering over them or <ctrl><g> and then type ?variablename[enter] to return the value.

Also, please very carefully follow the trouble shooting steps/section in this article: [*] > Before Posting (VBA or SQL) Code

[rc17wagg]

Thanks for your help but it is rather obscure to me. I'm not well-versed in VBA - just enough to get into trouble. All I'm trying to do is open a different form based on whether or not a record is closed. If it is closed, I want to open the CPARsForm_Closed. If open, the CPARsForm.

Another clue might be that it can't find a match when the event executes.

[twinnyfo]

If your two forms display the same information, why not just use one form? Then, just filter the form by either open CPARs or Closed CPARs. Then your code simply looks like this:

DoCmd.OpenForm "CPARSForm", , , "ID = " & Me.ID

Your code seems overly complex for simply opening a form to a set record.

[rc17wagg]

Thanks! I'll try that instead. Usually the simplest answer is the best. I appreciate your response.