Hi. This is the first time I've posted here. I'm having a problem doing something I've done multiple times before, and can't get to work in this situation. I have no Idea why. I have pared things down to the basics for simplicity.
I have a database that contains a list of Stocks e.g. IBM, Apple etc. Each stock has a Category that it is assigned to. I have a table -tblInvAAMainCats- That contains the category name in one field and an ID number in an autonumber field. I have another table -tblEquity- that contains fields for Equity Name, ID, and Category ID.
I have a continuous form with two columns: Name and Category. I have, in the Form Header, a combo box that populates itself from the table -tblMainCats and contains a list of Categories and their ID Number (AACatID). I want to use that ComboBox to filter the form to show only stocks in the category chosen are displayed. To do that I have always put a reference to the combobox as a Criteria for the forms RecordSource. This has always worked for filtering forms and cascading queries, but this time I get nothing.
>This is the SQL for the ComboBox rowsource: (RowSource Type is Table/Query and Bound Column is 1, No Control source)
SELECT tblInvAAMainCats.AACatID, tblInvAAMainCats.MainCat
FROM tblInvAAMainCats;
>This is the SQL for the Form RecordSource when I put a hard value as criteria in the HAVING line--in this case 1. It works fine and returns only rows where The autonumber ID field (AACatID) in tblAAInvMainCats is 1
SELECT tblEquity.Company, tblInvAAMainCats.MainCat, tblInvAAMainCats.AACatID
FROM tblInvAAMainCats INNER JOIN tblEquity ON tblInvAAMainCats.AACatID = tblEquity.MainAAID
GROUP BY tblEquity.Company, tblInvAAMainCats.MainCat, tblInvAAMainCats.AACatID
HAVING (((tblInvAAMainCats.AACatID)=1));
>This is the same SQL with a reference to the ComboBox -cboMainCats. It does not work.
SELECT tblEquity.Company, tblInvAAMainCats.MainCat, tblInvAAMainCats.AACatID
FROM tblInvAAMainCats INNER JOIN tblEquity ON tblInvAAMainCats.AACatID = tblEquity.MainAAID
GROUP BY tblEquity.Company, tblInvAAMainCats.MainCat, tblInvAAMainCats.AACatID
HAVING (((tblInvAAMainCats.AACatID)=[Forms]![frmInvEquity]![cboMainCat]));
I have manually refreshed to screen and checked all the spelling and I can't figure out why this will not work.
Would greatly appreciate any help anyone may have to offer!