position to record. 1

[GShen]

Hi,
running Access 2000. Sql 2000 backend. Trying to position to a record on the form I am displaying. It is a detail item list. Having the user keyin to an unbound field and want to position to that record. I tried creating a recordset and performing a loop with movenext until the record was found. Awfully slow. I then found some code for Docmd.findrecord but was getting errors saying Access version not up to date (which doesn't make any sense). I also tried creating an index for a recordset and then doing a recordset seek on it but was also getting syntax errors.
Any suggestions?????
Thanks, I am burning out right now.

 

[jfischer]

Here's a sample routine that you can adapt. It uses a Customer ID search to position the form (Me.) to that customer's record.

Private Sub Cust_Reposition(sCustID As String)
  Dim rs As ADODB.Recordset
    
  Set rs = Me.RecordsetClone
  'May need to wait for the recordset to be populated
  Do While (rs.State And adStateConnecting) Or _
           (rs.State And adStateFetching)
    DoEvents
  Loop
  rs.Find "CustID = '" & sCustID & "'"
  If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  Set rs = Nothing
End Sub

 

[GShen]

Thanks,
I will give it a try tomorrow.
2 questions.
1)I have used recordsets before what is recordsetclone? Does that allow me to use the rs.find code?
2)What is the rs.state, adstatefetching and adstateconnection relate to?

sorry that was 3 questions. 1a, 1b and 2.
thanks for your help.

 

[jfischer]

1 a) The RecordsetClone is a mirror image of the recordset that is bound to your form. Every form bound to a recordset has a RecordsetClone as well.

1 b) Yes, you can use the .Find method with the RecordsetClone.

2) When you attach recordsets to ADO connections the process passes through several states. In some cases a program may try to use the recordset data before it's ready. The .State property of an ADO recordset gives you a progress report on the building process of your data (cursor). adStateConnection indicates that the ADO is connecting to the data. adStateFetching indicates that the recordset is being filled with the records you requested. Just to be safe, I check the .State proprty to make sure I don't try to use the data too early in the process, which may cause errors.

 

[GShen]

To Jfischer,
THANKS! With a little tweaking I got it to work. I do have a 1 other question.

I never used the DoEvents before, after reading up, I am still a little confused. What does it do in the context of the code you sent me? I ran it with and without the code and it worked the same.

I sent you a copy of my code. me.txtSearchItem is an unbound field on the form. tblpod_item is the match I am looking for. Once I find the record, I want to set the focus to the Qty field (eg. Me.tblpod_OrderTotalQTY) for the matching item.

Private Sub txtSearchItem_AfterUpdate()

Dim rst As ADODB.Recordset

Set rst = Me.RecordsetClone

'May need to wait for the recordset to be populated
Do While (rst.State And adStateConnecting) Or _
(rst.State And adStateFetching)
DoEvents
Loop

rst.Find "tblpod_item = '" & Me.txtSearchItem & "'"

If rst.EOF Then
MsgBox ("Item is not on this P/O, try again&quot, vbOKOnly
Else
DoCmd.GoToRecord acForm, "frmPODetail", acGoTo, rst.Bookmark
Me.tblpod_OrderTotalQTY.SetFocus
End If

Set rst = Nothing

End Sub

THANKS AGAIN. A STAR for YOU!!