referencing subform from a subform not working - please help

[krowe]

Hi , i know this will be really easy for someone here, but im struggling to get this working.

I have parent form (frmRIASelectApplication) and sub form (sfrmRIASelectApplication) . The subform is a datasheet. I am creating a dblclick event on the Control StandardLetterID of the subform.

I need to open Parent form frmRIAProcess where its subform frmRIAApprovalRequest is equal to the subform sfrmRIASelectApplication.

This is the code im trying, but it just opens on a new record each time:

   Private Sub StandardLetterID_DblClick(Cancel As Integer)
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frmRIAProcess"
 
    stLinkCriteria = "Forms![frmRIAProcess]![frmRIAApprovalRequest].Form![StandardLetterID]=" & Me![StandardLetterID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close acForm, "frmRIASelectApplication", acSaveYes
End Sub

Can anyone please tell me where im going wrong. I've tried using the whole whole path instead of Me (Forms![frmRIAProcess]![frmRIAApprovalRequest].Form![StandardLetterID] but this did not work either.

Many Thanks

Kev

 

[vbaInet]

If the subform is linked to the parent form, you filter the parent form and the subform will filter down.

 

[krowe]

but i need to display not only the correct person in frmRIAProcess, but the correct application in the subform frmRIAApprovalRequest.

the parent form doesnt have a control called StandardLetterID only the subform

the field that links both parent forms with both subforms is PersonID, does this help?

 

[krowe]

im wondering if i could use openargs for this instead, but not sure exactly how to do this with 2 forms and 2 subforms.

 

[jdraw]

Perhaps you can do some tests by altering the Record source of the Form or the linking criteria. Just a thought.
If you can get it working by restricting to one parent record, then you can apply whatever you find to resolve the problem.

 

[krowe]

this code works fine to give me the correct record within the parent form, however multiple records show up in the subform (as expected with this code)

    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frmRIAProcess"
    
    stLinkCriteria = "[PersonID]=" & Me![PersonID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

I'm at a loss as to how to then filter the subform to get the record with the correct StandardLetterID, although i'm thinking a 2-step process is needed i dont know how to do this.

 

[boblarson]

The easiest way I've found to get the actual syntax you need for something as confusing as this is to use this method:
http://www.btabdevelopment.com/ts/refer2sfrms

And you start by opening the main form and then click on the subform control which is the one you want to use the code from and then follow the method I outline there.

But a quick reference is if you are referencing another subform on the main form it would be:

Me.Parent.Subform2ControlNamehere.Form.ControlNameHere

Where Subform2ControlNameHere refers to the subform control that houses/displays the other subform on the parent form (not the subform name itself, unless the control and the subform are named exactly the same).

The .Form. part needs to remain AS SHOWN - you don't substitute a name or anything there, the .Form. part tells Access you want to refer to something on that subform and not on the subform control itself. But if the subform and subform control are named the same, you can omit that .Form. part.

 

[krowe]

I'm afraid i'm not entirely understanding that link

Ive put a control on the main form and it gives me this syntax when i add in the controlsource
[sfrmRIASelectApplication].Form![StandardLetterID]

this is the subform I am double clicking on (so i am subsituting in me.)


the syntax for the subform i am opening and want to filter the results is:
[frmRIAApprovalRequest].Form![StandardLetterID]


my code is therefore:

    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frmRIAProcess"
    
    stLinkCriteria = "Forms.[frmRIAProcess].[frmRIAApprovalRequest].Form![StandardLetterID]=" & Me.[StandardLetterID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

but this still gives me a blank form.

Sorry to be a pain, but i really cant make head nor tail of this!!!!

 

[boblarson]

Can you post a copy of your database with fake data? If you can, first run Compact and Repair and then right click on the file and select SEND TO > COMPRESSED FOLDER which will create a zip file of it. Upload that and include instructions on what form to open, what items we're dealing with, etc.

 

[krowe]

Ok, here is a mock up of what im trying to do. I've used the same form names and field names where possible.

I want to be able to double click on StandardLetterID on frmRIASelectApplication and have it open frmRIAProcess filtered with just the StandardLetterID record. I have put in the code I think should work, but isn't.

Thanks for all your help so far

 

[boblarson]

Okay, so you don't reference the subform in the DoCmd.OpenForm bits. If you want to open it only to that one record you can do it in one of two ways. You open the form filtered on PersonID and include the StandardLetterID in the OpenArgs and then deal with it in that other main form's code as it is opened OR you can do it the way I am showing right here:

Private Sub StandardLetterID_DblClick(Cancel As Integer)
Dim stDocName As String
    Dim stLinkSubformCriteria As String
    Dim stLinkCriteria As String
    stDocName = "frmRIAProcess"
    stLinkCriteria = "[PersonID]=" & Me.PersonID
    stLinkSubformCriteria = "[StandardLetterID]=" & Me.[StandardLetterID]
    
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    With Forms!frmRIAProcess.frmRIAApprovalRequest.Form
        .Filter = stLinkSubformCriteria
        .FilterOn = True
    End With
    
End Sub

 

[krowe]

boblarson you are a legend, i would have never been able to do that without your help!!!

Thanks