Wildcard Thread crashing

[ledgerr.rob]

Hello Forum,

I have been having a little bit of an issue with a query crashing. I'm using a touch of VBA to run the operation so have landed in this forum but the query forum might be just as appropriate.

I have a form made to help query a database in a variety of ways. One of those is basically a wildcard search on the titles of the entries. This is a type of inventory DB and doing a basic search of a title seems important. I have between 500-600 items entered with a variety of related tables.

I first test the length of the search criteria to make sure something is being searched for. Once something is there, I open a report which has a record source of an underlying query. This query has criteria from the initial search criteria.

First i test the search criteria:

Private Sub btnSearchItem_Click()
'run report rptItemSearch

txtSearchItem.SetFocus

If Len(Me!txtSearchItem.Text) = 0 Then
    MsgBox ("You must enter an item to search for.")
Else
    DoCmd.OpenReport "rptItemSearch", acViewReport
End If

End Sub

I open the report, causing the query to run. Query's SQL"

SELECT tblItem.Item, tblCategory.Category, tblOwner.OwnerFirst AS [Original Owner First], tblOwner.OwnerLast AS [Original Owner Last], tblOwner_1.OwnerFirst AS [Steward First], tblOwner_1.OwnerLast AS [Steward Last]

FROM tblOwner RIGHT JOIN (tblCategory RIGHT JOIN (tblItem LEFT JOIN tblOwner AS tblOwner_1 ON tblItem.CurrentStewardID = tblOwner_1.OwnerID) ON tblCategory.CategoryID = tblItem.CategoryID) ON tblOwner.OwnerID = tblItem.OriginalOwnerID


WHERE (((tblItem.Item) Like "*" & [Forms]![frmReportGenerator]![txtSearchItem] & "*"));

The [Item] field is indexed in the table. Also, when i put a break in the VBA it steps through just fine. Once I step it through the first time and remove the break it will operate fine. If i try to run without first performing the break Access will hang and restart.

Putting up a sample DB is a possibility once i have a little time. I'm sure that would help the community look into this issue. Short of that is there obvious errors in my approach?

I appreciate any ideas and thoughts.

 

[MarkK]

What happens if you just run that query out of the designer?

 

[ledgerr.rob]

Thanks for your reply. I tested what you asked.

I entered the wildcard search in the form's field and ran the query manually. No problems. Then i opened the report manually which triggers the query to run, no problems. It would appear that pressing the button on the form and subsequent code is causing the problem.

 

[MarkK]

What happens if you change the code to this . . . ?

Private Sub btnSearchItem_Click()
    If Nz(me.txtSearchItem, "") = "" Then
        MsgBox ("You must enter an item to search for.")
        me.txtSearchItem.SetFocus
    Else
        DoCmd.OpenReport "rptItemSearch"
    End If
End Sub

 

[ledgerr.rob]

I have put the code you provided in, and by all accounts it seems to run just fine. No hang ups. This is great! Checking for null rather than length is a better approach.

Thanks for taking the time to help me troubleshoot my little problem. Much appreciated! Able to search our family heirlooms much easier now and continue on with our genealogy obsession!!

Rob