Eliminate showallrecords command

[spleewars]

In a stock form i have set apart a text box with row source connected to a table/query to display all the 'code numbers' available. I used the following code to display a search:

Private Sub cmdsearch_Click()
Dim reference As String
    Dim strSearch As String

    If IsNull(Me![txtsearch]) Or (Me![txtsearch]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
        Me![txtsearch].SetFocus
    Exit Sub
End If
    
[B]    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("CodeNumber")
    DoCmd.FindRecord Me!txtsearch[/B]
        
    CodeNumber.SetFocus
    reference = CodeNumber.Text
    txtsearch.SetFocus
    strSearch = txtsearch.Text

    If reference = strSearch Then
        MsgBox "PRODUCT FOUND!: " & strSearch, , "Successfull!"
        CodeNumber.SetFocus
        txtsearch = ""
        
        Else
          MsgBox "PRODUCT DOESN'T EXIST!: " & strSearch & " -  Try Again.", _
            , "Invalid Search Criterion!"
            txtsearch.SetFocus
    End If
End Sub

I need to replace the bold code so instead calling "showallrecords", the values written in "txtsearch" field, run through a query and then loaded the details in stock form. Any ideas? Hope i explained myself right

Thanks

 

[KenHigg]

I call this 'bound form muckity-muck world'. To master it means to understand all the form and control event combinations...yuck.

I would just look for the record with a dlookup() and if it finds the record then set the forms query with a parameter based on the value.

 

[Alansidman]

Cross posted at http://www.accessforums.net/showthread.php/25205-Running-query-instead-of-showallrecords-command

please read this link

http://www.excelguru.ca/content.php?184

 

[spleewars]

Kenhigg, i do agree with you: this is the worse of the worse search method possible did this coding around 7 years ago and now I am revamping the database completely.

Actually i found the solution to this problem by myself by setting up a new dedicated query for the form and filter the code number.

 

[KenHigg]

Cool, glad you have it working

..