Create a search button for a form 1

[masinod]

I am trying to create a form that will look at any number of the given varibles i describe to it search the corresponding table and then spit out the report. I am familiar with creating the report and form but what i have trouble with is getting the info from the text box into the code to have the code search the table. any help would be greatly appreciated

 

[PHV]

getting the info from the text box into the code
Which code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[masinod]

basically i want the user typed value of "firstname" to be the criteria in which the vba code executes itself in searching for the table value

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[masinod]

pretty much the whole thing i dont know what it is i can write mot sql and have no problem with almost all code except in creating a search fields. i understand how to create recordsets and that but when it comes to the code for search field i am as lost as a new born baby. any and all help would be appreciated i am sry to say but this is the one aspect that for some reason i have trouble grasping!

 

[MajP]

If you are using a single field the solution is easy. If you have the option of many types of controls with many fields the the user can choose from in order to build the search criteria, the solution gets more complicated. But basically you need to build the where clause. For a single field it is something like

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    
    stDocName = "rptYourReportName"
    stLinkCriteria = "yourFieldName = '" & Me.yourSearchControlName & "'"
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_Command10_Click:
    Exit Sub

Err_Command10_Click:
    MsgBox Err.Description
    Resume Exit_Command10_Click
    
End Sub

Disregard the single quotes if the search field value is non-text.

If I had multiple search criteria I would build a function to build the where clause. I would call the function "getLinkCriteria" and then in the above code I would change

stLinkCriteria = getLinkCriteria()

 

[masinod]

ok i know i am gonna sound really stupid for asking this but what does the & dilinate in the code. i see it all the time and cannot seem to find out what it atually means in refrence to the code

 

[PHV]

& is the concatenation operator in VBA.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[masinod]

ok i tried creating a search form and attaching this code to the cmd button to run the sql statement. however all i recieve is an error stateing the select statement is looking for a word that is either mispelled or missing punctuation is incorrect. any ideas on how to fix this or an easier way would be greatly appreciated

Private Sub Command24_Click()
On Error GoTo Err_Command24_Click

'set up active conneciton to store the data searched in the mysql variable
Dim rec1 As ADODB.Connection
Set rec1 = CurrentProject.Connection
Dim myrec As New ADODB.Recordset
myrec.ActiveConnection = rec1

'create a sql statement in small chunks to use search form to find its parameters
Dim mysql As String
mysql = "SELECT [Member Info].FirstName, [Member Info].LastName,"
mysql = mysql + "[Member Info].Dorm, [Member Info].EmailName,"
mysql = mysql + "from [Member Info]"
mysql = mysql + "WHERE ((([Member Info].FirstName) Like '!forms!membersearch!firstname' & '*'))"
mysql = mysql + "Or ((([Member Info].LastName) Like '!forms!membersearch!lastname' & '*')) "
mysql = mysql + "Or ((([Member Info].Dorm) Like '!forms!membersearch!dorm' & '*'))"
mysql = mysql + "Or ((([Member Info].email) Like '!forms!membersearch!email' & '*'))"

myrec.Open mysql

DoCmd.OpenReport memsearch, acViewPreview
myrec.Close
Set myrec = Nothing
Set rec1 = Nothing

Exit_Command24_Click:
Exit Sub

Err_Command24_Click:
MsgBox Err.Description
Resume Exit_Command24_Click



End Sub