Search Dialog Box Issues

[gogirl]

Hello, I'm trying to create a search dialog box which will display a table of matching records and allow you to pick the one you would like to view.

I can't seem to get it to work and was wondering if anyone has any suggestions. I've added comments in red.

Private Sub Search_Click()
Const cInvalidDataError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Company
If Nz(Me.CUST_NM) <> "" Then
    'Create Predicate
    strWhere = strWhere & " AND " & "CUST_NM = " & Me.CUST_NM & ""
[red]'can't assign value to object[/red]
End If

' If KPI Date
If Nz(Me.KPI_YR_MO) <> "" Then
    'Add it to the predicate - exact match
    strWhere = strWhere & " AND " & "KPI_YR_MO = " & Me.KPI_YR_MO & ""
[red]'SQL error[/red]
End If

' If Country
If Not IsNull(Me.WELL_CNTRY_NM) Then
    'Create Predicate
    strWhere = strWhere & " AND " & "WELL_CNTRY_NM = " & Me.WELL_CNTRY_NM & ""
[red]'Parameter Input box pops up[/red]
End If


' If Region
If Not IsNull(Me.G_RGN_NM) Then
'    'Create Predicate
    strWhere = strWhere & " AND " & "G_RGN_NM = " & Me.G_RGN_NM & ""
[red]'Parameter Input box pops up[/red]
End If

' If Practice
If Not IsNull(Me.Practice) Then
    'Create Predicate
     strWhere = strWhere & " AND " & "Practice = " & Me.Practice & ""
[red]'Parameter Input box pops up[/red]
End If


If strError <> "" Then
    MsgBox strError
Else
    'DoCmd.OpenForm "Browse Products", acFormDS, , strWhere, acFormEdit, acWindowNormal
    If Not Me.FormFooter.Visible Then
        Me.FormFooter.Visible = True
        DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
    End If
    Me.Browse_All_Products.Form.Filter = strWhere
    Me.Browse_All_Products.Form.FilterOn = True
End If

End Sub

Thanks

gogirl

 

[HarleyQuinn]

If any of the fields you're filtering on are text you'll need to wrap the variable in single quotes e.g.

If Nz(Me.CUST_NM) <> "" Then
    'Create Predicate
    strWhere = strWhere & " AND " & "CUST_NM = '" & Me.CUST_NM & "'"
'can't assign value to object
End If

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[gogirl]

HarleyQuinn, I'm now getting an SQL error...The specified field <field> could refer to more than one table listed in the FROM clause of your SQL statement. (Error 3079). I've checked the SQL statements of the queries I'm using. I can't figure out what I'm doing wrong.

 

[HarleyQuinn]

What's the recordsource for your form that you want to display?

You might have to do it like this

If Nz(Me.CUST_NM) <> "" Then
    'Create Predicate
    strWhere = strWhere & " AND " & "MyTableName.CUST_NM = '" & Me.CUST_NM & "'"
'can't assign value to object
End If

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[gogirl]

Brilliant! It worked. Thanks a bunch!

gogirl

 

[HarleyQuinn]

You're welcome, glad I could help

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.