SQL in boundless form

[ssampier]

I have a boundless form that I want to be able to perform basic search queries. I defined an click event in Visual Basic. I defined variables for each search box.

What I'd like to do to perform a SQL query on each text box that is Not Null. I'm having difficulty writing sql statements in either design view or Visual Basic.

This may be an easier to do this. I'd appreciate any help you can offer.

 

[grande]

Assuming that TextBox1 has the field name, TextBox2 has the table name, and TextBoxes 3 and 4 contain thigs like "[Whatever] = 3":

If Not IsNull(Me.TextBox3) Then
   strWhere = Me.TextBox3
End If
If Not IsNull(Me.TextBox4) Then
   strWhere = strWhere & " And " & Me.TextBox4
End If

strSQL = "SELECT " & Me.TextBox1 & " FROM " & Me.TextBox2 & " WHERE " strWhere

DoCmd.RunSQL strSQL

However, more information, and an example would make it a lot easier to figure out what you want.

-------------------------
Just call me Captain Awesome.

 

[ssampier]

That's pretty close. Sorry for being vague:

I have a boundless form containing Phone Number, First Name, Last Name, Address, City, email address. All of these are text boxes. The text boxes are named PhoneNumber, FirstName, LastName, Address, City, Email respectively. I defined each text box as variable string in Visual Basic (Dim strPhoneNumber as string), etc.

I would like the SQL return all fields from the table called tblAll containing the requisite fields*. Let me know if you need additional information.

I appreciate your help.

Example: I know a customers phone number, but I'm not sure what their address is. This would return all the information including address information from the phone number.

*

SELECT * FROM tblAll
Where
tblAll.PhoneNumber=%mysearch%

 

[grande]

Okay, so you would have an if statement to build the where, kinda like I had above:

If Not IsNull(Me.txtPhone) Then
   strWhere = "tblAll.PhoneNumber = " & Me.txtPhone
End If
If Not IsNull(Me.txtAddress) Then
   strWhere = strWhere & " And tblAll.Address = " & Me.txtAddress
End If

Then have something like

strSQL = "SELECT * FROM tblAll WHERE " & strSQL

-------------------------
Just call me Captain Awesome.

 

[alvechurchdata]

Don't forget that the target address must have quotes around it:

strWhere = strWhere & " And tblAll.Address = '" & Me.txtAddress & "'"

so that strSQL will come out reading something like:

[tt] ... And tblAll.Address = '1 Main Street'[/tt]

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[ssampier]

Sounds great. Now with the strWhere, what if multiple text boxes have elements in them?

Example:

First Name: David
Last Name: Smith
City: Anytown

the SQL code should be:

SELECT *
FROM tblAll

WHERE tblAll.[First Name]=David AND tblAll.[Last Name]=Smith AND tblAll.[City]=Anytown

BTW, I tried a sample code using only the search for phone number. I run the command, it gives an error messages and highlightes in yellow 'RunCMD.RunSQL strSQL'. I defined my strSQL variable as string. Is the error related to the '' as above?

 

[ssampier]

UPDATE:

that error was DoCmd.RunSQL strSQL

not RunCMD

 

[PHV]

The RunSQL method admits action queries.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ssampier]

PHV,

Thanks for your help. How can I run Select queries?