A2000: Changing LinkChildFields/LinkMasterFields for subform

[Arvi Laanemets]

Hi

Lately I started to use a design for my projects, where the Main form is an
unbound (multi-page) form. On Main form I have one or several combos, and a
subform with actual data. An example:

The subform sfData has fmData as source. Form fmData is based on table
tblData: ID, ID1, ID2, Field3, ...
ID1 contains values from table tblList1: ID1, List1Name
ID2 contains values from table tblList2: ID2, List2Name

On Main form are unbound combos
cmbList1 with row source 'SELECT ID1, List1Name From tblList1'
cmbList2 with row source 'SELECT ID2, List2Name From tblList2'
For both combos, the BoundColumn=1

Now I set properties for sfData
LinkChieldFields = "ID1;ID2"
LinkMasterFields = "cmbList1;cmbList2"

It's all. On fmData, I hide both ID1 and ID2, and whenever I select some
value for these combos, according set of data is displayed from tblData. And
whenever I add a new record, the current values from combos are inserted
automatically into fields ID1 and ID2. So long it works fine.

Now I want to enchance this design.:
Let us assume the field ID2 in tblList2 is autonumeric, i.e. all existing
values for this field there are >0.
I change the row source for cmbList2 to 'SELECT ID2, List2Name FROM tblList2
UNION SELECT 0, "All" FROM tblList2'
For cmbList2 I generate a Change event, where LinkChieldFields and
LinkMasterFields properties of subform are changed - something like
Forms("fmMain").sfData.LinkChildFields="ID1" & Iif(Me.cbbList2=0,"",";ID2"
Forms("fmMain").sfData.LinkMasterFields="cbbList1" &
Iif(Me.cbbList2=0,"",";cmbList2"

And there will be a problem, because as you try to change the number of
linked fields, access returns an error - something along lines of
non-matching number of linked fields - and opens VBA code for you. But
generally it is working - you may drag second code line above first one,
activate it, and rerun the code, and it works OK until next change of linked
fields.

Has someone here an idea, how to suppress subform/Access reacting until both
properties are changed?


Thanks in advance!

 

[Stefan Hoffmann]

hi Arvi,

Has someone here an idea, how to suppress subform/Access reacting until both
properties are changed?

Public Sub FormSetSubForm(ASubForm As Access.SubForm, _
ASourceObject As String, _
ALinkChildFields As String, _
ALinkMasterFields As String, _
Optional ALinkMasterChild As Boolean = True)

On Local Error GoTo LocalError

If ASubForm.SourceObject <> ASourceObject Then _
ASubForm.SourceObject = ASourceObject

If ALinkMasterChild Then
If ASubForm.LinkChildFields <> ALinkChildFields Then _
ASubForm.LinkChildFields = ALinkChildFields
If ASubForm.LinkMasterFields <> ALinkMasterFields Then _
ASubForm.LinkMasterFields = ALinkMasterFields
Else
If ASubForm.LinkChildFields <> "" Then _
ASubForm.LinkChildFields = ""
If ASubForm.LinkMasterFields <> "" Then _
ASubForm.LinkMasterFields = ""
End If

Exit Sub

LocalError:
' Your favorite error handling code.

End Sub


Just use parameters as seen in the property editor...


mfG
--> stefan <--

 

[Arvi Laanemets]

I'll give it a try. Thanks!


Arvi Laanemets

hi Arvi,



Public Sub FormSetSubForm(ASubForm As Access.SubForm, _
ASourceObject As String, _
ALinkChildFields As String, _
ALinkMasterFields As String, _
Optional ALinkMasterChild As Boolean = True)

On Local Error GoTo LocalError

If ASubForm.SourceObject <> ASourceObject Then _
ASubForm.SourceObject = ASourceObject

If ALinkMasterChild Then
If ASubForm.LinkChildFields <> ALinkChildFields Then _
ASubForm.LinkChildFields = ALinkChildFields
If ASubForm.LinkMasterFields <> ALinkMasterFields Then _
ASubForm.LinkMasterFields = ALinkMasterFields
Else
If ASubForm.LinkChildFields <> "" Then _
ASubForm.LinkChildFields = ""
If ASubForm.LinkMasterFields <> "" Then _
ASubForm.LinkMasterFields = ""
End If

Exit Sub

LocalError:
' Your favorite error handling code.

End Sub


Just use parameters as seen in the property editor...


mfG
--> stefan <--