Link to home
Start Free TrialLog in
Avatar of JARichard
JARichardFlag for United States of America

asked on

Link main form to subform

I have a mainform (BBMain) with a datasheet subform (BBMain_Subform) on it.  The main form is used to enter data for a single record, and the subform (datasheet) displays all the records in the table (Tickets).  I'm trying to make it so that when a user clicks on one of the records in the subform, the appropriate record information is automatically displayed on the mainform so that a user can add / edit information for that record.
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Have not used Split Forms in 2010, but that is precisely the purpose of that functionality.
In Properties sheet for Subform control,
Set  Parent Link Fields: to main form record source Key
        Child Link Fields: subform record source foreign key

Example: main for a bound to table a(aid, x)
                sub form b bound to table b (bid, aid, x) dragged to detail section of form a, creates a control source b

Properties sheet for Subform/Subreport b:
Parent Link Fields: aid
Child Link Fields: aid
Avatar of JARichard



Both the Main form and the Subform use the same table.  Other than that, I didn't really understand what you meant by the rest.

Fyed:  Split forms seem to have limited functionality as far as what I can do / edit as far as code, aesthetics, ect, this is why I created with a subform.
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
"Main form and subform use same table"

Subform Form_Current Event:

Private Sub Form_Current()
    Me.Parent.Filter = "aid='" & Me.aID & "'"
    Me.Parent.FilterOn = True
End Sub

replace "aid" with your table primary key.

This is what I entered into the on current event:

Private Sub Form_Current()
    Me.Parent.Filter = "Remedy Ticket Number='" & Me.Remedy_Ticket_Number & "'"
    Me.Parent.FilterOn = True
End Sub

but what I get is a " (Syntax error) missing operator..." error
on which line?

Did you try my recommendation for using the bookmark property of the form?

Is the Remedy_Ticket_Number field numeric or a string. If numeric try it without the quotes around the value.

Also, is the field name [Remedy Ticket Number] without underscores, or does it contain underscores between words?

Lastly.  Did you put the code in the subforms Current event, or the Parent forms Current event?  It should be in the subforms.

I changed the names of my fields just to make them one word so they'd be simpler and entered the code as such in the oncurrent event of the subform (BBMain_Subform):

Private Sub Form_Current()

    Dim rs As DAO.Recordset

    Set rs = Me.Parent.RecordsetClone

    rs.FindFirst "[Remedy] = " & Me.Remedy
    Me.Parent.Bookmark = rs.Bookmark

    Set rs = Nothing

End Sub

when I open the main form I get the error "Method or data member not found", and the first line is highlighted.
That is because the sub form loads before the main form.  Try adding the following as the first line of code in the sob forms Current event.

If me.parent.visible = false then exit sub
I actually prefer to set the sourceobject property of the main form to nothing in the forms design view.  Then, I use the main forms load event to set that property to the name of the subsurface.  This ensures that the main form recodrsource is set when the sub form is loaded.

The main form displays only 1 record.

Selecting a record in subform, filters the main form with that relevant record.
You do not need the recordset clone.

Upload the current current sample database for further analysis.
Does exactly as I needed, I just had to change how my subform was created to get it to work as it should.