Solved Opening a form from a subform on a tabControl based on two criteria

[Sam Summers]

Once again my senility and early dementia are preventing me from seeing this! After two days just on this pathetic task i am still no-where!

I have tried setting parameters in the query that the form to be opened is based on as well as the different lines of code attempting to achieve similar but all fails!!!!!!

Private Sub Form_DblClick(Cancel As Integer)

    Dim stDocName As String
    Dim dbMyDB As Database, rsMyRS As Recordset
    Dim strInput As String, strFilter As String
    Dim frm As Form

  '      strInput = ComponentID.Value
  '      strFilter = BuildCriteria("ComponentID", dbLong, strInput)
        'rsMyRS.FindFirst strCriteria
 '      DoCmd.OpenForm "EditMaintenanceComponent", , , "MaintenanceListSubform.[VesselID]" & " MaintenanceListSubform.[ComponentID]"
 
 '       DoCmd.OpenForm "EditMaintenanceComponent", , , "MaintenanceListSubform.[VesselID]='" & Me![VesselID] & "' AND MaintenanceListSubform.[ComponentID]='" & Me![ComponentID] & "'"
 
        DoCmd.OpenForm "EditMaintenanceComponent", acNormal
        
 '       Set frm = Forms![EditMaintenanceComponent]   ' Build criteria string.
 '       strFilter = BuildCriteria("ComponentID", dbLong, strInput)  ' Set Filter property to apply filter.
 '       frm.Filter = strFilter
        'Set FilterOn property; form now shows filtered records.
 '       frm.FilterOn = True

End Sub

If anyone can get me back on track that would be great..............

 

[Minty]

Debug.Print is your friend, put your criteria into your strFIlter , then Debug.Print it

However, I don't think you can apply a filter to a sub-form when you are opening the main form like that.

 

[Sam Summers]

Incidentally This is the SQL i have tried in my query of the form i am opening (EditMaintenanceComponent)

SELECT Maintenance.VesselID, MaintenanceComponent.ComponentID, MaintenanceComponent.ComponentName, Maintenance.MaintenanceDate, Maintenance.Status, Maintenance.Comments, DateDiff("d",[MaintenanceDate],Date())
AS Expr1
FROM Vessel
INNER JOIN (MaintenanceComponent
INNER JOIN Maintenance
ON MaintenanceComponent.ComponentID = Maintenance.ComponentID)
ON Vessel.VesselID = Maintenance.VesselID
WHERE (((Maintenance.VesselID)=[Forms]![MaintenanceListSubform]![VesselID])
AND ((MaintenanceComponent.ComponentID)=[Forms]![MaintenanceListSubform]![ComponentID])
AND ((Maintenance.Status) Not Like "1"));

 

[bob fitz]

What was the result of the suggestion from Minty of using

Debug.Print strFilter

 

[Sam Summers]

What was the result of the suggestion from Minty of using

Debug.Print strFilter

Hi Bob and thank you,

Its not even getting that far now?
Its saying it can't find the form referenced.

I'll try again tomorrow, i'm off for a bottle of Whisky!!

 

[Pat Hartman]

Normally, a subform would not be filtered on open. The subform is a child of the mainform and the master/child links control what subform records are displayed.

If the component is a child of the vessel, then on the vessel form you can navigate to the component. If you are having to open the component form from a different main form, perhaps the forms need to be redesigned so that component is not a subform.

 

[arnelgp]

only question, where is the code located?
you need to add the Parent form in your Criteria?

[Forms]![theMainFormName]![MaintenanceListSubform].[Form]![VesselID]
[Forms]![theMainFormName]![MaintenanceListSubform].[Form]![ComponentID]

 

[Sam Summers]

Ok so i had a think after i shutdown and realised that i didnt need to open another form in order to change the status of the component.
I had been overthinking and over complicating the process!

So i changed the 'Status' textbox to a combobox where the user can simply change the status instead of what i was attempting to do was open another form to enable the user to edit the status.

Thank you everyone for your patience with me and your help and guidance (I flippin need it )