Solved Opening a form from a subform on a tabControl based on two criteria (1 Viewer)

Sam Summers

Registered User.
Local time
Today, 13:15
Joined
Sep 17, 2001
Messages
939
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!!!!!!

Code:
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

AWF VIP
Local time
Today, 13:15
Joined
Jul 26, 2013
Messages
10,354
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

Registered User.
Local time
Today, 13:15
Joined
Sep 17, 2001
Messages
939
Incidentally This is the SQL i have tried in my query of the form i am opening (EditMaintenanceComponent)

Code:
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

AWF VIP
Local time
Today, 13:15
Joined
May 23, 2011
Messages
4,717
What was the result of the suggestion from Minty of using

Debug.Print strFilter
 

Sam Summers

Registered User.
Local time
Today, 13:15
Joined
Sep 17, 2001
Messages
939
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

Super Moderator
Staff member
Local time
Today, 09:15
Joined
Feb 19, 2002
Messages
42,970
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

..forever waiting... waiting for jellybean!
Local time
Today, 21:15
Joined
May 7, 2009
Messages
19,169
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

Registered User.
Local time
Today, 13:15
Joined
Sep 17, 2001
Messages
939
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 :) )
 

Users who are viewing this thread

Top Bottom