Dynamic query form

[scotent]

I have the following code attached to a command button, it takes its input from four drop down boxes. What I would like to do is:

a) allow for a null value i.e. if nothing is selected in the dropdown

and b) have the resultant recordset appear in the main body of the form as a datasheet view.

The dropdowns and command button are positioned in the head section of the form.

Private Sub cmdRunQuery_Click()

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

Set db = CurrentDb()

'Delete existing dynamic query; trap the error if the query does not exist

On Error Resume Next
db.QueryDefs.Delete ("Dynamic_Query&quot
On Error GoTo 0

where = Null

where = where & " AND [LECID]= '" + Me![LECName] + "'"
where = where & " AND [BusinessSector] = '" + Me![Sector] + "'"
where = where & " AND [Turnover] = '" + Me![Turnover] + "'"
where = where & " AND [NumberOfEmployees] = '" + Me![Employees] + "'"

Set QD = db.CreateQueryDef("Dynamic_Query", "select * from CompanyContacts " & (" where " + Mid(where, 6) & ";&quot)
DoCmd.OpenQuery ("Dynamic_Query&quot

End Sub

TIA

 

[Frink]

Hallo,

The way I do this sort of thing is to create a routine in the form module:

'Note: function so that it can be called from event
Public Function SetFormRecordsource()

  dim strRecordSource as string

  strRecordSource = ""
  if len("" & Me![LECName]) > 0 then strRecordSource =strRecordSource  & " AND [LECID]= '" & Me![LECName] & "'"
  if len("" & Me![Sector]) > 0 then strRecordSource =strRecordSource  & " AND [BusinessSector]= '" & Me![Sector] & "'"
  if len("" & Me![Turnover]) > 0 then strRecordSource =strRecordSource  & " AND [Turnover]= '" & Me![Turnover] & "'"
  if len("" & Me![Employees]) > 0 then strRecordSource =strRecordSource  & " AND [NumberOfEmployees]= '" & Me![Employees] & "'"

  if len(strRecordSource)>0 then strRecordSource = " WHERE " & mid$(strRecordSource,6)

  Me.Recordsource = "SELECT * FROM CompanyContacts" & strRecordSource

End Sub

Then put =SetFormRecordsource() as the AfterUpdate property for each drop down box.

Also, if I may be so bold, you'll find it easier if you give field and their controls similar, or identical names wherever possible.

- Frink