Syncronizing forms / linked forms

[SharynInCambodia]

I have been trying to create a series of forms that open up from a main form
(related like a subform but opening as a new form instead). Using the
IsLoaded code, i have managed to allow forms to be completed that
automatically enter in the foreign key / primary key linking the two forms.

I also would like to be able to filter records in the secondary form when
opened from the main form (based on the main form ID) so that i am only
viewing the related records. I have tried suggested code but it is not
working. This is what i have been trying in the main form (as suggested)

Private Sub Form_Current()

' Declare and set a variable to store the WHERE
' clause that describes the records you want to
' display.
Dim strCond As String
strCond = "MainTableID = Forms![Phase3-VProbAnalysis2]!ID"

' Use the IsLoaded function from the Northwind
' sample database to check whether the Products
' form is open, then set the properties.
If IsLoaded("Gen-InterestGroups") Then
Forms![Gen-InterestGroups].FilterOn = True
Forms![Gen-InterestGroups].Filter = strCond
End If

End Sub

The IsLoaded code has been defined in the module section of the DB as copied
from Northwind and that works for the other usage (automatically entering the
foreign key into the records on the secondary form).

Any ideas why this isn't working? Thanks

 

[Tom Lake]

strCond = "MainTableID = Forms![Phase3-VProbAnalysis2]!ID"

Try this:

strCond = "MainTableID = '" & Forms![Phase3-VProbAnalysis2]!ID & "'"

The way you have it written, the MainTableID would have to be
equal to the string "Forms![Phase3-VProbAnalysis2]!ID"
NOT the value contained in the field.

Tom Lake

 

[SharynInCambodia]

Thanks for answering Tom, but it still didn't work! The syntax i had was
copied direct from a couple of sites but for some reason isn't working! i'll
keep trying.

strCond = "MainTableID = Forms![Phase3-VProbAnalysis2]!ID"

Try this:

strCond = "MainTableID = '" & Forms![Phase3-VProbAnalysis2]!ID & "'"

The way you have it written, the MainTableID would have to be
equal to the string "Forms![Phase3-VProbAnalysis2]!ID"
NOT the value contained in the field.

Tom Lake

 

[SharynInCambodia]

This seems to have answered my question - copied from another question from
somebody (in the new users section from about 6 months ago).
Thanks Ken Sheridan!


To use separate 'linked' forms rather than a subform, put code in each
button's Click event procedure like this:

DoCmd.OpenForm "YourOtherForm", "MyID = " & MyID

where YourOtherForm is the name of the form to be opened and MyID is the
name of the primary key of the current form's underlying recordset, and also
of the corresponding foreign key in the other form's underlying recordset.
I've assumed that MyID is a number data type. If its text use:

DoCmd.OpenForm "YourOtherForm", "MyID = """ & MyID & """"

Also in the current form's Current event procedure put:

On Error Resume Next
Forms! YourOtherForm.Filter = "MyID = " & Nz(MyID,0)
'''repeat for other linked forms'''

This assures that if a linked form has been open and you set focus back to
the current form, the other form will be kept in sync with the current form
if you navigate to other records. The Nz function is used here for when you
move the current form to an empty new record. Again, if MyID is a text data
type amend it to:

On Error Resume Next
Forms! YourOtherForm.Filter = "MyID = """ & Nz(MyID,"") & """"
'''repeat for other linked forms'''

Ken Sheridan
Stafford, England

Opps! Left out the argument name:

DoCmd.OpenForm "YourOtherForm", WhereCondition:="MyID = " & MyID

or:

DoCmd.OpenForm "YourOtherForm", WhereCondition:="MyID = """ & MyID & """"

Ken Sheridan
Stafford, England

I have been trying to create a series of forms that open up from a main form
(related like a subform but opening as a new form instead). Using the
IsLoaded code, i have managed to allow forms to be completed that
automatically enter in the foreign key / primary key linking the two forms.

I also would like to be able to filter records in the secondary form when
opened from the main form (based on the main form ID) so that i am only
viewing the related records. I have tried suggested code but it is not
working. This is what i have been trying in the main form (as suggested)

Private Sub Form_Current()

' Declare and set a variable to store the WHERE
' clause that describes the records you want to
' display.
Dim strCond As String
strCond = "MainTableID = Forms![Phase3-VProbAnalysis2]!ID"

' Use the IsLoaded function from the Northwind
' sample database to check whether the Products
' form is open, then set the properties.
If IsLoaded("Gen-InterestGroups") Then
Forms![Gen-InterestGroups].FilterOn = True
Forms![Gen-InterestGroups].Filter = strCond
End If

End Sub

The IsLoaded code has been defined in the module section of the DB as copied
from Northwind and that works for the other usage (automatically entering the
foreign key into the records on the secondary form).

Any ideas why this isn't working? Thanks