CODEWITHSUNDEEP
formsms-access
abalter
abalter

Reputation: 10383

Stop search dialog from coming up in MS Access form

The "Find and Replace" dialog keeps coming up after my button event runs. Why and what can I do to stop it?

enter image description here

Private Sub btn_Find_Click()
    Dim query_string As String

    'MsgBox (Me.InventoryDetailsID)

    query_string = "SELECT " & _
               "tbl_Inventory_Header.InventoryHeaderID, " & _
               "tbl_Inventory_Header.InventoryTitle, " & _
               "tbl_Inventory_Header.Author, " & _
               "tbl_Lookup_MediaTypes.MediaTypeDescription, " & _
               "tbl_Inventory_Details.ShelfNumber, " & _
               "tbl_Lookup_Vendors.VendorName, " & _
               "tbl_Inventory_Header.Year_Publish_Produced, " & _
               "tbl_Inventory_Header.InventoryDescription " & _
               "FROM (((tbl_Inventory_Header " & _
               "INNER JOIN tbl_Inventory_Details ON tbl_Inventory_Details.InventoryHeaderID = tbl_Inventory_Header.InventoryHeaderID) " & _
               "INNER JOIN tbl_Lookup_Vendors ON tbl_Lookup_Vendors.VendorID = tbl_Inventory_Header.VendorID) " & _
               "INNER JOIN tbl_Lookup_MediaTypes ON tbl_Lookup_MediaTypes.MediaTypeID = tbl_Inventory_Header.MediaTypeID) " & _
               "WHERE tbl_Inventory_Details.InventoryDetailsID LIKE '" & Me.InventoryDetailsID & "';"

    Me.Controls!test.Value = query_string

    'MsgBox ("Querying for record data")
    CurrentDb.QueryDefs("qry_Inventory_Header_ISBN").SQL = query_string

    Dim recordSet As DAO.recordSet
    Set recordSet = CurrentDb.OpenRecordset("qry_Inventory_Header_ISBN")

    If Not recordSet.RecordCount > 0 Then
        MsgBox ("No record found for barcode " & Me.Controls!InventoryDetailsID)
        GoTo Exit_btn_SearchByHeaderId_Click
    Else
        'MsgBox ("record found")
    End If

    'MsgBox (recordSet!InventoryTitle)

    'MsgBox ("filling controls")
    Me.Controls!InventoryTitle.Value = recordSet!InventoryTitle
    Me.Controls!Author = recordSet!Author
    Me.Controls!MediaTypeDescription = recordSet!MediaTypeDescription
    Me.Controls!VendorName = recordSet!VendorName
    Me.Controls!Year_Publish_Produced = recordSet!Year_Publish_Produced
    Me.Controls!InventoryDescription = recordSet!InventoryDescription
    Me.Controls!ShelfNumber = recordSet!ShelfNumber

    Me.Controls!ISBN.SetFocus

    On Error GoTo Err_btn_SearchByHeaderId_Click
        'MsgBox ("got error")
        Screen.PreviousControl.SetFocus
        DoCmd.RunCommand acCmdFind

Exit_btn_SearchByHeaderId_Click:
        'MsgBox ("exit")
        Exit Sub

Err_btn_SearchByHeaderId_Click:
        MsgBox Err.Description
        Resume Exit_btn_SearchByHeaderId_Click
End Sub

Upvotes: 1

Views: 42

Answers (1)

Parfait
Parfait

Reputation: 107642

The Find dialog derives at this command DoCmd.RunCommand acCmdFind towards the bottom just before the error handlers.

And according to the code, this Find command will always run since no If Then, Do While Loop, For Next, or Goto is wrapped around it.

Consider removing it, re-positioning it, or placing an Exit Sub before it if this Find command should be part of an error handle.

Upvotes: 1

Related Questions