ADO Find

Question

Post reply

ADO Find

mbarr350

SSC Enthusiast

Points: 198
More actions
March 24, 2004 at 10:42 am

#66351

I get error message when I run this code. I am trying to stay on the current record after a me.requery. (ie don;t return to the first record)
Private Sub cboInvNo_AfterUpdate()
Dim ID As Integer
Me.Requery
ID = Me.OrderID
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.Find " Me.OrderID = " & ID
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 1

What is the error ur getting

My Blog: http://dineshasanka.spaces.live.com/

mbarr350 SSC Enthusiast Points: 198 More actions · Answer 2

With this code.

Private Sub cboInvNo_AfterUpdate()

Dim ID As Integer

Dim rs As Object

Set rs = New ADODB.Recordset

Set rs = Me.Recordset.Clone

Me.Requery

ID = Me.OrderID

rs.Find "[OrderID] = " & ID

If Not rs.EOF Then

Me.Bookmark = rs.Bookmark

End If

End Sub

I get this error as I step thru the code.

Run Time Err 2001

You cancelled the previous operation

on Me.Bookmark = rs.Bookmark

smknox Hall of Fame Points: 3275 More actions · Answer 3

I don't think you need to deal with ADO if you are just trying to return to the pertinent record. Try this syntax:

Private Sub Form_AfterUpdate()

Dim iBook As Variant

iBook = Me.Bookmark

Me.Requery

Me.Bookmark = iBook

End Sub

Explanation: the bookmark variable must be of "variant" type

Save the bookmark. Requery. Return to the bookmarked record.

HTH,

SMK

Peter Evans Ten Centuries Points: 1187 More actions · Answer 4

Are you sure your RS or COMMAND is not in adAsyncExecute mode?

You symptom sounds like your Find call is resulting in cancelling the .requery call. See relevant snippet from MSDN below.

Note If Options is set to adAsyncExecute, this operation will execute asynchronously and a RecordsetChangeComplete event will be issued when it concludes.

The ExecuteOpenEnum values of adExecuteNoRecords or adExecuteStream should not be used with Requery.

Peter Evans (__PETER Peter_)

mbarr350 SSC Enthusiast Points: 198 More actions · Answer 5

The problem with the code is that the"OrderID" which is a PK and an auto generated no. doesn't

update before I run the Requery. So the initial value of the OrderID is "99" BOF (the first OrderID was 100).

Is there a way to update the OrderID before the Requery is run or some other solution?

PS. The same exact code works on my PO recieving report because it is working with existing OrderID no.

Here is the code;

Private Sub cboInvNo_AfterUpdate()

Dim ID As Integer

Dim rs As Object

Set rs = New ADODB.Recordset

Set rs = Me.RecordsetClone

ID = Me.OrderID

Me.Requery

rs.Find "[OrderID] = " & ID

If Not rs.EOF Then

Me.Bookmark = rs.Bookmark

End If

End Sub

Thanks

smknox Hall of Fame Points: 3275 More actions · Answer 6

mbarr: I feel your pain because I have also been fighting this issue for a matter of months. What I have found out through MSDN and other sources is that SQL WILL NOT return the new OrderID value to Access. After trying everything imaginable to trick it into giving me that number back, I have found a way: You have to access SQL through ADO, run a stored procedure to return the value to ADO which then gives it to Access. Here is my code which returns a new PhoneID from a SQL stored procedure (as written by an Access guru, not myself as I am a newbie also)

Public Function InsertPhoneRecord() As Long

Dim cmdNew As ADODB.Command

Dim cnn As ADODB.Connection

Dim strCnn As String

strCnn = "Provider=sqloledb;Data Source=Knox-Server;" & _

"Initial catalog=DevBData;User Id=jk;Password=something;"

Set cnn = New ADODB.Connection

cnn.Open strCnn

Set cmdNew = New ADODB.Command

With cmdNew

Set .ActiveConnection = cnn

.CommandText = "procPhoneInsert"

.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter("@NewPhoneID", adInteger, adParamOutput)

.Execute

InsertPhoneRecord = .Parameters("@NewPhoneID").Value

End With

cnn.Close

Set cnn = Nothing

End Function

The CommandText is the name of the stored procedure.

You then have a line in your code that calls the function like:

Me.PhoneID=InsertPhoneRecord()

Here is the stored procedure:

CREATE PROCEDURE dbo.procPhoneInsert

@NewPhoneID int OUTPUT

AS

INSERT INTO phone (EmpUpdated) VALUES ('9')

SET @NewPhoneID=SCOPE_IDENTITY()

GO

The INSERT INTO line is just adding a value to the table in order to generate and new record. You could insert anything applicable to your app.

Granted this is a lot of work, but it works. Let me know if you follow my code.

Thanks,

SMK

kaeg Old Hand Points: 378 More actions · Answer 7

You can save the current record to force it to get the auto id.

Just use the code below and see if it works the way you want.

DoCmd.RunCommand acCmdSaveRecord