Coding the Search Comand

[Neozo]

I have this form where you have to enter records of Provinces, Islands,Area,Or ganization and a sub form where you can enter the Project amounts allocated to the above fields. I created a Search txtbox so that when you want to seach any one of the records entered above and click on the search button it displays that information for editing or just to view it... any help here on how to go about coding it?

[Scott Price]

Here's a link to Allen Browne's website where he has put together an example database to illustrate search methods.

Kind regards,
Scott

[Neozo]

ok i have this code where you enter a record in the text and then it displays the record found and u can edit it in the same form, however i am able to only do a search on the Project Number which is a text field but i also want to include other records like Province which is in a combo box to do a search on that to display the records..

here's the code

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

    If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
        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 ("ProjNum")
    DoCmd.FindRecord Me!txtSearch
        
    ProjNum.SetFocus
    strStudentRef = ProjNum.Text
    txtSearch.SetFocus
    strSearch = txtSearch.Text
        
'If matching record found sets focus in strStudentID and shows msgbox
'and clears search control

    If strStudentRef = strSearch Then
        MsgBox "Match Found For: " & strSearch, , "Congratulations!"
        ProjNum.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 Criterion!"
            txtSearch.SetFocus
    End If
End Sub

this code only search on one field which is Project number but i'd like it to also search the other records which a re displayed in a combo box on the same form if i want to edit a record... hope that makes sense

[Scott Price]

Have you looked at the link I gave you? There is a link on that page to a detailed code explanation of how Allen has set up the search string to find multiple criteria.

Regards,
Scott

[Neozo]

Yeah i have a look at it. that solution requires you to use filters for each field thus you required several text boxes and combo boxes for each field. what i want is to have just one text box say txtSearch with a command button cboSearch so that if a user wants to search the whole form, he can just type in any particular record based on whatever field if that record is displayed in a text box or combo box, when he click on the search button it displays the information on that same form...
that form is used to enter records, so all i want is if a user wants to edit a record he doesnt have to click on next to go through every record to find that record he want to edit.. all he has to do is type in the information he wanted and it will take him to that record based on watever record he wanted to edit eg. either prjNum which is in a txtfield or Province which is in a combo box.....

[Scott Price]

Don't want much do you!! :-)

What you are asking is probably possible, but I've never tried it.

At this point you should ask yourself if what you are wanting is worth the extra hard work and code writing that you will need to do to accomplish it. I have the utmost regard for Allen Browne, and if he decided that using separate text boxes to search separate fields and filter the form based on those criteria, then I'm quite sure that it's easier to do it that way!

If you want to go ahead and try coding this yourself, here's what I would try if it were me (I can help if you run into trouble, but I won't write the code for you :-)

First set a variable to point to the controls in the detail section of your form. Then a boolean variable to hold a successful or unsuccessful try. Then you'll end up writing a For Each [control] In... construct to loop through the various controls on your form and attempt to apply the search criteria to the contents of that control. If the search was successful, turn the boolean to true and exit the routine, otherwise leave the boolean as false and continue to the next control.

If you wrote the code above, I have no doubt you can write the code to do this part too :-)

Regards,
Scott