Need help finding a record (1 Viewer)

[chuckcoleman]

Hi, I have a continuous form that produces typically a small number of records that includes for each record a [CustomerID]. When I see the results I click on the "Find" button that successfully opens the Customer Form and finds the customer that matches the [CustomerID] from the previous form. That all works. On the Customer Form, there are two command buttons that will navigate to the next record or the previous record. Using an example, if the first form has a customer with a CustomerID of 2559 when I click on the "Find" button it opens the Customer Form to the customer that has a [CustomerIDd] of 2559. The form is now filtered and is on record one of one. What I want the next record button to do is, (I think the logic here makes sense), is to capture the [CustomerID] of 2559, (it does), remove the filter and then go to the record where [CustomerID] = 2559 and then does DoCmd.GoToRecord , , acNext. That happens which gets me to the CustomerID of 2559 and then moves to the next record, say 2560. However, if I click on the next record button again to go to the next record, I get an error message of: "You can't go to the specified record."

Private Sub NextRcd_Click()
    On Error GoTo Err_NextRcd_Click
    Dim CurrentCustomer As Integer
    CurrentCustomer = Me.CustomerIDX
    Me.Filter = ""
    Me.FilterOn = True
    DoCmd.GoToControl "CustomerIDX"
    Me.CustomerIDX.SetFocus
    DoCmd.GoToRecord , , , CurrentCustomer
    DoCmd.GoToRecord , , acNext
    CurrentCustomer = Me.CustomerIDX
    Me.Filter = ""
    Me.FilterOn = False
  
Err_NextRcd_Click_Exit:
    Exit Sub
    
Err_NextRcd_Click:
    MsgBox Err.Description
    Resume Err_NextRcd_Click_Exit
End Sub

So, what am I doing wrong?

Chuck

 

[theDBguy]

Hi Chuck. To avoid all that filtering and unfiltering (and marking your spot in the process), why not just open the other form to the selected record without using a filter? You'll then be free to navigate up or down as you wish.

 

[bastanu]

Not tested, but what happens if you remove the filter, then do a Me.Recordset.Requery then move next?

Cheers,

 

[theDBguy]

Not tested, but what happens if you remove the filter, then do a Me.Recordset.Requery then move next?

Cheers,

Just guessing... but the form will probably go to the first record when the filter is removed.

 

[bastanu]

You're right, just tried it... This should work:

Private Sub NextRcd_Click()
    On Error GoTo Err_NextRcd_Click
    Dim CurrentCustomer As Long
    CurrentCustomer = Me.CustomerIDX
    Me.Filter = ""
    Me.FilterOn = False
    Me.CustomerIDX.SetFocus   
    DoCmd.FindRecord CurrentCustomer
    DoCmd.GoToRecord , , acNext
      
Err_NextRcd_Click_Exit:
    Exit Sub
    
Err_NextRcd_Click:
    MsgBox Err.Description
    Resume Err_NextRcd_Click_Exit
End Sub

 

[chuckcoleman]

Hi Chuck. To avoid all that filtering and unfiltering (and marking your spot in the process), why not just open the other form to the selected record without using a filter? You'll then be free to navigate up or down as you wish.

Hi DBG! Here is the code I use that opens the Customer Form from the "Customers with Similar Last Names Form". I've tried it with and without the filtering. In both cases, when the Customer Form opens, it opens to the correct customer, however it's record 1 of 1.

   DoCmd.GoToControl ("CustomerIDX")
    Dim CustNbr As String
    Dim CustNbr2 As TempVars
    CustNbr = Me.CustomerIDX
    TempVars!CustNbr2 = CustNbr
    DoCmd.Close acForm, "Customer Form"
 '   MsgBox ("CustNbr after closing form is: " & CustNbr)
 '   DoCmd.OpenForm "Customer Form", , , "[CustomerID] = " & Me.CustomerIDX
    DoCmd.OpenForm "Customer Form", , , "CustomerID = " & TempVars!CustNbr2

 '   Me.Filter = ""
 '   Me.FilterOn = False
    DoCmd.Close acForm, "Customers with Similar Last Names Form"

 

[bastanu]

Using Docmd.OpenForm will open the target form filtered if using the Where condition; to do what theDBGuy suggested you would open the form without the Where condition but pass the CustomerID as the OpenArg. Then in the new form's Load event you navigate to that record by using Docmd.FindRecord (as shown in my previous post), Docmd.SearchForRecord (kinda' similar to the previous one) or using the bookmark property of the form and FindFirst method of its recordset(clone) (put that in the Load event of the "Customer Form"):

Me.RecordsetClone.FindFirst "[CustomerID]=" & Me.OpenArg
Me.Bookmark=Me.RecordsetClone.Bookmark

 

[chuckcoleman]

DBG & bastanu, Good work from both of you. It works. Here is the code that works.

I appreciate all your help! You made a difference.

Chuck

Private Sub FindCustX_Click()
On Error GoTo Err_FindOrder

    DoCmd.GoToControl ("CustomerIDX")
    Dim CustNbr As String
    Dim CustNbr2 As TempVars
    CustNbr = Me.CustomerIDX
    TempVars!CustNbr2 = CustNbr
    DoCmd.Close acForm, "Customer Form"
    DoCmd.OpenForm "Customer Form", , , "CustomerID = " & TempVars!CustNbr2
    DoCmd.Close acForm, "Customers with Similar Last Names Form"
    
Exit_Command23_Click:
    Exit Sub
    
Err_FindOrder:
    MsgBox Err.Description
    Resume Exit_Command23_Click
End Sub

Private Sub NextRcd_Click()
    On Error GoTo Err_NextRcd_Click
    Dim CurrentCustomer As Long
    CurrentCustomer = Me.CustomerIDX
    Me.Filter = ""
    Me.FilterOn = False
    Me.CustomerIDX.SetFocus
    DoCmd.FindRecord CurrentCustomer
    DoCmd.GoToRecord , , acNext
 
    
Err_NextRcd_Click_Exit:
    Exit Sub
    
Err_NextRcd_Click:
    MsgBox Err.Description
    Resume Err_NextRcd_Click_Exit
End Sub

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[bastanu]