Multiple criteria query does not work

[Archie999]

Hi All,

This seems like a really simple thing and that's why I am pulling my hair out because I cannot get it to work.

I have a simple form with 4 comboboxes, 3 of them are cascading. All 4 boxes are bound to the same table - no join. And in fact, these are the only 4 fields in the table.

What I want to do is have the user enter values in the 3 cascading comboboxes and then have the 4th box populated with the unique ID, based on a query of the other 3 boxes. The 3 values will always be collectively unique and should return only one unique ID #. Here is my query:

SELECT tblIssue.MyIssueID
FROM tblIssue
WHERE (((tblIssue.ProductID)=[Forms]![frmNewIssue]![ProductID]) AND ((tblIssue.CategoryID)=[Forms]![frmNewIssue]![CategoryID]) AND ((tblIssue.IssueName)=[Forms]![frmNewIssue]![cmboIssueName]));

My query always returns 0 values although I know it should return one value. The field IssueName is a text field and the others are integers, if that makes any difference (I have played with the parameters to no avail).

If anyone spots anything wrong or has any suggestions it would be much appreciated! Thanks in advance.

Arch

 

[WayneRyan]

Arch,

Your SQL looks OK, I take it that you pasted it in. Is it possible
to compact, ZIP and post your db?

Wayne

 

[Archie999]

Hi Wayne,

Yeah, I pasted it in. I am attaching part of my Db (whole thing was too big) so you can take a look.

The form in question is frmNewIssue. There is not a huge amount of data in the tables so for testing you can use these values:

Product: Horizon Rad Station
Category: General
Issue Name: Select any

There is something weird that you can also take a look at and see what you think.

- Open the frmIssues-temp form . It's been imported from a previous version of my DB so just cancel all the prompts for values of non-existant controls. Then in the first part of the form enter the same values I mentioned above into the comboboxes.

Hmmm - look what pops into the IssueID field. The correct issue ID. Now look at the SQL. It does not even make sense (issueID should never = IssueName).

Anyway, I could not help but point that out cuz it's so strange. BTW - yes I tried copying that SQL over to my current form and modifying accordingly and it did not work??

Thanks for taking a look and hopefully clearing the fuzz from by brain!

Arch

 

[WayneRyan]

Arch,

Sorry, but EVERY time I run it, after I put an entry (any entry)
in the Product combo, Access dies and wants to send an error
report. It does not have anything to do with you AfterUpdate
event, I think that it is just an Access quirk.

I tried to Compact/Repair, but no effect. Its too late for me
tonight. I'll check in on it in the morning, and if noone has
posted, I'll try to import into a new db, or download a new
copy.

Has this ever happened when you ran it?

Wayne

 

[Archie999]

i Wayne,

No, I've never got errors. Just the the bizarro query problem. I saved it as 2000 file format. I could save it differently if you think that will help (I'm running 2002). I could also email directly the whole DB.

Thanks for the effort. Let me know if you get it going.

Thanks /Joel

 

[namliam]

I found your problem!

In your query you are refering to
[Forms]![frmNewIssue]![cmbIssueName]
whilst in the form it is:
cmboIssueName

Thus your problem!

It originates from NOT having a naming convention in place! Try for instance having all combo boxes either cmbo or cmb and the followed by a name .

You have a naming convention for forms tables and queries and stuff. So you know about it. Use it and stick to it !

Regards

The Mailman

 

[Archie999]

Thanks

Thanks mailman, your are certainly right about the naming convention. This is my first DB and I sure wich I'd started it right. Now it will take some work to standardize it.

Thanks to you too Wayne.

Arch