Please help.
If I am to do what I think I need to do, i'll be here for years!!!
I have 8 fields which I need to give the user the option to search on all or some or any of the combo boxes. All the combo boxes are pre-populated obviously so data entegrity is not an issue.
I have googled and found that doing it in SQL is probably the best option, but mathematically, 8 x 8 x 8 permutations = 512 entries
How on earth do you start to do this. Surely there's a more efficient way to do it? Unfortunately, I'm new to access and programming AND SQL so it's a job and a half. Here's some code that I've started for 3 fields.
SELECT MainJobform.[Job Reference no], MainJobform.[Date of request], MainJobform.[Requested by], IsNull([forms]![fAdvancedSearch]![comboReqBy]) AS Expr1, MainJobform.Room, IsNull([forms]![fAdvancedSearch]![comboRoomName]) AS Expr2, MainJobform.Area, MainJobform.[Job category], MainJobform.[Priority rating], MainJobform.[Estimated time for job], MainJobform.[Estimated cost for job], MainJobform.[Description of request], MainJobform.[Job status], MainJobform.[Job allocated to], MainJobform.[Actual Time for job], MainJobform.[Actual cost for job], MainJobform.[Site Managers comments], MainJobform.[Date Completed], IIf(IsNull(MainJobform![Date Completed]),"N/A",DateDiff("d",MainJobform![Date of request],MainJobform![Date Completed])) AS [Days taken], IIf(IsNull(MainJobform![Date Completed]),DateDiff("d",MainJobform![Date of request],Now()),DateDiff("d",MainJobform![Date of request],MainJobform![Date Completed])) AS [Days since request], MainJobform.[Special Instructions], MainJobform.[Contractor type], MainJobform.[Contractors name], MainJobform.[Overall status]
FROM MainJobform
WHERE (((MainJobform.[Requested by])=[Forms]![fAdvancedSearch]![comboReqBy]) AND ((MainJobform.Room)=[Forms]![fAdvancedSearch]![comboRoomName])) AND ((MainJobform.[Job Category])=[Forms]![fAdvancedSearch]![comboJobCat])
OR (((IsNull([forms]![fAdvancedSearch]![comboReqBy]))<>False) AND ((MainJobform.Room)=[Forms]![fAdvancedSearch]![comboRoomName])) AND ((MainJobform.[Job Category])=[Forms]![fAdvancedSearch]![comboJobCat])
OR (((MainJobform.[Requested by])=[Forms]![fAdvancedSearch]![comboReqBy]) AND ((IsNull([forms]![fAdvancedSearch]![comboRoomName]))<>False)) AND ((MainJobform.[Job Category])=[Forms]![fAdvancedSearch]![comboJobCat]) OR (((IsNull([forms]![fAdvancedSearch]![comboReqBy]))<>False) AND ((IsNull([forms]![fAdvancedSearch]![comboRoomName]))<>False)) AND ((MainJobform.[Job Category])=[Forms]![fAdvancedSearch]![comboJobCat]) OR (((isnull([Forms]![fAdvancedSearch]![comboReqBy]))<>False) AND (((isnull([Forms]![fAdvancedSearch]![comboRoomName]))<>False)) AND (((isnull([Forms]![fAdvancedSearch]![comboJobCat]))<>False))) OR (((MainJobform.[Requested by])=[Forms]![fAdvancedSearch]![comboReqBy]) AND ((isnull([Forms]![fAdvancedSearch]![comboRoomName]))<>False) AND (((isnull([Forms]![fAdvancedSearch]![comboJobCat]))<>False)) OR (((MainJobform.[Requested by])=[Forms]![fAdvancedSearch]![comboReqBy]) AND ((MainJobform.[Room])=[Forms]![fAdvancedSearch]![comboRoomName])) AND (((isnull([Forms]![fAdvancedSearch]![comboJobCat])<>False)))) OR (((MainJobform.[Requested by])=[Forms]![fAdvancedSearch]![comboReqBy]) AND (((isnull([Forms]![fAdvancedSearch]![comboRoomName]))<>False) AND ((MainJobform.[Job Category])=[Forms]![fAdvancedSearch]![comboJobCat])))
THanks in wild anticipation!!!
Graeme
4 1569
I'm a little unsure of what you're looking for. Are you saying you have 8 fields that are bound to 8 combo boxes? And you need to search these comboboxes?
Or are you saying you need to search records in the table that match the 8 comboboxes?
If I am on target I think I have recently dealt with a similar issue that got a lot more in depth and took a ride of it's own.
For searching records I usually use an unbound control in the forms header then using VBA filter the records. This allows me to choose the field or fields I want to search by then chose the records I want. The code provided allows me to use many (usually 6 fields) in the header and offers them 3 pieces of criteria. I have also designed a subform for this purpose that allows many more, I generally use that for reports and such.
I don't know how capable you are in any of these areas but I will be glad to try and help. If this is to complex maybe someone can show you a simpler way.
First create a query with the fields you want to include in any of your searchs then add a combo box to the header set the rowsourcetype to - Field List then add that query to the rowsource next you add a combo box to filter the records according to the field they selected.
I usually name the controls for this FilterBy0,FilterBy1,FilterBy2 etc.
then the searchby0,searchby1,searchby2.
The first combobox I use an afterupdate event like so -
Private Sub FIRSTCOMBOBOX_AfterUpdate()
-
If IsNull(Me.ActiveControl) Or Trim(Me.ActiveControl) = "" Then Exit Sub
-
MySearch Me, Me.ActiveControl, "SECONDCOMBOBOXNAME"
-
End Sub
-
-
Public Function MySearch(frm As Form, FList As String, SrchBx As String)
-
On Error GoTo Err_MySearch
-
Dim QDef As QueryDef
-
Dim myDB As Database
-
Dim myField As Field
-
Dim ctl As Control
-
Set ctl = frm(SrchBx)
-
Set myDB = CurrentDb
-
-
Set QDef = myDB.QueryDefs("YOURQUERYNAME")
-
Set myField = QDef.Fields(FList)
-
myField.SourceField
-
frm(SrchBx) = ""
-
frm(SrchBx).RowSource = "Select " & myField.SourceField & " From " & myField.SourceTable & " Group By " & myField.SourceField
-
-
'Me!Search0.RowSource = "Select " & myField.SourceField & " , Max(Distributions.RevVal) AS MaxOfRevVal From " & myField.SourceTable & " Group By " & myField.SourceField
-
frm(SrchBx).Requery
-
-
Set myField = Nothing
-
Set myDB = Nothing
-
Set QDef = Nothing
-
Exit sub
-
Err_MySearch
-
Msgbx err.number & " " & err.description
-
End Function
-
The second combobox would filter the results. if you can get this far I will be glad to add the code I use for this also.
I'm a little unsure of what you're looking for. Are you saying you have 8 fields that are bound to 8 combo boxes? And you need to search these comboboxes?
Or are you saying you need to search records in the table that match the 8 comboboxes?
Hi,
I have 8 fields in a table and the user needs to search/filter from an unbound form using combo boxes on all the 8 fields (using all 8 comboboxes) or none (giving all records) and also some of the 8 comboboxes, but not all.
Does that make sense?
Cheers, Graeme
If I am on target I think I have recently dealt with a similar issue that got a lot more in depth and took a ride of it's own.
For searching records I usually use an unbound control in the forms header then using VBA filter the records. This allows me to choose the field or fields I want to search by then chose the records I want. The code provided allows me to use many (usually 6 fields) in the header and offers them 3 pieces of criteria. I have also designed a subform for this purpose that allows many more, I generally use that for reports and such.
I don't know how capable you are in any of these areas but I will be glad to try and help. If this is to complex maybe someone can show you a simpler way.
First create a query with the fields you want to include in any of your searchs then add a combo box to the header set the rowsourcetype to - Field List then add that query to the rowsource next you add a combo box to filter the records according to the field they selected.
I usually name the controls for this FilterBy0,FilterBy1,FilterBy2 etc.
then the searchby0,searchby1,searchby2.
The first combobox I use an afterupdate event like so -
Private Sub FIRSTCOMBOBOX_AfterUpdate()
-
If IsNull(Me.ActiveControl) Or Trim(Me.ActiveControl) = "" Then Exit Sub
-
MySearch Me, Me.ActiveControl, "SECONDCOMBOBOXNAME"
-
End Sub
-
-
Public Function MySearch(frm As Form, FList As String, SrchBx As String)
-
On Error GoTo Err_MySearch
-
Dim QDef As QueryDef
-
Dim myDB As Database
-
Dim myField As Field
-
Dim ctl As Control
-
Set ctl = frm(SrchBx)
-
Set myDB = CurrentDb
-
-
Set QDef = myDB.QueryDefs("YOURQUERYNAME")
-
Set myField = QDef.Fields(FList)
-
myField.SourceField
-
frm(SrchBx) = ""
-
frm(SrchBx).RowSource = "Select " & myField.SourceField & " From " & myField.SourceTable & " Group By " & myField.SourceField
-
-
'Me!Search0.RowSource = "Select " & myField.SourceField & " , Max(Distributions.RevVal) AS MaxOfRevVal From " & myField.SourceTable & " Group By " & myField.SourceField
-
frm(SrchBx).Requery
-
-
Set myField = Nothing
-
Set myDB = Nothing
-
Set QDef = Nothing
-
Exit sub
-
Err_MySearch
-
Msgbx err.number & " " & err.description
-
End Function
-
The second combobox would filter the results. if you can get this far I will be glad to add the code I use for this also.
Thanks very much. I'll have a look at this today!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: TH |
last post by:
I am (still :) working on a recipe database. Now I am trying to figure out
how to set it up for an ingredient search. What I want it to be able to do
is three things:
1. Search based on just...
|
by: ShyGuy |
last post by:
I have a table with 4 fields. Three are used for criteria.
I can get the DLookup to work with 1 criteria with the following but
can't get it to work with 2 or three.
NumofAppts = DLookup("",...
|
by: starace |
last post by:
I have designed a form that has 5 different list boxes where the
selections within each are used as criteria in building a dynamic
query. Some boxes are set for multiple selections but these list...
|
by: carl.barrett |
last post by:
Hi,
I have a list box that displays 2 columns. Behind it sits a query with
five columns. These are Column1 (DOB), column2 (a concatenated string
of Surname Forname, Title), Column3 (Surname),...
|
by: NOSPAMrclark |
last post by:
I'm wondering . . . .
What is the generally accepted method for calling reports from multiple
locations?
Example:
REPORT-A is called by selecting a record in a listbox and clicking a...
|
by: Greg Strong |
last post by:
Hello All,
In the past I've used a combo box with the 'row source' being an Access SQL
union query to select "All" or 1 for only 1 criteria in a query. An example
is as follows:
SELECT 0 As...
|
by: Ceebaby via AccessMonster.com |
last post by:
Hi All
Here's hoping someone can help me with this.
I have a report based on a query where the criteria for 4 of the fields is
set from an unbound form. I want the user to be able to select any...
|
by: dlevene |
last post by:
Hi - newbie here, be gentle. In Access 2003, I've created a report (based on a query) to produce mailing labels for all records where = "Community". is a combo-box field with the values coming from...
|
by: jvan2008 |
last post by:
"Form1"
combobox "cboModel"
Row Source
SELECT ., . FROM tblModel ORDER BY ;
combobox "cboContactName"
SELECT . FROM Query1 ORDER BY ;
|
by: kstevens |
last post by:
Please help. I know the sysntax is wrong. Here are some details. I am looking for the sum of past shipped items. I originally wrote this Dsum with only one criteria, but because of multiple...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |