1 parameter controlled by multple combo boxes on a form (1 Viewer)

[shapman]

Hi

I have been trying to set up this query via VBA code but, with no luck, have gone back to the query in design view. Basically the parameters of the query are determined by combo boxes in a form. This is fine if one combo box indicates one criteria but a problem arisis when multiple combo boxes are possible parameters for one field.

i.e. Field 1 = combo box one
field 2= combo box two
field three = combo box three, four or five.

i have found it difficult running this, especially in terms of a returning all if a combo is left blank. the problem with setting this up on field three would be that if i run combo box four and five to return all if left blank it overides the other command to set a specific parameter. Also, for some reason i can tdecide whether this is an AND or OR command, its actually both i.e. Field three parameter = combo box three and/or combo box4 etc...

please help this is driving me crazy.

Thanks

Shapman

 

[ajetrumpet]

Question:

Your situation: If ANY or ALL of combos 3, 4 or 5 are left blank, then return all records. Otherwise, use all 5 combos as criteria for the search...CORRECT??

 

[shapman]

Question:

Your situation: If ANY or ALL of combos 3, 4 or 5 are left blank, then return all records. Otherwise, use all 5 combos as criteria for the search...CORRECT??

not quite...
if the combos are blank then they do not count towards the results, if they are all left blank then they return all records..
any ideas?

 

[ajetrumpet]

not quite...
if the combos are blank then they do not count towards the results, if they are all left blank then they return all records..
any ideas?

Yes, as I currently have database forms that do this. If I am understanding you correctly, there are only TWO conditions here...

1) ANY or a COMBINATION of the combos are blank.
2) ALL combos are blank.

If that is your situation, type this in a query for a search button....

WHERE (([field1] = Forms![formname]![combo1] OR Forms![formname]![combo1] Is Null)

AND ([field2] = Forms![formname]![combo2] OR Forms![formname]![combo2] Is Null)

AND ([field3] = Forms![formname]![combo3] OR Forms![formname]![combo3] Is Null),

etc, etc...

 

[shapman]

thanks for this. is this something i type into the criteria of the query or in code somewhere on the form?

thanks again.

 

[ajetrumpet]

criteria of the query...notice the WHERE in the code window above (I thought that would have given it away)...

 

[shapman]

criteria of the query...notice the WHERE in the code window above (I thought that would have given it away)...

by [field] do you mean what i type in different fields of the criteria. I tried typing in one field all that you noted apart from the word 'field' as i did not know how it was applicable. im not sure my brain is working properly today.

 

[ajetrumpet]

by [field] do you mean what i type in different fields of the criteria.

No, I don't. Combo boxes have Rowsources. Rowsources are based off of fields from tables. [field] in my code refers to the FIELD of the table in which the Combo's ROWSOURCE pulls values. Does that clear things up a bit??

im not sure my brain is working properly today.

I'm not sure it is either...

 

[shapman]

No, I don't. Combo boxes have Rowsources. Rowsources are based off of fields from tables. [field] in my code refers to the FIELD of the table in which the Combo's ROWSOURCE pulls values. Does that clear things up a bit?? I'm not sure it is either...

The lack of brain power seems to have continued until this morning. how comes I have to quote the original table the combos come from, surely using there contents as the source of the criteria of a query that queries the original table anyway would be enough.

my god im confused

kind regards

Simon

 

[ajetrumpet]

how comes I have to quote the original table the combos come from

combos don't come from tables, they are stand alone entities built into the program...you can look at them as HOLDING CELLS for data. Remeber too, that every object (entity) in Access is nothing more than a HOUSE for your data. Some are temporary (e.g. forms), while others are permanent (e.g. tables)

surely using there contents as the source of the criteria of a query that queries the original table anyway would be enough.

I'm not sure what you mean by this, but it sounds like you might be thinking too much into it. I would suggest just memorizing the routine of creating criteria if you're not sure that you understand the process. I don't understand much about Accounting, and if I tried to disect it all, I would never get anything done!!

my god im confused

That is actually a good thing, because if you are confused and you're willing to stick with it until just one thing clicks, you will be back for more...and you might even start to like it too!

Lecture's over...

Have you gotten everything worked out with the Rowsource issue?? You are welcome to post your file. I might be able to offer you a bit more explanation of processes if I saw what you are doing with your database.

 

[shapman]

combos don't come from tables, they are stand alone entities built into the program...you can look at them as HOLDING CELLS for data. Remeber too, that every object (entity) in Access is nothing more than a HOUSE for your data. Some are temporary (e.g. forms), while others are permanent (e.g. tables)I'm not sure what you mean by this, but it sounds like you might be thinking too much into it. I would suggest just memorizing the routine of creating criteria if you're not sure that you understand the process. I don't understand much about Accounting, and if I tried to disect it all, I would never get anything done!! That is actually a good thing, because if you are confused and you're willing to stick with it until just one thing clicks, you will be back for more...and you might even start to like it too!

Lecture's over...

Have you gotten everything worked out with the Rowsource issue?? You are welcome to post your file. I might be able to offer you a bit more explanation of processes if I saw what you are doing with your database.

ok, i am loosing the will to live with this one. i still cant get this working, i can set up the query for one publication but not for multiple selection. i have attached the database, as you can see frmemail has five possible publications, i cant get this to work. the user needs to be able to search for up to five publications. I have had to take some forms and many details out of the database but the essesne of the structure is there. there are also unused tables and macros whcih relate to other features i have had to takee out.

thanks in advance

 

[shapman]

ok.. attachment is there this time

 

[EMP]

Still not there.

You may need to compact the database before zipping to make it smaller.

^

 

[ajetrumpet]

Shapman,

I don' see it either. The max file size for ZIPs is 393K (that's what this says), which I believe is about 3,000K or maybe a little lower for actual file sizes. Check the size of either to see if you're over the limit.

 

[shapman]

trying again...

 

[ajetrumpet]

Shapman,

Take a look at the ZIP file.

The user can search for publications in ANY of the five combo boxes and the query will return all records that satisfy each combo box. Not sure if this is what you want...but it is flexible.

I dropped the GROUP BY clause, that was not needed, unless you prefer it.

Also, I didn't see any use for the "LIKE "*"" criteria section you had in there for most combo boxes. LIKE is not needed, as all of your combo boxes (except for 1) pull their values directly from table fields.

You also might want to consider cascading the Status and SubStatus boxes.

In the new query, the only combo box values that are mandatory are "Status" and "SubStatus".

 

[shapman]

Shapman,

Take a look at the ZIP file.

The user can search for publications in ANY of the five combo boxes and the query will return all records that satisfy each combo box. Not sure if this is what you want...but it is flexible.

I dropped the GROUP BY clause, that was not needed, unless you prefer it.

Also, I didn't see any use for the "LIKE "*"" criteria section you had in there for most combo boxes. LIKE is not needed, as all of your combo boxes (except for 1) pull their values directly from table fields.

You also might want to consider cascading the Status and SubStatus boxes.

In the new query, the only combo box values that are mandatory are "Status" and "SubStatus".

hi

thanks for this, I still can't get it to work because when the combo boxes are left blank the query takes this as a null value and returns no results(because none of the publications or status's are left blank). the like "*" is in there to help deal with this, it acts as my wild card, so it retuns all results when the combos are left blank. I just cant seem to get this working for multiple combo boxes. the Like function doesnt seem to work for multiple criterias and "*".

I have also been trying to work out how to ensure that each search will only return a record once... i.e. someone may want multiple publications but there email address is only needed once per search and at the moment it may come up several times. so i need to be able ot limit the ID field to returning only once.

do you know how i could get round this?

Thanks in advance

Shapcman

 

[ajetrumpet]

I think the tough thing here is the relay of information Shapman.

the "*" acts as my wild card, so it retuns all results when the combos are left blank.

The "*" character DOES NOT return all records when a value is null or blank. When used with the LIKE operator, it evaluates the left side or right side (depending on it's placement) of entered data to complete FULL string values(broken or unbroken - depending on the way it is entered in the criteria) in a cell that contain the string entered. See below for an example of this...

the Like function doesnt seem to work for multiple criterias and "*".

it does not work for the reasons stated above.

I have also been trying to work out how to ensure that each search will only return a record once...

That is easy to do, but not until you get the basics figured out.

i.e. someone may want multiple publications but there email address is only needed once per search and at the moment it may come up several times.

The SEARCH form DOES NOT return e-mail addresses, beacuase that field is not in the criteria section of the query. I think you mean ID instead of e-mail address.

I just need to clarify a few things, please answer these....

when the combo boxes are left blank the query takes this as a null value and returns no results

WHICH combo boxes EXACTLY??

**Which combo boxes are required (which boxes need to contain values in order for the search to work)??
**Do you want the query to return publications that are ONLY associated with the value(s) in status box(es)??

"*" Wilcard Example - Cell Value for the example = "Shapman's Wildcard Test" (NO result = NO Value Returned, RESULT = Full Cell Value Returned)

LIKE "*" & "man's" = NO Result
LIKE "*" & "man's*" = RESULT
LIKE "*" & "Test" = RESULT
LIKE "*Test" = RESULT

LIKE "*" & "ildcar" & "*" = RESULT
LIKE "*ildcard*" = RESULT

LIKE "Shap" & "*" = RESULT
LIKE "Shap*" = RESULT
LIKE "Wild" & "*" = NO Result

See the differences?? The "*" is used to evaluated the ENTIRE cell value against the criteria entered.

 

[shapman]

I think the tough thing here is the relay of information Shapman. The "*" character DOES NOT return all records when a value is null or blank. When used with the LIKE operator, it evaluates the left side or right side (depending on it's placement) of entered data to complete FULL string values(broken or unbroken - depending on the way it is entered in the criteria) in a cell that contain the string entered. See below for an example of thisit does not work for the reasons stated above.That is easy to do, but not until you get the basics figured out.The SEARCH form DOES NOT return e-mail addresses, beacuase that field is not in the criteria section of the query. I think you mean ID instead of e-mail address.

yep, i meant ID can only be returned once.

I just need to clarify a few things, please answer these....WHICH combo boxes EXACTLY??

This is combo boxes "Status", "substatus", "cbopub1", "cbopub2", "cbopub3", "cbopub4", "cbopub5" on the form "frmemail".

**Which combo boxes are required (which boxes need to contain values in order for the search to work)??

The form needs to be set up so that if the combo box has something chosen in it that value will count as part of the criteria. but if it is left blank then it will not count towards the criteria (i.e. as if the parameter is not even typed into the grid). The Like "*" was a neat way of getting around this as it basically returned the criteria back to all results - as if there was nothing type in the criteria in the first place.

**Do you want the query to return publications that are ONLY associated with the value(s) in status box(es)??

if the status boxes are filled in then yes, if not then they will not be a part of the query as noted above with my reasons for using the like "*". for example if i search for a value in pub1 and pub2 i need those two only to be in the results, pub3/4/5 need to be ignored by the query, if the status box is left blank that needs to be ignored, if the substatus is left blank that needs to be ignored. In short, if a combo box has a value in it it is counted as a criteria, if not it needs to be be as if the parameter is not even typed into the query (or ignored by the query), only filtering on values in combo boxes that are selected on the frmemail."*" Wilcard Example - Cell Value for the example = "Shapman's Wildcard Test"

CRITERIA​

]RESULT​

Thanks in advance, i hope this helps clear up the relay of information issue, Being relatively new and self taught with Access I tend to use the language which i have built in my brain to help me understand in the concepts with which I am working, rather then using the language that would be used in a reference book to help explain things

thanks again
Shapman

 

[ajetrumpet]

Shapman,

I think you need to fix something first...your tables "status" and "substatus" are NOT linked to any other tables that hold the CONTACT ID from the "tblgeneralcontactdetails", however you have the status tables JOINED with the other tables in your query. If you look at the relationship window, this JOIN is not present.

This needs to be fixed before your form will work correctly.

I noticed that in your "status" table you have the correct ID's listed in the field (from the contactdetailsID field), but the ID in "status" should be a FK, not a PK, which is what you have right now.

Bottom line...you need a relationship between the "tblstatustype" and "tblgeneralcontactdetails".