Linking one subform with another (1 Viewer)

[jasonp21]

Hi

I have a form with a calandar control and two subforms. The calandar control links to the first subform, containing events on that day. The second subform is for equipment required / reserved for that event, and therefore changes depending on the date and event.

While I have managed to get the first subform operating correctly, I cant seem to get the equipment subform to stay 'attached' to the specific event record.

Any suggestions?

 

[vbaInet]

The second subform is linked to what?

 

[jasonp21]

The second subform should link to the first subform and the calandar (i'm guessing) I've tried using the master and child fields, setting the master as the event ID and the child as the calandar control, but that doesn't work.

The equipment reserved should change with each event booking record, but it just stays the same for any date or event selected.

 

[vbaInet]

You won't be able to use the Link Master/Child Fields property of subform2 because you're needing to do it based on two fields. Well you can if you concatenate the Date and EventID fields together and link them up via that but you will lose the benefit of indexing. If you're happy to go ahead with this, how many records will the tables on subform1 and subform2 typically have?

The other way is to do it in code by changing the Filter property of subform2 in the Current event of subform1 and the Current event of the Calendar control.

 

[jasonp21]

How do I changethe Filter property of subform2 in the Current event of subform1 and the Current event of the Calendar control?

 

[vbaInet]

For example (aircode):

Dim strFilter As String

With [COLOR=Red]Me.Parent[/COLOR]
    ' Validate and build
    
    If IsDate(.[COLOR=Blue]CalendarControl[/COLOR]) Then
        strFilter = "[[COLOR=Blue]DateField[/COLOR]] = " & Format(.[COLOR=Blue]CalendarControl[/COLOR], "\#mm\/dd\/yyyy\#")
    End If
    
    If Len(.[COLOR=Blue]EventID[/COLOR] & vbNullString) <> 0 Then
        If Len(strFilter) <> 0 Then
            strFilter = strFilter & " [[COLOR=Blue]EventID[/COLOR]] = " & .[COLOR=Blue]EventID[/COLOR]
        Else
            strFilter = "[[COLOR=Blue]EventID[/COLOR]] = " & .[COLOR=Blue]EventID[/COLOR]
        End If
    End If
End With

' Set the filter
If Len(strFilter) <> 0 Then
    Me.Filter = strFilter
    Me.FilterOn = True
End If

Me.Parent refers to the main form so I'm guessing that the calendar control is located on the main form. Copy the code and amend only the blue bits.