Not able to find records using search box (1 Viewer)

Prakashpdl

New member
Local time
Today, 12:29
Joined
Apr 25, 2020
Messages
27
Hello,
I am novice to Acess.
I have many forms linked to single table. SInce dataset fields are many, I created one form and kept rest of the forms as subforms in tabbed pages of the main form. Data in all forms are linked to the same single table. I have thousands of data in the table. I am not able to find record using serach box. When I created searchbox using unbound combo, it finds records in main form only and nothing changes/happens in subforms. Is there any way by which I can find the same record in main form and all the subforms linked to main form? (all data are in single table). Do I have to create relationships between form and subforms ?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 06:44
Joined
Feb 19, 2013
Messages
16,553
you need to reference the subform. You haven't provided your code but assuming you are using filter it would be something like

me.subformname.form.filter="Fieldname = '" & cboSearch & "'"
me.subformname.form.filteron=true

if your subform is related to your mainform, you need to populate the subform linkmaster/child properties with the relevant primary and foreign keys
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:44
Joined
May 7, 2009
Messages
19,169
are you using Macro to find the record on the Main form?
if so you can Add to this macro a RunCode macro:
Code:
RunCode
    Function Name fncFindRecord(Nz([Screen].[ActiveControl],0))
on a Separate Module create the function fncFindRecord:
Code:
Public Function fncFindRecord(ByVal ID As Long)
    Dim varBookMark As Variant
    With [Forms]![MainFormNameHere]
        varBookMark = Null
        With ![subForm1_nameHere].Form
            With .RecordsetClone
                .FindFirst "ID=" & ID
                If Not .NoMatch Then
                    varBookMark = .Bookmark
                End If
            End With
            If Not IsNull(varBookMark) Then
                .Bookmark = varBookMark
            End If
        End With
        varBookMark = Null
        With ![subForm2_nameHere].Form
            With .RecordsetClone
                .FindFirst "ID=" & ID
                If Not .NoMatch Then
                    varBookMark = .Bookmark
                End If
            End With
            If Not IsNull(varBookMark) Then
                .Bookmark = varBookMark
            End If
        End With
        varBookMark = Null
        With ![subForm3_nameHere].Form
            With .RecordsetClone
                .FindFirst "ID=" & ID
                If Not .NoMatch Then
                    varBookMark = .Bookmark
                End If
            End With
            If Not IsNull(varBookMark) Then
                .Bookmark = varBookMark
            End If
        End With
        varBookMark = Null
        With ![subForm4_nameHere].Form
            With .RecordsetClone
                .FindFirst "ID=" & ID
                If Not .NoMatch Then
                    varBookMark = .Bookmark
                End If
            End With
            If Not IsNull(varBookMark) Then
                .Bookmark = varBookMark
            End If
        End With
    End With
End Function
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:44
Joined
Feb 19, 2002
Messages
42,981
I have many forms linked to single table. SInce dataset fields are many, I created one form and kept rest of the forms as subforms in tabbed pages of the main form.
You very likely have not properly normalized your schema. If you have repeating groups,each needs to be a separate table.

It is also poor practice to have multiple open forms linked to the same table open at the same time. Rather than using subforms on the tab pages, simply separate the fields so that the fields in groupA are on the first tab, groupB are on the second tab, etc. That way, all the tab pages are bound to a single instance of the record and all will update in sync.

The best solution is to actually fix the schema issue.
 

Users who are viewing this thread

Top Bottom