sub forms

[jlitven]

I have a main form called Clients with two sub-forms. Each sub form displays the Client’s Products. One is a single form view, the other is a datasheet view. Both of the sub-form’s information comes from a product table. I want to be able to click on a certain product in the data sheet sub-form and have the single sub-form correspond to that product. The closest I have gotten is more of a work around that works like this.

In the data sheet sub-form Current()

Me.Parent.Form.txtHiddenID = Me.ref

This tells the main form what reference the data sheet is currently on. The single form then has

Link Childs fields: ref

Link Master fields: txtHiddenID

However, not all products have references and there must be a better way to do it. They are both referencing the same records so logically it’s not difficult.

 

[MartijnR]

When I want to make a subform I just use the easiest way I know.

1. Make a query which has the outcome you desire
2. Open the mainform in design view
3. Add a subform using the toolbox with wizard on
4. Select the query and answer the next few questions (you can adjust the parent and child ID later via properties of the subform, if unsuccessfull)
5. Adjust layout of subform
6. Done

 

[jlitven]

But the problem is I want the two subforms to interact with eachother.

 

[MartijnR]

Is it always the same subform you use, to get the other subform to display the records?

If so, you can use the query of the 'display' subform to get the records you want. Use the criteria field in the query and 'build' it to point to the related field. You can try switching between design and datasheet view to see your results.

If I understand the first post, you are trying to make something which allows you to click a certain product while somewhere else you see the details? In that case, why not use a listbox?

Sorry, was a bit sleepy when I posted before

 

[jlitven]

The listbox would work, but the problem is that the listbox displays all the products. I only want the ones that particular client has.

 

[jlitven]

I got the listbox to show only the certain products, but now clicking on each won't change the display form anymore (the form the listbox is in).

 

[MartijnR]

Does your listbox still have an after_update event set to vba-code?

I have have copied this from a form where List24 is the name of my listbox.

Private Sub List24_AfterUpdate()
On Error GoTo Error

' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![List24], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Error:
If Err.Number <> "0" Then
MsgBox Err.Number & " " & Err.Description
End If
Exit Sub

End Sub

Btw, I get a lot of this, when display isn't updated and I always check via the menu bar, Records if updating works via refresh or remove filter sort so I can add ' Me.Refresh ' as a line into the code.

' Docmd.Runcommand accmdremovefiltersort ' is like the 2nd option, but would reset your recordpointer to the beginning. Storing the ID in a variable before doing docmd.R-etc and afterwards using the variable to set the recordpointer is a way around that.

Then again, I could be going totally the wrong way here, because I'm starting to get more and more confused with how many form's etc. you have