Errors trying to link forms

[Chris Burnette]

Ok, I have a main form with a tab control on it, and on each page of the tab
control is a subform. These subforms are not linked to the main form using
LinkMaster/LinkChild fields, primarily because they are not one-to-many
relationships, but also because I want to be able to use the Find function on
these forms.

So, I have been writing my own code in VBA to link up the forms. I have
already set the relationships between the tables in the Relationships window
in Access, so everything should have the correct FK relationship.

I have two procedures that I have been writing. My first procedure is
intended to set the current record in the subform (the one in the active page
of the tab control) to match the current record in the main form. Although I
don't get any errors when I run this code, it just doesn't seem to work.

Sub LinkRecords()
'This procedure needs to go to the record that is current in the TabForm
Dim MyVar As Variant
Dim MyForm As String

'MyForm needs to be set to the form in the active page of the tab control.
If CurrentProject.AllForms("AuthorSubform").IsLoaded = True Then
MyForm = Forms!TabForm!ctlAuthorSubform.SourceObject
Forms!TabForm!BookID.SetFocus
MyVar = Forms!TabForm!BookID.Text
DoCmd.GoToRecord acDataForm, MyForm, acGoTo, MyVar
Forms!TabForm.SetFocus
End If
End Sub


My second procedure is intended to create a new record in the subform when a
new record is created in the main form, and set the value of the FK in the
subform equal to the value of the PK in the main form. When I try to run
this code, I get the error "there is an invalid method in an expression", and
it takes me to this line:

Forms!TabForm!ctlAuthorSubform.Form.SetFocus

It seems that it doesn't like my syntax, as technically Form is a property,
although (I think) it refers to the form that is designated by SourceObject
property of my subform control.

My code is below:

Sub NewRecordMark()
'This is the procedure that takes place to link up new records when they are
created.
Dim intnewrec As Integer

intnewrec = Me.NewRecord
If intnewrec = True Then
Forms!TabForm!ctlAuthorSubform.Form.SetFocus
DoCmd.GoToRecord acDataForm, "AuthorSubform", acNewRec
Forms!TabForm!AuthorSubform.Form!BookID.SetFocus
If Forms!TabForm!AuthorSubform.Form!BookID.Text = 0 Then
Forms!TabForm!AuthorSubform.Form!BookID =
Forms!TabForm!BookID.Text
End If
End If
End Sub

I know there must be another way to do this, however I'm not really sure
what it is, so if someone can help me get my code right I would greatly
appreciate it.

Thanks,

Chris

 

[George Nicholson]

1)

If CurrentProject.AllForms("AuthorSubform").IsLoaded = True Then

IsLoaded will always return False for a subform because Access doesn't
consider forms being used as subforms to be open forms.

2)

DoCmd.GoToRecord acDataForm, MyForm, acGoTo, MyVar

you are trying to reference a subform by it's name (the string MyForm). You
can't do that. A subform name is pretty much useless (see #1: it isn't
considered to be an open Form, so the name can't do anything for you).
Anytime you find yourself trying to use a subform name (except for filling a
SourceObject property), stop. You can't get there from here.

You can only reference an open subform via the control that is acting as
it's container:

Forms!TabForm!ctlAuthorSubform.Form
Forms!TabForm!ctlAuthorSubform.Form.Controls(1)
etc.

So, I think you'll need to find another approach than DoCmd.GoToRecord
(since that requires a FormName, unless you can find a way for
acActiveDataObject to be appropriate). Maybe a public "Set Filter" routine
on the subform that you can pass MyVar to?

HTH,

 

[Chris Burnette]

Thanks for the info George. From what you're saying, it sounds like it's
almost more of a pain that it's worth to use subforms, so I think I'll just
use separate forms and simplify everything.

That said, what do you mean by a "Set Filter" routine? I checked the VBA
help and I didn't see anything on "Set Filter."

-Chris