Open Unbound form and filter subform

[matt beamish]

Hello folks. I've been battling with this and can't see what I am doing wrong. I am trying to open form2 from form1. Form2 is not bound but has an unbound search that allows users to open it from a menu and find the records they need. I want to open Form2 and pass a text value from Form1 to the unbound control, and then filter a subform (Form3) to another value being picked up from a control on Form1

I am failing at the first, and although the Form2 is opening, I cannot populate the unbound control.

Here is my code

Private Sub Command17_Click()
On Error GoTo Err_Command17_Click

    Dim stDocName As String
    Dim stLinkCriteria, stFilter As String

    stDocName = "F_SampleFractionRecordsAcc"
    
    stLinkCriteria = "[FullAccessioncbo]=" & Chr(34) & [FullAccessioncbo] & Chr(34)
    stFilter = "[SampRecID] = " & [SampleRecIDcntrl]
   
   Debug.Print "Stlinkcriteria: " & stLinkCriteria & " StFilter: " & stFilter
  

    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    'Forms!F_SampleFractionRecordsAcc!F_FractionRecords.Form.Filter = stFilter
    'Forms!F_SampleFractionRecordsAcc!F_FractionRecords.Form.FilterOn = True

Exit_Command17_Click:
    Exit Sub

My debug.print is
Stlinkcriteria: [FullAccessioncbo]="LMARS_XA72025" StFilter: [SampRecID] = 10151

I've tried running with and without the filter on the subform, but my unbound control is not populating. The unbound control is a combo box named FullAccessioncbo

Can anyone help me please?

thanks

 

[Gasman]

Well you are not populating the control. Simple as that.
You can pass in the value in OpenArgs and then test if a value exists in OpenArgs in Form2. If it does, then set your control to that value.
Or you can refer to the Form1 control in the load of Form2 and set it there using Form1 full reference.
Or you can set it from that code after opening the form by using the full form reference of form1.

 

[theDBguy]

I just have to ask, why are you using an unbound form to view records?

 

[arnelgp]

Form2 is unbound, so Criteria is of no use.
you need to set the combox directly:

stDocName = "F_SampleFractionRecordsAcc"
DoCmd.OpenForm stDocName

' set the Form2's combobox
[Forms]![F_SampleFractionRecordsAcc]![[FullAccessioncbo]=" & Chr(34) &  [FullAccessioncbo] & Chr(34)

 

[matt beamish]

Well you are not populating the control. Simple as that.
You can pass in the value in OpenArgs and then test if a value exists in OpenArgs in Form2. If it does, then set your control to that value.
Or you can refer to the Form1 control in the load of Form2 and set it there using Form1 full reference.
Or you can set it from that code after opening the form by using the full form reference of form1.

Thanks. I've found the method below does now open Form2 and populate the control, so progress. But I'm not getting the subform (form3) to navigate to the value of sprid successfully.

Private Sub Command17_Click()
On Error GoTo Err_Command17_Click

    Dim stDocName As String
    Dim srid As String
    Dim sprid As String
    
    stDocName = "F_SampleFractionRecordsAcc"
 
  
    sprid = Me!SampPtRecIDcntrl
 
  DoCmd.OpenForm stDocName
     With Forms(stDocName)
         .FullAccessioncbo = Me.FullAccessioncbo
         .F_FractionRecords.Form.Recordset.FindFirst "SampPtRecID = '" & sprid & "'"
    
     End With
 
Exit_Command17_Click:
    Exit Sub

Err_Command17_Click:
    MsgBox Err.Description
    Resume Exit_Command17_Click
    
End Sub

 

[matt beamish]

I just have to ask, why are you using an unbound form to view records?

the Form is opened by some users to find the set of records they want by running a wild card search and then using the resulting records in the cbo to find the ones they want. There are a number of forms that are then synchronised using various controls and oncurrent events. Most of my forms are bound but I chose this way to have this form perhaps 10 years ago when I first put it together.

 

[matt beamish]

I've tried this way, but Form3 is not picking up the value/navigating

Private Sub Command17_Click()
On Error GoTo Err_Command17_Click

    Dim stDocName As String
    Dim srid As String
    Dim sprid As String
    
    stDocName = "F_SampleFractionRecordsAcc"
 
  
    sprid = Me!SampPtRecIDcntrl
 
  DoCmd.OpenForm stDocName
     With Forms(stDocName)
         .FullAccessioncbo = Me.FullAccessioncbo
      End With
 
  With Forms!F_SampleFractionRecordsAcc.F_FractionRecords.Form.RecordsetClone
       .FindFirst "SampPtRecID = " & Chr(34) & sprid & Chr(34)
  End With
 
Exit_Command17_Click:
    Exit Sub

Err_Command17_Click:
    MsgBox Err.Description
    Resume Exit_Command17_Click
    
End Sub

 

[Gasman]

Walk your code with F8 and breakpoints.