Openform with search criteria

[NachoMan]

Openform with search criteria

Friends,
Having trouble making my search form work correctly. I have a search form which returns records in a query (Dynamic_Query). Instead of the query, I want the the retrieved records to be displayed in the form that I use for data entry. I am not sure how to write the where clause for the filter argument of the openform method. I have a number of different things and most recently tried to set the QueryDef SQL string to a variable and use that for the filter argument, but no joy. Any help on this would be greatly appreciated. Here is a the code I am using (shortened) to help illustrate my problem.


'''''''
Option Compare Database
Option Explicit

Private Sub cmdRunQuery_Click()
Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant
Dim strSQL As String

Set db = CurrentDb()
' Delete the existing dynamic query; trap the error if the query
' does not exist.
On Error Resume Next
db.QueryDefs.Delete ("Dynamic_Query")
On Error GoTo 0

where = Null

'Report Type
If left(Me![txtReportType], 1) = "*" Or right([txtReportType], 1) = "*" Then
where = where & " AND [ReportType]= '" + Me![txtReportType] + "'"
Else
where = where & " AND [ReportType]= '" + Me![txtReportType] + "'"
End If

'ReportNum field
If left(Me![txtHumintReportNumber], 1) = "*" Or right([txtHumintReportNumber], 1) = "*" Then
where = where & " AND [HumintReportNum]= '" + Me![txtHumintReportNumber] + "'"
Else
where = where & " AND [HumintReportNum]= '" + Me![txtHumintReportNumber] + "'"
End If

'Status field
If left(Me![txtStatus], 1) = "*" Or right([txtStatus], 1) = "*" Then
where = where & " AND [Status]= '" + Me![txtStatus] + "'"
Else
where = where & " AND [Status]= '" + Me![txtStatus] + "'"
End If


Set QD = db.CreateQueryDef("Dynamic_Query", _
"Select * from (tblLogos RIGHT JOIN tblHumintCases ON tblLogos.LogoID = tblHumintCases.LogoID) LEFT JOIN tblIntelAgencies ON tblHumintCases.Source = tblIntelAgencies.AgencyName " & (" where " + Mid(where, 6) & ";"))

DoCmd.OpenQuery "Dynamic_Query"
'DoCmd.OpenForm "frmHumintCasesDE", acNormal, , "frmHumintCases.RecordSource = '" & "Dynamic_Query"

'Clear fields on search form
Me.txtHumintReportNumber = Null
Me.txtReportType = Null
Me.txtStatus = Null


End Sub

 

[Mile-O]

Some quick notes:

• Use Me. and not Me!
• Use & and not + for concatenation
• Use the [CODE] [/CODE] tags when posting code.
• Keep to a standard. If you declare DAO.Database, then declare DAO.QueryDef
• Take off the OnError statements so that you can see any errors
• MsgBox the entire SQL statement to see what it looks like when built
• "frmHumintCases.RecordSource " don't know what that's meant to be
• if you are going to use where for holding text then declare it as a String and not a Variant.

 

[NachoMan]

A Few Questions

Thanks for the reply, Mile-O-Phile. A few questions though.

1. Why can't I use the bang (!) operator instead of the dot (.)since I am referencing form controls and not properties/methods?

2. I would have thought you would use the & symbol too for the concatenation. I just borrowed this code pretty much for a MS KB article, so I didn't change it. It works well enough with the + symbol.

3. Sorry about not using the code tags.

4. Thanks for the insight about the consistency with the declarations, and the msgbox idea is very helpful.

5. The "frmHumintCases.RecordSource" thing was just the latest desperate attempt to get this thing to ouput to my data entry form.

which leads me to my last question..

How do I use the SQL statement that builds my query to create a where clause on the openform statement for my data entry form?

This is my big problem. I didn't know if I should open the data entry form and change the recordsource to my newly created query or if I should create a where clause somehow. I assume the right strategy is to create the where clause based on the SQL statement, but I do not know how to do this correctly. Any help would be great. Thanks again.

-Lou

 

[Mile-O]

Re: A Few Questions

1. Why can't I use the bang (!) operator instead of the dot (.)since I am referencing form controls and not properties/methods?

Visit Me! or Me. - for an explanation.

2. I would have thought you would use the & symbol too for the concatenation. I just borrowed this code pretty much for a MS KB article, so I didn't change it. It works well enough with the + symbol.

Yes, it will still work. It's just that + is a mathematical operator. Text can only be concatenated so you can't, obviously, add "a" + "b". It's a question of best practice.

3. Sorry about not using the code tags.

Grrr!

4. Thanks for the insight about the consistency with the declarations, and the msgbox idea is very helpful.

5. The "frmHumintCases.RecordSource" thing was just the latest desperate attempt to get this thing to ouput to my data entry form.

You can't do this the way you are trying. The WHERE condition you are trying to apply on the DoCmd.OpenForm line applies to a field and it's value: i.e. [MyField] = " & intValue

You'd be needing to do something like this:

If Forms("frmHumintCases").RecordSource = '" & "Dynamic_Query" Then
    DoCmd.OpenForm "frmHumintCasesDE"
End If

How do I use the SQL statement that builds my query to create a where clause on the openform statement for my data entry form?

On this issue, you are confused, misguided, etc.

See my answer to 5. regarding that question.

 

[NachoMan]

Got it!

Thanks again for the quick response, Mile-O-Phile. I just figured out that this thing will work if I just use my new created query
(Dynamic_Query) as the filtername argument of the openform method.

i.e.

DoCmd.OpenForm "frmMyDataEntryForm", acNormal, "Dynamic_Query", , acFormReadOnly, acDialog

I don't know why I was thinking about resetting the forms recordsource. Using the filter was a lot easier.

Thanks again for your help. On to the next headache!