HELP! a command button for a subform to open another form

[Guest]

Hi, I have two forms 'schools' and 'trainees'. The form 'schools' has a
subform 'placements' into which I can place trainees with schools.

I would like to add a command button for the 'schools' form which will open
a particular record from the 'placements' subform in the 'trainees' form. I'm
sorry if this sounds a little confusing.

Can anyone please tell me how? I can put a command button to open the
'trainees' form but it defaults to record 1

Thank you

 

[Guest]

If you are looking to link 'trainees' to 'placement', you may want to add the
command into your 'placement' subform instead of the 'schools' form, as you
would have to come up with a work-around to identify which record from
'placement' to match to 'trainees' otherwise.

Is your 'placements' subform in Datasheet, Single Form, or Continuous Form
format? If it is in the Continuous Form format (the simplest, I think to
obtain your desired results), simply go into design mode and add a command
button, use the wizard to link it to the 'trainees' form.

 

[Guest]

In some event from the calling form:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "2ndForm"
stLinkCriteria = "[FieldName] =" & Me![FieldName]

DoCmd.OpenForm stDocName, , , , , , Me![FeildName]

Then in the Open or Load Event of 2nd Form:

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

If Not IsNull(Me.OpenArgs) Then
rs.FindFirst "[FeildName]=" & Me.OpenArgs
End If

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Also, open a code module and go to Tools | References and make sure DAO 3.X
listed? IF not, it needs to be.

This works fine from a stand alone form to another stand alone form.

However, I am trying to solve a similar problem with a form with 3 subforms.
I want an event on one subform to "synch" the other two subforms to the
primary key of the calling subform.
If I use this approach it opens another instance of the subform...

I've been playing with setfocus and findrecord but I can't seem to get the
syntax right on the references to the sibling subforms...

owp^3

 

[Guest]

Hi, Thanks for your response. My subform is in datasheet view (I am running
access 2003, I don't seem to have continuous form view) so that I can see all
trainees in a placement in one go. I have seen this done on another dbase,
so I know it's possible.

 

[Guest]

I'm no MVP, so there may be more in-depth information that I'm unaware of,
but my experience is that, unless you would like to either use an unbound
text box in which you enter the unique identifier of the record you want, or
use a combo box or list box, Access will have no way of determining which
record you would like to link from in the subform.

I still think the easiest way is to convert your datasheet to continuous
form - this option is available in Access 2003 (you can do this by going into
the design view, selecting the subform's form properties, in the Format tab,
the second option down in "Default View", select "Continuous Form" from the
drop down options)

Another option, if you would rather use datasheet, is to include a checkbox.
Label the column 'Add Trainees' or something similar, then insert your
openform code behind the checkbox.

As I've said, there may be other ways of doing this, but if so, I do not
know them. I do, however, know that each of these options will obtain your
desired result.