Go to record ISSUE

[CanWest]

Ok let me see if I can lay this out

Forms Involved:
frm_MainMenu with a subform sfrm_Clients
pfrm_AddClientDuplicateCheck

So pfrm_AddClientDuplicateCheck is opened from a button on the sfrm_Clients subform. The frm_MainMenu and sfrm_Clients remain open but in the background. There are three text boxes that help me filter records to check if a client already exists. This part is working great.

Now lets say my filter results in 5 records that match the criteria, say five people with the first name John, Each of those has a unique ClientId,

I have the following code on a button beside each result as this is a continuos form

Private Sub cmdGoToClientRecord_Click()
    
    Forms!frm_MainMenu!sfrm_Clients.Form.Filter = "ClientID = " & Me.clientid
    Forms!frm_MainMenu!sfrm_Clients.Form.FilterOn = True
    DoCmd.Close
    
End Sub

This successfully filters the sfrm_Clients to show only the record for the corresponding client in the pfrm_AddClientDuplicateCheck form.

Unfortunately that is not exactly what I want to do. Instead of filtering the form and showing 1 of 1 records I want to go to that specific record but still have all of the records available to me with out having to clear the filter.

I have done quite a bit of reading on this subject but it all seems to confuse me. If someone could give me a little guidance in this I would be very greatful

Thank you in advance

 

[JHB]

Take a look at the Bookmark property in the MS-Access Help file, there is one example shown, which you with a small change in the code easy can use in your case.

 

[CanWest]

I gave this a read and am no wiser for it. As I am still learning vba the hard way I need to understand what that code does and how.

 

[JHB]

Try the below code instead of your filter code.

Private Sub cmdFindContactName_Click()
     Dim rst As DAO.Recordset
     Set rst = Me.RecordsetClone
     rst.FindFirst "ClientID = " & Me.clientid
     If rst.NoMatch Then 
        MsgBox "No entry found.", vbInformation
     Else
        Me.[B]Bookmark[/B] = rst.[B]Bookmark[/B]
     End If
     Set rst = Nothing
End Sub

 

[Wiz47]

Try the below code instead of your filter code.

Private Sub cmdFindContactName_Click()
     Dim rst As DAO.Recordset
     Set rst = Me.RecordsetClone
     rst.FindFirst "ClientID = " & Me.clientid
     If rst.NoMatch Then 
        MsgBox "No entry found.", vbInformation
     Else
        Me.[B]Bookmark[/B] = rst.[B]Bookmark[/B]
     End If
     Set rst = Nothing
End Sub

That's it. I go to a record from a combobox using similar code.

 

[CanWest]

Try the below code instead of your filter code.

Private Sub cmdFindContactName_Click()
     Dim rst As DAO.Recordset
     Set rst = Me.RecordsetClone
     rst.FindFirst "ClientID = " & Me.clientid
     If rst.NoMatch Then 
        MsgBox "No entry found.", vbInformation
     Else
        Me.[B]Bookmark[/B] = rst.[B]Bookmark[/B]
     End If
     Set rst = Nothing
End Sub

I have one question. This has to be applied to an underlying form/subform from another open form. I am not sure how to do that. My original code sets the filtre of this form. That was easy to figure out, this is not

 

[JHB]

Post you database with some sample data, (zip it).

 

[Wiz47]

I have one question. This has to be applied to an underlying form/subform from another open form. I am not sure how to do that. My original code sets the filtre of this form. That was easy to figure out, this is not

Private Sub Cborecord_AfterUpdate()
On Error GoTo Err_Combo_AU

Me.RecordsetClone.FindFirst "[ID] = " & Me![Cborecord]
Me.Bookmark = Me.RecordsetClone.Bookmark

Exit_Combo_AU:
    Exit Sub

Err_Combo_AU:
    If Err = 3077 Then
        Resume Exit_Combo_AU
     Else
         MsgBox Err.Description
            Resume Exit_Combo_AU
End If
End Sub

This is the code I use when I click on an item in my combo box (cboRecord). It moves to and displays the record chosen from the combo box.

 

[CanWest]

Post you database with some sample data, (zip it).

Here is the DB

Open the MainMenu Form
Then Click the New Button in the Custom Navigation Area
This opens pfrm_AddClientDuplicateCheck
Use a common first name like John
This will show several results

On the left side of the list each result has a button because this is a continuous form. When clicked this will filter the underlying form sfrm_Clients which is a subform of Main Menu

This works fine but it is not what I want. I want the underlying form sfrm_clients to Go To that record rather than filter the form

 

[JHB]

Replace code in cmdGoToClientRecord_Click() with the below code:

Private Sub cmdGoToClientRecord_Click()
  Dim rst As Recordset
  
  Set rst = Forms!frm_MainMenu!sfrm_Clients.Form.RecordsetClone
  rst.FindFirst "ClientID = " & Me.ClientID
  If rst.NoMatch Then
     MsgBox "No entry found.", vbInformation
  Else
     Forms!frm_MainMenu!sfrm_Clients.Form.Bookmark = rst.Bookmark
  End If
  Set rst = Nothing
  DoCmd.Close
End Sub

 

[CanWest]

Replace code in cmdGoToClientRecord_Click() with the below code:

Private Sub cmdGoToClientRecord_Click()
  Dim rst As Recordset
  
  Set rst = Forms!frm_MainMenu!sfrm_Clients.Form.RecordsetClone
  rst.FindFirst "ClientID = " & Me.ClientID
  If rst.NoMatch Then
     MsgBox "No entry found.", vbInformation
  Else
     Forms!frm_MainMenu!sfrm_Clients.Form.Bookmark = rst.Bookmark
  End If
  Set rst = Nothing
  DoCmd.Close
End Sub

That did it. I had tried several iterations of something like this on my own but was getting nowhere. Thank you very much!!

 

[CanWest]

That's it. I go to a record from a combobox using similar code.

This only works on the form you are on. I needed to go to a record on a different form. Thankyou anyway. I will be able to use this code in other places

 

[JHB]

You're welcome, luck with your project.

 

[Wiz47]

This only works on the form you are on. I needed to go to a record on a different form. Thankyou anyway. I will be able to use this code in other places

No problem. Happy to help.