A very basic search form in access

[jim Bob]

Hi,
This is probably very simple to do so if anyone can point me to the
right place for reading, it would be much appreciated.

I just want to build a very basic search form where i can enter a name
or part of a name into a text box, press a button, and the entered value
gets inserted into a sql query and the results of the query gets
displayed into a list or text box.

(ie take the input from the text box and plug that variable in my query)

Thanks

*** Sent via Developersdex http://www.developersdex.com ***

[GAVO-UK]

Your could create a form with two pages. On the first page you can
place the text boxes on which you would enter your search criteria and
on the second page you can place a list box where the search result
would be displayed.

on the following example, I have three text boxes and one listbox

txt1ABFirstName = First Name of Customer
txt1ABLastName = Last Name of Customer
txt1ABPhoneNo = Phone Number (on my table each customer can have 1 or 2
TEL Nos)

lstSearchCustomers

This code I extracted from a working Search form, it may not be the
correct approach but it works.

Dim cn As Connection
Dim rs As ADODB.Recordset

Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset

SQL = "SELECT "
SQL = SQL & " tblCustomer.CustomerID, "
SQL = SQL & " tblCustomer.FirstName+' '+tblCustomer.LastName AS
CustomerName, "
SQL = SQL & " ISNULL(tblCustomer.Phone1,tblCustomer.Phone2), "
SQL = SQL & " tblArea.AreaCode " & _ " "
SQL = SQL & "FROM tblCustomer "
SQL = SQL & "INNER JOIN "
SQL = SQL & " tblAgent "
SQL = SQL & " ON tblCustomer.AgenteID = tblAgent.AgentID "
SQL = SQL & "INNER JOIN "
SQL = SQL & " tblArea "
SQL = SQL & " ON tblAgent.AreaID = tblArea.AreaID " & _ " "
SQL = SQL & "WHERE ( tblCustomer.FirstName LIKE '%" &
Form("txt1ABFirstName") & "%' ) "
SQL = SQL & " AND ( tblCustomer.LastName LIKE '%" &
Form("txt1ABLastName") & "%' ) "
SQL = SQL & " AND ( ISNULL(tblCustomer.Phone1 + '. ', ' ')+'
'+(ISNULL(tblCustomer.Phone2 + '. ', ' ')) LIKE '%" &
Form("txt1ABPhoneNo") & "%' ) "
SQL = SQL & " AND ( tblCustomer.Status = '1' ) "
SQL = SQL & " AND ( tblArea.AreaCode = '1' ) " & _ " "
SQL = SQL & "ORDER BY tblCustomer.FirstName;"
' Search by Customer's Details
If IsNull(Me.txt1ABFirstName) And IsNull(Me.txt1ABLastName) And
IsNull(Me.txt1ABPhoneNo) Then
Me.lblMessage.Caption = "Please enter a search criteria
before proceeding."
Me.txt1ABFirstName.SetFocus
Else
With rs
.Source = SQL
.ActiveConnection = cn
.Open
End With

If rs.RecordCount >= 1 Then
If rs.RecordCount > 100 Then
Me.lblMessage = "Your search has returned too many
possible matches, please narrow down you search."
Else
Me.lstSearchCustomers.RowSource = rs.Source
Me.lstSearchCustomers.Requery
Me.lstSearchCustomers.SetFocus
rs.Close
Set rs = Nothing
DoCmd.GoToPage 2
End If
Else
msgbox "No Records have been found."
End If
End If

GAVO

[Lyle Fairfield]

jim Bob <fr****@gmail.com> wrote in news:HL***************@news.uswest.net:

I just want to build a very basic search form where i can enter a name
or part of a name into a text box, press a button, and the entered value
gets inserted into a sql query and the results of the query gets
displayed into a list or text box.

1. I have a Form called "Cheques".

2. In the Form is a TextBox named "Payee".

3. I open the Form.

4. I right click on the Form

5. From the PopUp menu I click on "Filter By Form"

6. A new Form appears; its caption is "Cheques: Filter by Form".

7. In the Payee TextBox I enter "Like Sh*" without the quotation marks.

8. I right click on the new Form.

9. From the PopUp menu I click "Apply Filter/Sort".

10. The old Form appears but now it shows only records beginning with "Sh".
I say to myself, "Now I know why I paid $130 CAD for Access. It does so
many things so much better than I could ever do myself, even though I have
been programming databases for more then twenty years."

--
Lyle Fairfield

[Sebastian]

Check this:
http://www.datapigtechnologies.com/f...earchform.html

Sebastian

[Jana]

Jim Bob:

Here's one way, but I'm sure there are others out there :)

Create an unbound text box on your form and name it txtSearchString.
Create a list box on your form and call it lstSearchResults.
In the AfterUpdate event of your text box, create a procedure something
like this:

Private Sub txtSearchString_AfterUpdate()
Dim MySQL As String
MySQL = "SELECT DISTINCTROW ATT_ID, FIRM, ATT_NAME," _
" CITY, STATE FROM ATT "
If Nz(Trim(Me!txtSearchString), "") <> "" Then
MySQL = MySQL & "WHERE Instr(1, [FIRM], '" & _
Me!txtSearchString & "')>0 "
End If
MySQL = MySQL & " ORDER BY FIRM"
Me.lstSearchResults.RowSource = MySQL
Me!lstSearchResults.Requery
End Sub

What we're doing is building the SQL on the fly for the row source of
your list box, then forcing a requery. You'll have to customize your
SQL string to match what you're wanting returned to the list box. If
you want your search to be case sensitive, change "')>0 " to "',0)>0 ".
Otherwise, it will be case insensitive. Check out the documentation
on the Instr function in Help for more details on the function.

If you need more detailed instructions, let me know.

HTH,
Jana

[Steve]

All right, here's what I would do to build a crude search form for
searching a name field:

Assuming:
-Form name is frmName
-Table name is TableName
-Field to search on is LastName
-Code is placed in Button1_KeyDown event
-Search string is typed into txtSearch
-Listbox is lstBox
-Rowsource for lstBox is the following SQL string:
SELECT TableName.LastName FROM TableName WHERE TableName.LastName LIKE
'" & Forms!frmName!txtSearch & "*'"

Code:
lstBox.Requery

When you type something into the txtSearch textbox, LastName fields
matching or close to that text will appear in the lstBox ListBox.

Code is untested.

[jim Bob]

Hi Guys,
thanks for all your replies. The reason why i haven't responded was
because I have been so impressed with the tutorials at
http://www.datapigtechnologies.com/AccessMain.htm, the link that
Sebastian provided. It is the bomb. Thank you so much. The guy is an
expert and actually shows how easy working with Access and VB is.

Thanks again.

*** Sent via Developersdex http://www.developersdex.com ***

[Jerome Ranch]

great site thanks

On Thu, 29 Dec 2005 11:00:03 GMT, jim Bob <fr****@gmail.com> wrote:

Hi Guys,
thanks for all your replies. The reason why i haven't responded was
because I have been so impressed with the tutorials at
http://www.datapigtechnologies.com/AccessMain.htm, the link that
Sebastian provided. It is the bomb. Thank you so much. The guy is an
expert and actually shows how easy working with Access and VB is.

Thanks again.

*** Sent via Developersdex http://www.developersdex.com ***