Search on text and display record

[chrisdedobb]

I am trying to create a form that the user can type a serial number in a text box then click a button which will close this "popup subform" and search the table and goto the record on the previous form that matches with the serial number. I have included a picture file to try and demonstrate what I need it to do.

I have searched the forums many times and either this is not available or I don't know what to search for.

Any help would be greatly appreciated.

 

[WayneRyan]

Chris,

Look in the Sample Database forum here. There are plenty of examples of
Search Forms.

Wayne

 

[chrisdedobb]

Chris,

Look in the Sample Database forum here. There are plenty of examples of
Search Forms.

Wayne

As I mentioned above, I have searched the forums. My situation was not addressed in the Sample Database Forums.

Basically I want to customize the "Find" feature in access. The reason is my users will only be searching on one field and it will always be a partial value.

I know how to write the queries but I don't know how to make one form go to a specific record in another form without filtering out the data the does not match the search.

IE: Say a have a DB with 5 peoples names in it...

1 Tom Hanks
2 Bill Murray
3 Jack Black
4 Will Ferrel
5 Ben Stiller

and then lets say the user clicks a search button. This would bring up a popup search form. The user wants to see Jack Blacks record so they type "black" in the field and press the find button.

This would close the search popup and go to record 3 in the db. But I still want the user to have access to the other records in the db without having to requery or turn off filters. The user should be able to press the next record button and get to record 4 - Will Ferrel.

I still need help with this. Anyone have any ideas or samples?

 

[chrisdedobb]

One of my friends from work just emailed me this. Not sure where he got it, but it seems to be heading in the right direction. He says he changed the form and field names for me but I am still having troubles. Not the experienced with VB. ANy help would be greatly appreciated. Thanks in advance!

Private Sub cmdSearch_Click()
        Dim strSearch As String
    
'Check txtSearch for Null value or Nill Entry first. WORKS - CAD

    If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criteria!"
        Me![txtSearch].SetFocus
    Exit Sub
End If
'---------------------------------------------------------------
        
'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID
        
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("ALL!ANN_NUM")
    DoCmd.FindRecord Me!txtSearch
        
    ALL!ANN_NUM.SetFocus
    strANN_NUM = ALL!ANN_NUM.Text
    txtSearch.SetFocus
    strSearch = txtSearch.Text
        
'If matching record found sets focus in strStudentID and shows msgbox
'and clears search control

    If strANN_NUM = strSearch Then
        MsgBox "Match Found For: " & strSearch, , "Congratulations!"
        ALL!ANN_NUM.SetFocus
        txtSearch = ""
        
    'If value not found sets focus back to txtSearch and shows msgbox
        Else
            MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
            , "Invalid Search Criteria!"
            txtSearch.SetFocus
    End If
End Sub

 

[suepowell]

This opens the popup form to get the value to search for. There is code on the popup to put the required value into an invisible field on the main form called existmo.

DoCmd.OpenForm "frmItemNo", acNormal, , acFormEdit, , acDialog

Set the focus on the main form to the field you want to search.

Me.MONo.SetFocus

Search for the correct record, not sure about matching a partial field, maybe the help for findrecord can help with this one

DoCmd.FindRecord existmono, acEntire, , acSearchAll, , acCurrent

Go to the record you have found

DoCmd.GoToRecord acActiveDataObject, , acGoTo, CurrentRecord

Refresh the form to see the record.

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Hope this helps and is not too late, of course you will need some code to handle values that don't exist.

Sue

 

[chrisdedobb]

Thanks Sue!

Sounds exactly like what I was looking for! I will give it a shot.

 

[godofhell]

to search for partial names you can use the Like in front of your value. Note the 2 "*" in front and after the SearchField, doing this allows you to search by anything matching your search box. Example the users types black in the search box, the results will be Darkblack, blackAngel, abrablackable (no such word), you get the point.

Dim SearchField as string
Dim SearchBy as string
SearchField = txtBox.value
If SearchBy Like ("*" & SearchField & "*") Then
End If
Note: this line can also be written as
If SearchBy Like ("*" & txtBox.value & "*") Then
End If


If strANN_NUM Like ("*" & strSearch & "*") Then
MsgBox "Match Found For: " & strSearch, , "Congratulations!"
ALL!ANN_NUM.SetFocus
txtSearch = ""

'If value not found sets focus back to txtSearch and shows msgbox
Else
MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
, "Invalid Search Criteria!"
txtSearch.SetFocus
End If