Reference Form when using Navigation Form Access

[pritesharyan]

I have navigation form inside which i have Order By Customer form.


i am trying to find order of Customer by OrderID for that i have using flowing code

Private Sub cmdSearch_Click()

    Dim search_customerID As Long
    search_customerID = DLookup("CustomerID", "Order", "OrderID =" & Me.txtSearch) 
    DoCmd.SearchForRecord acDataForm, "Order by Customer", acFirst, "CustomerID=" & search_customerID

End Sub

now i am geting error as shows in image.

NOTE: When i am open "Order by Customer" form directly
without inside navigation Form it works properly.
but i am getting above error(shows in image) only when i open "Order by Customer" form using navigation form.


Please guide me.

[NTC]

inside a Navigation form, the Order by Customer form is a sub form; so this syntax is not valid:
DoCmd.SearchForRecord acDataForm, "Order by Customer",

because one must first reference the main form...... sorry I don't know the syntax for it as it is not a method I use plus I don't use Navigation forms...

in general the approach I would take is for the Order by Customer form to be a standard inset sub form - whereby when making it the wizard will prompt & suggest for you to set up a linking key field between main form and sub form....then when you change customers in the main form - it triggers a requery of the sub form - and it would display only records of that customer. I would say this is a more classic design.

the Navigation form, invented by Microsoft, is suppose to have an advantage in that it doesn't call data in a sub form located in a tab - until that tab button is pressed. And that can be helpful when one has many tabs and the sub forms contain lots of records.....but when that is not the case it really isn't the type of form to use - in my opinion alone.....

[pritesharyan]

Link For Solution

[pritesharyan]

Downlaod Project With Solution

PrivateSub cmdSearch_Click()

    Dim MainFK AsLong
    Dim rs As DAO.Recordset
    Dim WhereStr AsString

    MainFK = DLookup("MainformID","Subform","SubformID ="&Me.txtSearch)

    WhereStr ="MainformID="& MainFK

    WithMe.Form

        Set rs =.RecordsetClone
        rs.FindFirst WhereStr

        If _
            rs.NoMatch _
        Then

            MsgBox "Subform record not match to mainform record"

        Else

            .Bookmark = rs.Bookmark

        EndIf

    EndWith

EndSub