re: Syntax error - I suspect that the MsgBox line "wrapped" when it
shouldn't. The line starts with MsgBox and ends with "Error" - all on one
line. That could be what's giving you the syntax error.
re: Which records - the previous response only guarantees that a new record
in frmLevel2Goals will have the foreign key value that matches the PK of
frmMainGoals.
To make frmLevel2Goals displayed records stay in synch with the current
position of frmMainGoals when you move to a different record you need to
have the main form cause a requery of the "sub' form (although it isn't
really a subform in Access terminology). You do it via the On Current event,
something like this. ( I've had to use pseudo code as I don't have your
table and column names handy.):
in frmMainGoals
Private Sub Form_Current()
Dim frm_Level2GoalsHandle As Form
' check to see if the Level2Goals form is loaded as per other example
If CurrentProject .AllForms("frmLevel2Goals").IsLoaded Then
Dim SQL As String
SQL = "SELECT * FROM tblLevel2GoalsSourceTable WHERE (linkToMainFK = " &
Me.PKfield & ")"
Set frm_Level2GoalsHandle = Forms![frmLevel2Goals].Form
frm_Level2GoalsHandle.Recordsource = SQL
frm_Level2GoalsHaneld.Requery
Set frm_Level2GoalsHandle = Nothing
End If
re: Button on MAIN form to go to new record
Private Sub btn_Level2GoalsNewRecord_Click()
Dim frm_Level2GoalsHandle As Form
If CurrentProject .AllForms("frmLevel2Goals").IsLoaded Then
Set frm_Level2GoalsHandle = Forms![frmLevel2Goas].Form
frm_Lvel2GoalsHandle.Filter = "([PK] = -1 )"
' the above is impossible, so you will only get the new record
frm_Level2GoalsHandle.FilterOn = True
set frm_Level2GoalsHandle = Nothing
End If
End Sub
Private Sub btn_Level2GoalsAllRecords_Click()
Dim frm_Level2GoalsHandle As Form
If CurrentProject .AllForms("frmLevel2Goals").IsLoaded Then
Set frm_Level2GoalsHandle = Forms![frmLevel2Goas].Form
frm_Level2GoalsHandle.FilterOn = False
End If
End Sub
I'm getting a syntax error on the Private Sum Form_Open (Cancel as
Integer) line when I try to open the form. And will this make it so
that one button on the main form (frmMainGoals) opens frmLevel2Goals
to a new record while another button opens it to the beginning record?
Thanks.