Goto Specific Record In Subform

[Guest]

Hello,

I have a form/subform (Form A) from which I open another form (Form B).

Based on an action the user takes in Form B, the sort order in subform of
Form A changes when I return, and as I REQUERY the subform, I lose my "place"
in the subform, which is a datasheet.

Any suggestion on how I can set the FOCUS to that specific keyed record when
I return?

Thanks.

 

[Dirk Goldgar]

Hello,

I have a form/subform (Form A) from which I open another form (Form
B).

Based on an action the user takes in Form B, the sort order in
subform of Form A changes when I return, and as I REQUERY the
subform, I lose my "place" in the subform, which is a datasheet.

Any suggestion on how I can set the FOCUS to that specific keyed
record when I return?

I'm assuming there's some code that requeries the subform. In that
section of code, before you call the Requery method, capture the value
of the primary key field of the subform's current record. Save this
value in a variable. Then requery the form, and after the requery, use
the key value and the FindFirst methof of the form's Recordset (or
RecordsetClone) to reposition the form to that record. Code mght look
something like this:

Dim varKey As Variant

With Forms!FormA!SubformControlName.Form
' If this code is running on FormB, use the following instead:
' With Forms!FormA!SubformControlName.Form

varKey = !KeyField

.Requery

If Not IsNull(varKey) Them
.Recordset.FindFirst "KeyField=" & varKey
End If

End With

Note: that assumes that the KeyField is numeric. If it's text, use
this:

.Recordset.FindFirst "KeyField=" & _
Chr(34) & varKey & Chr(34)

 

[Guest]

Dirk,

Thanks for replying. I tried your suggestion but get:

"Object Doesn't Support This Property Or Method"

My code is:

With Forms!frmPatentListingByCriteriaScore.subfrmctl1
.SetFocus
varKey = ![Patent Number]

.Requery
.Recordset.FindFirst "[Patent Number]=" & varKey
End With

As an aside, I am assigning the record source as a QueryDef as follows. The
subform control is dynamic and based on the results of the QueryDef:

With Me.subfrmctl1
.SourceObject = "Query.qryTabListing"
.Requery
End With

Any other ideas?

 

[Dirk Goldgar]

Dirk,

Thanks for replying. I tried your suggestion but get:

"Object Doesn't Support This Property Or Method"

My code is:

With Forms!frmPatentListingByCriteriaScore.subfrmctl1
.SetFocus
varKey = ![Patent Number]

.Requery
.Recordset.FindFirst "[Patent Number]=" & varKey
End With

That doesn't match the example code I posted. Are you calling the
SetFocus method for some particular reason? If not, try the following
code:

With Forms!frmPatentListingByCriteriaScore!subfrmctl1.Form
varKey = ![Patent Number]
.Requery
.Recordset.FindFirst "[Patent Number]=" & varKey
End With

If you really need that Setfocus there, use this code instead:

With Forms!frmPatentListingByCriteriaScore!subfrmctl1
.SetFocus
varKey = .Form![Patent Number]
.Requery
.Form.Recordset.FindFirst "[Patent Number]=" & varKey
End With

As an aside, I am assigning the record source as a QueryDef as
follows. The subform control is dynamic and based on the results of
the QueryDef:

With Me.subfrmctl1
.SourceObject = "Query.qryTabListing"
.Requery
End With

I don't think that's relevant, though I could be mistaken.

 

[Guest]

Kirk,

Thanks for replying. For some reason, and this is why I was using the
SETFOCUS, this code does not bring me back to the desired record in the
subform.

I've tried making varKey both a VARIANT and STRING, to no avail. I've also
tried hard coding the PATENT NUMBER into the code to see if that works, but
no luck.

Here is the code, and some instances of what I have tried:

With Forms!frmPatentListingByCriteriaScore!subfrmctl1.Form
varKey = ![Patent Number]

.Requery
strSearch = "[Patent Number] = " & varKey
' .Recordset.FindFirst [Patent Number] = varKey
' .Recordset.FindFirst "[Patent Number]=" & varKey
' .Recordset.FindFirst [Patent Number] = "'" & varKey & "'"
' .Recordset.FindFirst strSearch
.Recordset.FindFirst [Patent Number] = "EP1558914"

End With

--
David

Dirk,

Thanks for replying. I tried your suggestion but get:

"Object Doesn't Support This Property Or Method"

My code is:

With Forms!frmPatentListingByCriteriaScore.subfrmctl1
.SetFocus
varKey = ![Patent Number]

.Requery
.Recordset.FindFirst "[Patent Number]=" & varKey
End With

That doesn't match the example code I posted. Are you calling the
SetFocus method for some particular reason? If not, try the following
code:

With Forms!frmPatentListingByCriteriaScore!subfrmctl1.Form
varKey = ![Patent Number]
.Requery
.Recordset.FindFirst "[Patent Number]=" & varKey
End With

If you really need that Setfocus there, use this code instead:

With Forms!frmPatentListingByCriteriaScore!subfrmctl1
.SetFocus
varKey = .Form![Patent Number]
.Requery
.Form.Recordset.FindFirst "[Patent Number]=" & varKey
End With

As an aside, I am assigning the record source as a QueryDef as
follows. The subform control is dynamic and based on the results of
the QueryDef:

With Me.subfrmctl1
.SourceObject = "Query.qryTabListing"
.Requery
End With

I don't think that's relevant, though I could be mistaken.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

....

additionally, I get an error on the code line: .Form.Recordset.FindFirst
"[Patent Number]=" & varKey

The Microsoft Jet Database Engine does not recognize X as a valid field name
or expression (where X is the value of the Key Field)

--
David

Dirk,

Thanks for replying. I tried your suggestion but get:

"Object Doesn't Support This Property Or Method"

My code is:

With Forms!frmPatentListingByCriteriaScore.subfrmctl1
.SetFocus
varKey = ![Patent Number]

.Requery
.Recordset.FindFirst "[Patent Number]=" & varKey
End With

That doesn't match the example code I posted. Are you calling the
SetFocus method for some particular reason? If not, try the following
code:

With Forms!frmPatentListingByCriteriaScore!subfrmctl1.Form
varKey = ![Patent Number]
.Requery
.Recordset.FindFirst "[Patent Number]=" & varKey
End With

If you really need that Setfocus there, use this code instead:

With Forms!frmPatentListingByCriteriaScore!subfrmctl1
.SetFocus
varKey = .Form![Patent Number]
.Requery
.Form.Recordset.FindFirst "[Patent Number]=" & varKey
End With

As an aside, I am assigning the record source as a QueryDef as
follows. The subform control is dynamic and based on the results of
the QueryDef:

With Me.subfrmctl1
.SourceObject = "Query.qryTabListing"
.Requery
End With

I don't think that's relevant, though I could be mistaken.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

.... an apostrophe here or there did the trick...

With Forms!frmPatentListingByCriteriaScore!subfrmctl1
.SetFocus
varKey = .Form![Patent Number]

.Requery
.Form.Recordset.FindFirst "[Patent Number]= " & "'" & varKey & "'"

End With

Thanks for your help.

--
David

Dirk,

Thanks for replying. I tried your suggestion but get:

"Object Doesn't Support This Property Or Method"

My code is:

With Forms!frmPatentListingByCriteriaScore.subfrmctl1
.SetFocus
varKey = ![Patent Number]

.Requery
.Recordset.FindFirst "[Patent Number]=" & varKey
End With

That doesn't match the example code I posted. Are you calling the
SetFocus method for some particular reason? If not, try the following
code:

With Forms!frmPatentListingByCriteriaScore!subfrmctl1.Form
varKey = ![Patent Number]
.Requery
.Recordset.FindFirst "[Patent Number]=" & varKey
End With

If you really need that Setfocus there, use this code instead:

With Forms!frmPatentListingByCriteriaScore!subfrmctl1
.SetFocus
varKey = .Form![Patent Number]
.Requery
.Form.Recordset.FindFirst "[Patent Number]=" & varKey
End With

As an aside, I am assigning the record source as a QueryDef as
follows. The subform control is dynamic and based on the results of
the QueryDef:

With Me.subfrmctl1
.SourceObject = "Query.qryTabListing"
.Requery
End With

I don't think that's relevant, though I could be mistaken.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

... an apostrophe here or there did the trick...

With Forms!frmPatentListingByCriteriaScore!subfrmctl1
.SetFocus
varKey = .Form![Patent Number]

.Requery
.Form.Recordset.FindFirst "[Patent Number]= " & "'" &
varKey & "'"

End With

Thanks for your help.

Great! Remember this note from my first reply?

Note: that assumes that the KeyField is numeric. If it's text, use
this:

.Recordset.FindFirst "KeyField=" & _
Chr(34) & varKey & Chr(34)

Aside from the need to add the .Form qualifier, because of the way
you've set up the With block, that takes care of the necesary quoting.