Bookmarking a record in a subform.

[Dave]

Hi All,

This has probably been answered many times before but my searches draw blanks.

My main form has subforms, but subforms don't seem to filter too well, so...

I have a button on my main form (form1) which opens a new form (form2) which has a view of 'all' the db records - this enables me to perform complex filters.

My problem is this...

Can I bookmark a record in (form2) and return to it in (form1) after I have closed (form2)?

I've got a global var. for the boomark and I was hoping to set it to the row on (form2) before I closed the form, but I don't know how to set it to the row within the subform tha tthe user is
currently pointing to.

Any help would be very much appreciated.

Thank you,
Dave

 

[Klatuu]

Rather than use a global variable, add a hidden control to Form1 to hold the
key value of the record chosen in form 2.

Then in the Unload event of form2, populate the control on form1.
When you close form2, form1's Activate event will fire. You can use that to
position to the record in the subform.

So in the Unload event of form2:

Forms!Form1!txtRecID = Me.SomeControl (or where ever the key field is)

Then in the Activate Event of Form1:

If Not IsNull(Me.txtRecID) Then
With Me.SubFormControlName.Form.RecordsetClone
.FindFirst "[Key Field Name] = " & Me.txtRecID
If .NoMatch Then
MsgBox "Record Not Found"
Else
Me.SubFormControlName.Form.Bookmark = .Bookmark
End If
End With
End If

All the names are made up, so you will need to use your real names.

Also note that subFormControlName is the name of the subform control on your
main form. It is not necessarily the name of the form being used as a
subform. The form being used as the subform is identified in the subform
control's source object property. The syntax for this is important or it
will not work.

 

[Dave]

Many thanks for the reply,

I still have doubts about getting the data from the selected subform record, it's where you stated...

Forms!Form1!txtRecID = Me.SomeControl (or where ever the key field is)

What syntax do I use when replacing the Me.SomeControl in the above?

Sorry to be a pain, but I have often wondered how to do this.

Thanks again,
Dave

Rather than use a global variable, add a hidden control to Form1 to hold the
key value of the record chosen in form 2.

Then in the Unload event of form2, populate the control on form1.
When you close form2, form1's Activate event will fire. You can use that to
position to the record in the subform.

So in the Unload event of form2:

Forms!Form1!txtRecID = Me.SomeControl (or where ever the key field is)

Then in the Activate Event of Form1:

If Not IsNull(Me.txtRecID) Then
With Me.SubFormControlName.Form.RecordsetClone
.FindFirst "[Key Field Name] = " & Me.txtRecID
If .NoMatch Then
MsgBox "Record Not Found"
Else
Me.SubFormControlName.Form.Bookmark = .Bookmark
End If
End With
End If

All the names are made up, so you will need to use your real names.

Also note that subFormControlName is the name of the subform control on your
main form. It is not necessarily the name of the form being used as a
subform. The form being used as the subform is identified in the subform
control's source object property. The syntax for this is important or it
will not work.

 

[Dave]

Hi,

I noticed from other postings that I forgot to mention something which may be important, namely...

All my subforms are datasheet views.

Regards,
Dave

 

[Klatuu]

Me.SomeControl is just a made name. Since I don't know your form control
names, I have to make them up for example purposes. This would be code in
the Unload event of Form2.

The Idea is to populate a hidden control on form 1 with a value from a
primary key field that will find a record in the subform of form 1.

It doesn't matter that your subform is a datasheet.
--
Dave Hargis, Microsoft Access MVP

Many thanks for the reply,

I still have doubts about getting the data from the selected subform record, it's where you stated...

Forms!Form1!txtRecID = Me.SomeControl (or where ever the key field is)

What syntax do I use when replacing the Me.SomeControl in the above?

Sorry to be a pain, but I have often wondered how to do this.

Thanks again,
Dave

Rather than use a global variable, add a hidden control to Form1 to hold the
key value of the record chosen in form 2.

Then in the Unload event of form2, populate the control on form1.
When you close form2, form1's Activate event will fire. You can use that to
position to the record in the subform.

So in the Unload event of form2:

Forms!Form1!txtRecID = Me.SomeControl (or where ever the key field is)

Then in the Activate Event of Form1:

If Not IsNull(Me.txtRecID) Then
With Me.SubFormControlName.Form.RecordsetClone
.FindFirst "[Key Field Name] = " & Me.txtRecID
If .NoMatch Then
MsgBox "Record Not Found"
Else
Me.SubFormControlName.Form.Bookmark = .Bookmark
End If
End With
End If

All the names are made up, so you will need to use your real names.

Also note that subFormControlName is the name of the subform control on your
main form. It is not necessarily the name of the form being used as a
subform. The form being used as the subform is identified in the subform
control's source object property. The syntax for this is important or it
will not work.