Go to Specific Record, FindFirst

  • Thread starter Thread starter Larry R Harrison Jr
  • Start date Start date
L

Larry R Harrison Jr

I have Access 2000. I want code in a function--which is
called up from a custom pull-down menu--to go to a
specified record. The idea is if this form ( which is a
continuous form) is showing search results and the user
selects "View...All" it should show all records and THEN
go to the record they had their cursor on prior to
saying "View All" (so they don't lose place with the found
record in the scheme of where it lands amongst them all).

The code looks like this:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Function show_all()
Dim sSQL As String, stX As Integer
stX = Screen.ActiveForm.autoid
MsgBox "stX = " & stX
sSQL = "SELECT tblMain.* FROM tblMain;"
If Not IsLoaded("frmBrowseMain") Then
Call closeallfrms
DoCmd.OpenForm "frmBrowseMain"
End If
Screen.ActiveForm.RecordSource = sSQL
Screen.ActiveForm.Caption = "Showing All Records. Double-
click your selection to open it (or use the OPEN menu)."
DoCmd.FindRecord "[autoid] = " & stX

End Function

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

It works fine excepts for the DoCmd.FindRecord portion.
It's supposed to match up the record with the proper
autoid. Notice: stX DOES have the proper value (the MsgBox
code helped me confirm this) but it continues to remain at
the 1st record rather than jumping to the one I want.

Tips?

LRH
 
try replacing

DoCmd.FindRecord "[autoid] = " & stX

with this

DoCmd.FindRecord stX, , , , , acAll

if your AutoID number could potentially match the value of
another field (example: a record has AutoID 7, and some
other record has a value of 7 in any other field), then
suggest you try this instead:

Me!autoid.SetFocus
DoCmd.FindRecord stX, , , , , acCurrent

for details, see Microsoft Visual Basic Help
topics "FindRecord Method" and "FindRecord Action".
 
I am trying a similar thing, where my form is based on a
table, and I am using a listbox to select a record, to go
to that specific record.
Ihave tried the same set up, using the value from the
bound column of the listbox to seed the findrecord command.

The result is error 2045, 'Findrecord isnt available now'.

What next?
I feel access is being really awkward when I just want to
perform a simple action.

-----Original Message-----
try replacing

DoCmd.FindRecord "[autoid] = " & stX

with this

DoCmd.FindRecord stX, , , , , acAll

if your AutoID number could potentially match the value of
another field (example: a record has AutoID 7, and some
other record has a value of 7 in any other field), then
suggest you try this instead:

Me!autoid.SetFocus
DoCmd.FindRecord stX, , , , , acCurrent

for details, see Microsoft Visual Basic Help
topics "FindRecord Method" and "FindRecord Action".

-----Original Message-----
I have Access 2000. I want code in a function--which is
called up from a custom pull-down menu--to go to a
specified record. The idea is if this form ( which is a
continuous form) is showing search results and the user
selects "View...All" it should show all records and THEN
go to the record they had their cursor on prior to
saying "View All" (so they don't lose place with the found
record in the scheme of where it lands amongst them all).

The code looks like this:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Function show_all()
Dim sSQL As String, stX As Integer
stX = Screen.ActiveForm.autoid
MsgBox "stX = " & stX
sSQL = "SELECT tblMain.* FROM tblMain;"
If Not IsLoaded("frmBrowseMain") Then
Call closeallfrms
DoCmd.OpenForm "frmBrowseMain"
End If
Screen.ActiveForm.RecordSource = sSQL
Screen.ActiveForm.Caption = "Showing All Records. Double-
click your selection to open it (or use the OPEN menu)."
DoCmd.FindRecord "[autoid] = " & stX

End Function

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

It works fine excepts for the DoCmd.FindRecord portion.
It's supposed to match up the record with the proper
autoid. Notice: stX DOES have the proper value (the MsgBox
code helped me confirm this) but it continues to remain at
the 1st record rather than jumping to the one I want.

Tips?

LRH
.
Click to expand...
.
Click to expand...
 
Back
Top