FindNext does not go to the correct bookmark

[AshikHusein]

I am trying to use the InputBox method to retreive a particular record. The code activates on the event when the form opens. The problem is that the form does not display the correct record. Can any one figure out the problem with the code? Thanks.

Private Sub Form_Open(Cancel As Integer)

Dim rec As DAO.Recordset

Dim Count As Integer
Dim AccountNumber As String


Set rec = Me.RecordsetClone



AccountNumber = InputBox("Enter the account number to search in the format 999-9999")
rec.FindFirst "[Account Number] = " & AccountNumber
Me.Bookmark = rec.Bookmark

Exit Sub

"Account Number" is a field in the table bound to the form.

 

[Mile-O]

Use the Form_Load event as, using the Open event, your form's recordset has not been loaded and therefore you can't clone a recordset that is not there.

 

[AshikHusein]

It is doing the exactly same thing when I run the code on the "On Load" event.

 

[Mile-O]

Private Sub Form_Load() 

    Dim rs As DAO.Recordset 
    Dim strAccNumber As String 

    strAccNumber = InputBox("Enter the account number to search in the format 999-9999")

    If Len(strAccNumber) = 8 Then
        If IsNumeric(Left(strAccNumber),3)) And Mid(strAccNumber, 4, 1) = "-" And IsNumeric(Mid(strAccNumber,5,4)) Then
            Set rs = Me.RecordsetClone 
            With rs
                .MoveLast
                .MoveFirst
                .FindFirst "[Account Number] = """ & strAccNumber & """"
                Me.Bookmark = .Bookmark 
                .Close
            End With
        End If
    Else
        Call Form_Load
    End If

End Sub

 

[AshikHusein]

That worked perfectly. Thanks a million!!

 

[RayH]

Hi Milo

Can you explain why you have used this.

    .MoveLast
    .MoveFirst
    .FindFirst "[Account Number] = """ & strAccNumber & """"

I understand what they do individually but not when used one after the other like this.

Thanks

 

[Mile-O]

It "populates" the recordset.

I don't know why you can't just say .FindFirst without having to go to the end of the recordset and return to the start.

Many is the time I've used .Recordset and got a result of 0 as I never "populated" the recordset object.

I suppose it is because you have basically assigned the recordset to the rs object but it just points to the recordset and it is only once you physically get an extent of the records within, can you truly manipulate them.

 

[RayH]

gotcha. Makes sense.
cheers