form and query

[maxmangion]

Hi,

I am creating a search form which is going to have about 4 combo boxes, which will be populated from lookup tables. The "enabled" default value of these combos are going to be set to false, and at any one time, only 1 combo box can be enabled. This form will return records from a query so in the query design view under a field called series i am placing the following criteria [Forms]![frmSearch]![cboSearchSeries] then on another field but in the "Or" critieria (i.e. not in the same line with the series criteria), i am putting [Forms]![frmSearch]![cboSearchFormat] and so on.

Now since i have about 4 combos (and considering that only one value from one combo can be passed at any one time), is this a correct approach in this scenario, or is there a better way of doing this ?

Thank you

 

[Rabbie]

If you double post by mistake you can delete the post.

 

[Reyno]

How do you plan on displaying your results? In a list box?

If so, add a "Search" command button next to your combo boxes and construct your SQL statement after all the combo boxes selections have been made.

Example:

Private Sub cmdSearch_Click()
dim strSQL as String

strSQL = "SELECT tblTable.* FROM tblTable WHERE tblTable.field1 = " & _
cmbCombo1.value & " AND tblTable.field2 = " & cmbCombo2.value & _
";"

lstSearchResults.RowSource = strSQL
lstSearchResults.Requery

End Sub

Replace:
"tblTable" with the name of your table
"field(number)" with the field name in your table
"cmbCombo(number)" with the corresponding combo box
"lstSearchResults" with the name of your results list box

Hope that helps

 

[maxmangion]

Hi,

Well i am planning to show the returned records in a subform. The following is an example which may make more sense than my original question:

let's say i have the following table:

DocumentID, DocumentName, Publisher, Series, Format, DocumentType,etc

1, Book1, Publisher1, series1, pdf, book
2, Book2, publisher2, series2, pdf, tutorial
3, Book3, publisher2, series1, chm, book
etc

Now i have a query which returns the above data. eventually i want to filter the data for example, by selecting a series or a format etc.

I want to do this on a 1 combo selection and not "cascading".

Therefore in my search form,at the top i have four combos, cboPublisher, cboSeries, cboFormat, and cboDocumentType and only one of them may be enabled at one time. So, in the query design i put in the criteria field under the publisher field, [Forms]![frmSearch]![cboPublisher] ... similarly for the other, but each one under the other in the "Or" rows?

So my question is, in such a scenario is this a correct approach or there is a better way ?

Thank You

 

[Reyno]

change "AND" to "OR" in my previous example, and lstSearchResult to me.subForm1

I would think that would do it.

I'm having a slow day, if you want to post your db I'll take a crack at this for you.

 

[maxmangion]

Hi,

Thanks for your reply. My last two questions are the following:

first of all, is it considered better to build the query in your suggested approach rather than create it in the query window? Moreover, if i will change the "And" to "OR", since three of the combos will be disabled (as i want only one combo to be enabled at any one time), will that result in an error, when that sql is run ?

Thanks

 

[Reyno]

I try not to use the query window when I can write the SQL...habit from working on a shared database; users will mess with queries, but you can protect your code (and I know, there are things that you can do to protect access to your queries too, but objects can be exported and imported into databases).

if you use a command button to execute the search then the SQL won't execute until all the parameters are selected.

Another thing you can do is to build the SQL string based on the selections of the combo boxes and allow for some to be omitted...like this:

if cbo1.value <> "All" then
strFilter = strFilter & " OR tblTable.field1 = " & cbo1.value
end if

(repeat for all of your combo boxes)

strSQL = "SELECT tblTable.* FROM tblTable WHERE " & strFilter & ";"

me.subform1.rowsource = strsql
me.subform1.requery