Go to Record Combobox issues (1 Viewer)

[alikona]

Hi all,

I'm having some odd issues with my record selecting combobox on my form.

Form name: frm_OrderDetails
Bound table: tbl_Orders
Combobox: GoTo (unbound, placed in the header of the form)

Combobox settings -
Rowsource:

SELECT [tbl_Orders].[OrderID], [tbl_Orders].[Order], [tbl_Orders].[PO], [tbl_Orders].[DateOrdered] FROM tbl_Orders;

Bound Column: 1
Column Count: 4
Column Widths: 0";1";1";1"

How my database is set up, the main form (frm_Orders) is a basically a lookup/reference form that users can filter orders, which are displayed in a subform datasheet view. They can double click on an order to open up the frm_OrderDetails form which allows editing.

I've tried using the wizard to create a combobox to allow users to navigate to another record within the frm_OrderDetails form. It works until I close the form and open it again via the main form. Nothing happens when I select a different record from the combobox.

I've also tried the following VBA instead of the macro generated by the wizard in the AfterUpdate event for the combobox but the same issue occured. It worked until I closed/reopened the form. This one produces the error message box when selecting a different record:

   On Error Resume Next
    ' Dim rst As Object
    Dim rst As DAO.Recordset
    Set rst = Me.RecordsetClone
    rst.FindFirst "OrderID = " & Me.GoTo
    If Not rst.NoMatch Then
        Me.Bookmark = rst.Bookmark
    Else
        MsgBox "Selection not found"
    End If
    'clean up
    Set rst = Nothing

Any ideas?

 

[theDBguy]

Hi. Just in case it is related to your issue, would you mind posting the code behind your double-click event to open frm_OrderDetails as well? Thanks.

 

[alikona]

Private Sub Order_DblClick(Cancel As Integer)
Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frm_OrderDetails"
    
    stLinkCriteria = "[Order]= '" & Me![Order] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

 

[theDBguy]

Private Sub Order_DblClick(Cancel As Integer)
Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frm_OrderDetails"
    
    stLinkCriteria = "[Order]= '" & Me![Order] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

Hi. Thanks. So, if I understand it correctly, try the following change:

   On Error Resume Next
     [COLOR=red]If Me.FilterOn=True Then Me.FilterOn=False[/COLOR]
   ' Dim rst As Object
     Dim rst As DAO.Recordset
     Set rst = Me.RecordsetClone
     rst.FindFirst "OrderID = " & Me.GoTo
     If Not rst.NoMatch Then
         Me.Bookmark = rst.Bookmark
     Else
         MsgBox "Selection not found"
     End If
     'clean up
     Set rst = Nothing

 

[alikona]

That seems to have fixed it, thanks!

Another issue I'm having with this is I have a new order button on frm_Orders that opens frm_OrderDetails to a new record.

The code for that button is:

Private Sub NewOrderBTMain_Click()
DoCmd.OpenForm "frm_OrderDetails", , , , acFormAdd
End Sub

Once I fill out the information and try to use my GoTo combobox (which is populated with records), I get the "Selection not found" error.

 

[theDBguy]

That seems to have fixed it, thanks!

Another issue I'm having with this is I have a new order button on frm_Orders that opens frm_OrderDetails to a new record.

The code for that button is:

Private Sub NewOrderBTMain_Click()
DoCmd.OpenForm "frm_OrderDetails", , , , acFormAdd
End Sub

Once I fill out the information and try to use my GoTo combobox (which is populated with records), I get the "Selection not found" error.

In that case, try adding this line to the combo code:


Me.DataEntry = False


Hope that helps...

 

[alikona]

That solved the problem, again thank you!

 

[theDBguy]

That solved the problem, again thank you!

Hi. You're welcome. Glad we could assist. Good luck with your project.