Query on form using input from text box

[rexmorgan]

Good day all,
I have a database with several tables, forms, reports, and queries. I would like to create a form that include a textbox and a command button. What I want to query is all people with the Last Name of (whatever value is in the text box) from the table 'Spotters'. I have included a sample of my database. Thanks in advance I have tried to implement code that I got from another example but have failed to get it to work properly. Specifically the form in question is frmReports in the included example. I want to generate a report using a query built with input from the text box included on the frmReports form. Any help is greatly appreciated. The code I attached to the Generate Report button on this form follows. I was unable to implement this properly. Although this code looks promising.

Private Sub cmdLastNameSearch_Click()
    On Error Resume Next
    
    Dim ctl As Control
    Dim sSQL As String
    Dim sWhereClause As String
    
    'Initialize the Where Clause variable.
    wWhereClause = "Where"
    
    'Start the first part of the select statement.
    sSQL = "Select * from spotters "
    
    'Loop through each control on the form to get its value.
    For Each ctl In Me.Controls
        With ctl
            'the only control you are using is the text box.
            'However, you can add as many types of controls as you want.
            Select Case .ControlType
                Case acTextBox
                    .SetFocus
                    'This is the function that actually buids
                    'the clause.
                    If sWhereClause = "Where " Then
                        sWhereClause = sWhereClause & BuildCriteria(.Name, dbText, .Text)
                    Else
                        sWhereClause = sWhereClause & " and " & BuildCriteria(.Name, dbText, .Text)
                    End If
            End Select
        End With
    Next ctl
    
    'set the forms recordsource equal to the new
    'select statement.
    Me.txtLastName = sSQL & sWhereClause
    Me.RecordSource = sSQL & sWhereClause
    Me.Requery
    
End Sub

I hope I have explained this well enough. If not please ask and I will attempt to explain more thoroughly.

 

[namliam]

Unfortunatly I cant open 2007 databases.

What is your function "BuildCriteria" ?

 

[rexmorgan]

I got that code from http://support.microsoft.com/kb/304302 the BuildCriteria is a method in VBA. I personally am not that familiar with it. I simply was trying to take the code provided at the site above and modify it to my needs. I have saved the database as a 2003 format file and included it as an attachment to this.

 

[namliam]

OK, some issues here:
1) No "option explicit" on the top of the module ... not a big problem but ultimatly the root cause

2)
Dim sWhereClause As String
...
wWhereClause = "Where"
...
If sWhereClause = "Where " Then

a) wWhereClause is wrong it should be sWhereClause
b) the "Where" and the "Where " are not the same they need to be the same "Where "

3) BuildCriteria(.Name, dbText, .Text)
This builds a critiria with the name of the control to use in the actual SQL so your last name 'input box' has to be called LastName, not txtLastname.
Simular FirstName for the first name input box not Text34

4) sSQL = "Select * from spotters "
There is no table spotters, remove the S from the end.

With these changes the code should be working as per expectation, barring it not actually showing you your information.

Now some "other" stuff

5) General 1
It is "good practice" to, like you do with your forms, prefix names. Forms (like you do) get frmSomeName (ie. frmReports), while tables go tblName, queries qryName
Also please refrain from using spaces (and other special characters like -)(*&^%$#@!@_ ) in names of any columns, tables, queries, controls, forms etc. this to prevent future issues
I.e.
Do FirstName (yours), dont First Name
Do Wx Intruments, dont Wx Instruments (yours)
Do frmReports, dont: SplashScreen (frmSplashScreen) and dont: Spotter form

6) General 2
There is a number of design issues here with county and country obviously trying to link to the corresponding tables but not working properly. As well as your repetative queries Daniels county, Dawson County, etc.

And as usual, this code of M$ is spiffy looking though not-so-good-semi-abusive-ab-so-frigging-useless-bad-halfway-example...
Try searching the forum for "Search form" and find some samples on here that are MUCH better and MUCH more flexible.

Good luck !

 

[rexmorgan]

Wow! Thanks for all of the advice. I think most of the sloppiness has to do with the fact that I have been up all night and not completely coherent. I usually do better with about sloppiness. It also had a lot of sensitive info that I tried to purge without ruining the idea of what I was trying to do. I found another example and put it to use. I have included it below. Thanks for all of the help. I also will try your suggestions to try to get it work for me.

Private Sub cmdRunQuery_Click()

    Dim db As DAO.Database
    Dim QD As DAO.QueryDef
    Dim where As Variant

    Set db = CurrentDb

    'Delete existing dynamic query, trap error if it does not exist.
    On Error Resume Next
    db.QueryDefs.Delete ("MyQuery")
    On Error GoTo 0

    'Note single quotes surrounding text fields [Ship country]
    'and [Customer ID]
    'Note NO Single quotes surrounding Numeric field [Employee ID]
    where = Null


    If Not IsNull(txtFirstName.Value) Then
        If Len(where) > 0 Then
            where = where & (" AND [FirstName]= '" & Me![txtFirstName] & "' ")
        Else
            where = where & (" [FirstName]= '" & Me![txtFirstName] & "' ")
        End If
    End If


    Set QD = db.CreateQueryDef("MyQuery", "SELECT * FROM Spotter WHERE " & where & ";")

    DoCmd.OpenQuery "MyQuery"

    'Me.Visible = False
    DoCmd.Close acForm, Me.Name

End Sub

I will apply the same logic to the other text box. Thanks again for your help!

 

[namliam]

    If Not IsNull(txtFirstName.Value) Then
        If Len(where) > 0 Then
            where = where & (" AND [FirstName]= '" & Me![txtFirstName] & "' ")
        Else
            where = where & (" [FirstName]= '" & Me![txtFirstName] & "' ")
        End If
    End If

if you are going to be searching for multiple fields changing this to somthing like

    If Not IsNull(txtFirstName.Value) Then
        where = where & (" AND [FirstName]= '" & Me![txtFirstName] & "' ")
    End If

and then just before the sql:

where = mid(where,4)

To take out the first AND
This removes to much lines of code and removes the need for redundant coding

Yes this type of code is (to me) better suited to work with as it will allow you to (more easily) work with wild card searches and other personal needs.
Thought the deleting of the query is not something I would do as it throws all kinds of potentials problems. Instead of the delete and create:

    On Error Resume Next
    db.QueryDefs.Delete ("MyQuery")
    On Error GoTo 0
    Set QD = db.CreateQueryDef("MyQuery", "SELECT * FROM Spotter WHERE " & where & ";")

I would "simply" keep the query and write new SQL into it:

currentdb.querydefs("MyQuery").SQL = "Select...."