Jump to a specific record in a continuous form

[Djblois]

I want to be able to jump to a specific record using code in a continuous form based on a unique ID field. How can you do this?

The reason I want to be able to do this is I want my users to be able to search by Confirmation Number and jump right to that number.

 

[boblarson]

You can do this with the Wizard that adds a combo box to go there but it would be something like this:

    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[QuoteID] = " & Str(Nz(Me![cboSelect], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

 

[MSAccessRookie]

I want to be able to jump to a specific record using code in a continuous form based on a unique ID field. How can you do this?

The reason I want to be able to do this is I want my users to be able to search by Confirmation Number and jump right to that number.

The following VB Code Opens a Form and either displays information pertaining to a particular Researcher for the purpose of Modification or Viewing, or displays a New Record to Add a new Researcher. It is activated by Double Clicking on the Researcher Control in the WorkOrders Form. You will need to substitute as required if it suits your needs.

Private Sub ResearcherID_DblClick(Cancel As Integer)    ' fmrWorkOrders
    Dim rst As Recordset
 
    DoCmd.OpenForm "fmrPeople", acNormal
 
    If IsNull(Me.ResearcherID) Then
        ' Start at the next available new record
        DoCmd.GoToRecord , , acNewRec
 
        [Forms]![fmrPeople].Form.Title.SetFocus
 
    Else
        Set rst = Forms!fmrPeople.Form.RecordsetClone
 
        rst.FindFirst "[Person_ID] = " & CStr(Me.ResearcherID.Column(1))
 
        If Not rst.NoMatch Then
            Forms!fmrPeople.Form.Bookmark = rst.Bookmark
        Else
            MsgBox CStr(Me.ResearcherID.Column(0)) & " Not Found!"
        End If
 
        [Forms]![fmrPeople].Form.Title.SetFocus
 
        rst.Close
        Set rst = Nothing
 
    End If
 
End Sub

ADDENDUM: It seems that Bob has beaten me to the post again. That's what I get for having a boss leaving for Europe in 1/2 hour.

 

[Djblois]

I know how to open a new form with the recordset that I am looking form but I dont want that. I want to jump to a record that is on a continuous form. Here is a pic to explain it better.

 

[MSAccessRookie]

I know how to open a new form with the recordset that I am looking form but I dont want that. I want to jump to a record that is on a continuous form. Here is a pic to explain it better.

I believe that if a Form is already open, then the "Open a New Form" aspect of my example will be ignored, and the existing Form will be treated as if it were just opened. Since my example was based on clicking a box, the effect will be the same as you are looking for. Modify it as you require for your needs.

By the way, if all you want is to search a column on a form that is already opened, then Access has a built-in function to do that. You can access it by clicking on the icon on the Edit Toolbar that looks like a pair of Binoculars (or by typing Ctrl/F if you prefer Keyboard Shortcuts)

 

[boblarson]

I know how to open a new form with the recordset that I am looking form but I dont want that. I want to jump to a record that is on a continuous form. Here is a pic to explain it better.

My code should do that (just make sure to reference the correct name). Or you can actually add a combo box to do that with the wizard. If you are on the continuous form, you can add a combo and the wizard will give you THREE options instead of the normal two. One is to find a record on your form. That's how I got my code. It works for Continuous forms as well as single forms.

 

[Bust_Ed]

You can do this with the Wizard that adds a combo box to go there but it would be something like this:

    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[QuoteID] = " & Str(Nz(Me![cboSelect], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

A little bit more comments will be helpful, i have similar situation, but all i need is to open form at record, which date field is most closer to current date.

 

[cyberman55]

Here's a simple modification of the above code to find (and go to) a record in a subform from a combo box that exists on a main form- the subform:

Private Sub Combo54_AfterUpdate()
On Error GoTo HandleErr

Dim rs As Object

Set rs = Me.Recordset.Clone 'this is the main form
rs.FindFirst "[Employee ID Code] = " & str(Me![Combo54])
Me.Bookmark = rs.Bookmark

rs.Close
Set rs = Me.cleaningroutessub.Form.Recordset.Clone 'this is the subform

If Not rs.NoMatch Then
rs.FindFirst "[Employee ID Code] = " & str(Me![Combo54])
Me.cleaningroutessub.Form.Bookmark = rs.Bookmark
Else
Debug.Print 'didn't work
End If

rs.Close
Set rs = Nothing

Exithere:
Exit Sub

HandleErr:
Select Case Err.Number
Case Else
Debug.Print "Error " & Err.Number & ": " & Err.Description & " form_cleaning routes.Combo54_AfterUpdate"
End Select

End Sub

 

[tubar]

as in what i posted first of all?