Subforms on top of each other

[maria2008]

Hey,

I'm working on a database which was created by someone else, so for some parts I don't know why they work how they do. As is the case with this subform issue.

There is a form "Search Aggregate" and on it a subform "Search Aggregate - Subform", which is not visible normally. On form "Search Aggregate" there is a dropdownbox "Search Aggregate" where you can choose which aggregate you want to look at, and when you choose it and click button "Search", the information appears on the subform "Search Aggregate - Subform", now visible. The information comes from table "Aggregate".

What I want to do is to have different subforms for different aggregates (for example "Ventilator - subform"), but when I add a subform, it doesn't appear like the subform "Search Aggregate - Subform", but stays visible all the time (and on top of the other subform) and I don't manage to get the information. In the code I put in red what I've changed. I don't quite undorstand what the code does, so my try for the new subform is probably not at all what it should be.. So if anyone has a suggestion what to do, I would be very grateful!

Thanks, Maria

Private Sub Form_Open(Cancel As Integer)

Dim MySQL As String
Dim Tmp As Variant

MySQL = "SELECT * FROM Aggregate WHERE False"

Me![Search Aggregate] = Null

Me![Search Aggregate - Subform].Form.RecordSource = MySQL

Me![Search Aggregate].SetFocus

End Sub

Private Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)

If FieldValue <> "" Then
If ArgCount > 0 Then
MyCriteria = MyCriteria & " And "
End If

MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) & FieldValue & Chr(42) & Chr(39))

ArgCount = ArgCount + 1
End If

End Sub

Private Sub DisableControl()

Dim Tmp As Variant

If Me![Search Aggregate - Subform].Enabled Then
Tmp = EnableControls("Detail", False)
End If

End Sub

Private Sub Search_Click()

Dim MySQL As String, MySQL1 As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
Dim Tmp As Variant

ArgCount = 0

MySQL = "SELECT * FROM Aggregate WHERE "
MyCriteria = ""

AddToWhere [Search Aggregate], "[Art]", MyCriteria, ArgCount

If MyCriteria = "" Then
MyCriteria = "False"
End If

MyRecordSource = MySQL & MyCriteria

If MySQL = "Ventilator" Then
Me![Ventilator - Subform].Form.RecordSource = MyRecordSource

Else
Me![Search Aggregate - subform].Form.RecordSource = MyRecordSource

End If

If Me![Search Aggregate - subform].Form.RecordsetClone.RecordCount = 0 Then
MsgBox "There is no matching Data for these criterias.",48, "No data found"
Me![Search Aggregate] = Null
Me![Search Aggregate].SetFocus
End If

Me![Search Aggregate].SetFocus

End Sub

 

[ajetrumpet]

What I want to do is to have different subforms for different aggregates (for example "Ventilator - subform"), but when I add a subform, it doesn't appear like the subform "Search Aggregate - Subform", but stays visible all the time (and on top of the other subform) and I don't manage to get the information. In the code I put in red what I've changed.

The code in red looks like it just changes the RS of the form. That's all fine and dandy, but you won't see anything in your form controls if you just change the RS. A form has an RS as a source. Controls have CS's as sources. That's why you won't see any data if you just change an RS.

As for stacking subforms on top of one another, I've tried to this, and it's a terrible way to bring up different data based on a selection, because any control edits that you have to make through design view must be done with the object drop down box in the property sheet. If you stack subs on top of one another, it's a nightmare trying to track what controls belong to each subform...

 

[maria2008]

Thanks for the reply. Ok, I won't try to do it like that then, I'll try with separate forms, not subforms.