Goto Record Problem

[Chipcom]

Hi

I need to know how to goto a specific record.
I tried : DoCmd.GoToRecord acDataForm, "MyTable", acGoTo, Forms!
MyTable!ID
While ID is the record number that I need to go to but ID is different
from the record number that access shows me.
ID sturs from 800 and the records numbers that access shows me stars
from 1.
What I need to do to solve this problem?

 

[Guest]

Hi

There are a number of ways to do what you want - this is just one. Create
an unbound text box on your form. Call it txtSearch. Place this
AfterUpdate of txtSearch.


Private Sub txtSearch_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[IDFieldName] = " & Str(Nz(Me![txtSearch], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.SomeOtherField.SetFocus
Me.txtSearch = ""
End Sub


Notes

IDFieldName = the name of control containg the ID data you are looking for.

SomeOtherField = the control on the form you want to set the focus to after
the search.

Me.txtSearch = "" simply clears the text box as (it is unbound) you don't
want it to keep showing the search criteria.

Of course you can improve on this by added some other code in case the ID a
user puts into txtSearch does not exist, etc, etc. But I hope this above
points you in the right direction.

Good luck

 

[Larry Daugherty]

Understand that the number that you see in the record selector box in
the navigation pane has nothing to do with the value stored in the
Primary Key field.

You haven't told us explicitly what you have named that field but I
expect that it is or ends with "ID".

Without explicit details of your table and form designs there is no
knowing what will happen when you execute your code.

HTH

 

[Pierre]

Wayne
I was trying the following code you recommended and ran into a problem.
Once I entered the code in Text256_AfterUpdate I recived the following Run
Time error '13' Type mismatch.

'Here is the modified code I entered.
Private Sub Text256_AfterUpdate()
Dim rs As Object
Set rs - Me.Recordset.Clone
'NOTE: BELOW LINE is hightlighted in yellow when I debug
rs.FindFirst "[Sur Name] = " & Str(Nz(Me![Text256],0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.[Given Name].SetFocus
Me.Text256 = ""
End Sub

' Can you please provide some input as to what I could have done wronge.
--
Work is sometimes hard....but someone has to do it.

Hi

There are a number of ways to do what you want - this is just one. Create
an unbound text box on your form. Call it txtSearch. Place this
AfterUpdate of txtSearch.


Private Sub txtSearch_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[IDFieldName] = " & Str(Nz(Me![txtSearch], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.SomeOtherField.SetFocus
Me.txtSearch = ""
End Sub


Notes

IDFieldName = the name of control containg the ID data you are looking for.

SomeOtherField = the control on the form you want to set the focus to after
the search.

Me.txtSearch = "" simply clears the text box as (it is unbound) you don't
want it to keep showing the search criteria.

Of course you can improve on this by added some other code in case the ID a
user puts into txtSearch does not exist, etc, etc. But I hope this above
points you in the right direction.

Good luck

--
Wayne
Manchester, England.

Hi

I need to know how to goto a specific record.
I tried : DoCmd.GoToRecord acDataForm, "MyTable", acGoTo, Forms!
MyTable!ID
While ID is the record number that I need to go to but ID is different
from the record number that access shows me.
ID sturs from 800 and the records numbers that access shows me stars
from 1.
What I need to do to solve this problem?

 

[Douglas J. Steele]

Use

Dim rs As DAO.Recordset

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Wayne
I was trying the following code you recommended and ran into a problem.
Once I entered the code in Text256_AfterUpdate I recived the following Run
Time error '13' Type mismatch.

'Here is the modified code I entered.
Private Sub Text256_AfterUpdate()
Dim rs As Object
Set rs - Me.Recordset.Clone
'NOTE: BELOW LINE is hightlighted in yellow when I debug
rs.FindFirst "[Sur Name] = " & Str(Nz(Me![Text256],0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.[Given Name].SetFocus
Me.Text256 = ""
End Sub

' Can you please provide some input as to what I could have done wronge.
--
Work is sometimes hard....but someone has to do it.

Hi

There are a number of ways to do what you want - this is just one.
Create
an unbound text box on your form. Call it txtSearch. Place this
AfterUpdate of txtSearch.


Private Sub txtSearch_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[IDFieldName] = " & Str(Nz(Me![txtSearch], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.SomeOtherField.SetFocus
Me.txtSearch = ""
End Sub


Notes

IDFieldName = the name of control containg the ID data you are looking
for.

SomeOtherField = the control on the form you want to set the focus to
after
the search.

Me.txtSearch = "" simply clears the text box as (it is unbound) you
don't
want it to keep showing the search criteria.

Of course you can improve on this by added some other code in case the ID
a
user puts into txtSearch does not exist, etc, etc. But I hope this above
points you in the right direction.

Good luck

--
Wayne
Manchester, England.

Hi

I need to know how to goto a specific record.
I tried : DoCmd.GoToRecord acDataForm, "MyTable", acGoTo, Forms!
MyTable!ID
While ID is the record number that I need to go to but ID is different
from the record number that access shows me.
ID sturs from 800 and the records numbers that access shows me stars
from 1.
What I need to do to solve this problem?

 

[Dirk Goldgar]

Wayne
I was trying the following code you recommended and ran into a problem.
Once I entered the code in Text256_AfterUpdate I recived the following Run
Time error '13' Type mismatch.

'Here is the modified code I entered.
Private Sub Text256_AfterUpdate()
Dim rs As Object
Set rs - Me.Recordset.Clone
'NOTE: BELOW LINE is hightlighted in yellow when I debug
rs.FindFirst "[Sur Name] = " & Str(Nz(Me![Text256],0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.[Given Name].SetFocus
Me.Text256 = ""
End Sub

Is this line really the code you have:

Set rs - Me.Recordset.Clone

?
That should be:

Set rs = Me.Recordset.Clone

Furthermore, unless this code is running in an ADP, the line that tests the
result of the seach should really be:

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

I would have used:

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

rs.FindFirst "[Sur Name] = " & Str(Nz(Me![Text256],0))
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

Note that, if you're running Access 2000 or 2002, you may need to set a
reference to the Microsoft DAO 3.6 Object Library before the declaration of
rs as DAO.Recordset will compile.