Opening sub form from a sub form, in a new window?

[misscrf]

I found this thread: thread702-1083535 , but I don't seem to be getting this right.

I have a main form and it has Deals. This main form has a tab control for information pertaining to a deal. One of those pages has a sub-form for singers.

Once you enter a singer, you can enter in a work history for that singer. To do this, I have a combo box on the Singer subform, which opens the work history form. It is supposed to tie to the current singer from the singer subform on the deals main form.

When I click the command button to open the sub form, it is not filtering correctly. I looked at the code in the above referenced thread and tried to adjust my code to use the recordset clone. I had originally put a command button on the form and used the wizard to say show specific records and then the code populated to do the stlinkcriteria.

So my isssue is 2 fold because I need it to filter to just that singer's work history. The part 2 is if they don't have work history yet, I need it to open to a new record so one can be started.

This is what I have. When I run it, I get an "Object variable or with block variable not set". I am not sure how to fix it:

Private Sub cmdOpenWorkHistory_Click()
On Error GoTo Err_cmdOpenWorkHistory_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmWorkHistory"
    
    stLinkCriteria = "[FKSinger]=" & Me![PKSingerID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

   Dim rst As DAO.Recordset, frm As Form, sfrm As Form
   
   Set sfrm = Forms!frmWorkHistory
   
   Set rst = sfrm.RecordsetClone
   rst.FindFirst "[FKSinger]=" & Me![PKSingerID]
   
   If rst.NoMatch Then
      MsgBox "Singer work history not found!"
   Else
      frm.Bookmark = rst.Bookmark
'
'      Set rst = sfrm.RecordsetClone
'      rst.FindFirst "[fkSinger]=" & Me![PKSingerID]
'
'      If rst.NoMatch Then
'         MsgBox "Singer work history not found 2!"
'      Else
'         sfrm.Bookmark = rst.Bookmark
'      End If

   End If

   Set rst = Nothing
   Set sfrm = Nothing


Exit_cmdOpenWorkHistory_Click:
    Exit Sub

Err_cmdOpenWorkHistory_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpenWorkHistory_Click
    
End Sub

I appreciate any help to get this working. Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

ok, sorry to come back so quick. I found another thread (thread702-958940) after I posted this and I got my code kind of working. (lol).

This is what I have now:

Private Sub cmdOpenWorkHistory_Click()
On Error GoTo Err_cmdOpenWorkHistory_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmWorkHistory"
    stLinkCriteria = "[FKSinger]=" & Me![PKSingerID]
    
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Forms![frmWorkHistory].Filter = "[FKSinger]=" & Me![PKSingerID]
    Forms![frmWorkHistory].FilterOn = True
   
 
Exit_cmdOpenWorkHistory_Click:
    Exit Sub

Err_cmdOpenWorkHistory_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpenWorkHistory_Click
    
End Sub

Here is the new problem. I am now on the work history form. This is a continuous form so that as a new record is created it just shows up underneath the first one. Problem is that I fill out that record and it is not including the singer ID into the FKSinger field. It isn't populating it automatically. I need a way to make it default to the ID of the singer on the Singer subform on the Deals form which is in the back ground. How do I do that please? I am thinking of things like putting a formula in the properties of that field, but not sure if that will work. I will post back if it does.

This is turning into quite the 1 man post as I work this out on paper hahaha.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

Got it. Made the default value = the Primary ID of the parent form. That makes it work. Whew!

misscrf

It is never too late to become what you could have been ~ George Eliot