Unbound combo's on form to filter report

[fraser_lindsay]

I setup a form with unbound filters on (as per MS example) to open a report in full page and then filter the results.

I have set my rowsource details for the relevant combo boxes and bound to column 1 in properties. When I load the query for that combo and run it it reports the list of combo items as text. When I load the form it only shows them as numerical values.

I have the same problem with some report fields and I can't see what I'm missing.

I know I can use 'controlsource.column(1)' on a form normally to remedy this but that doesn't seem to work just now.

 

[stopher]

I've give an example to ensure we are talking about the same thing here. Suppose your combo is to select a user from the following table/query:

UserID   Name
1        John
2        Bill
3        Andy
4        Jim

So you want to select by name in the combo but you want the combo to store the UserID (not the name).

The way to make this happen is to:
- set the column count to 2
- set column widths to 0;2 (cm or an appropriate width)
- set the bound column to 1

thus the bound column is hidden because the width is zero

hth
Chris

 

[fraser_lindsay]

Yes, I need the combo to display the text column, not the ID column. 3 our of 4 display the ID column but when I set column count to 2 for these and set the widths I then get nothing showing in the combo.

I am definitely bound to column 1 on them all, as it should be.

I just can't make sense of it, especially as the underlying query shows the data correctly.

I must have a problem with my underlying relationships or something. I can only filter on the ones that display the correct text value, the others show the equivalent numerical ID value but when you try and select them to filter the report I get the data type mismatch error.

Back to the drawing board...

 

[fraser_lindsay]

I still haven't cracked this. I thought I had followed this perfectly and have just tried setting it up again with the same problem.

http://support.microsoft.com/default.aspx?scid=kb;en-us;208529&Product=acc

I have setup an unbound form with unbound combos.
I have setup a query which joins two tables with the information I need.
I have created a report based on the same query.
The query display the information as expected.

I have set the row source to the fields I want to filter with from the query on my combo's.

I have made sure the tag names are the same as the field names.

Each combo has a name 'Filter1', 'Filter2' through to 5.


However, when I load the unbound form only one out of five combo boxes shows the text name, in this case 'surname'. The other four refuse to show the text I want, only ID numbers.

I've tried changing column count to 2 and setting widths to '0;2' but this doesn't work. It just shows a blank combo. Bound column is set to 1.


If I select one of the numerical values and try to filter with it I get the data type mismatch error.



I don't know what else to try or troubleshoot, can anyone help?


At the moment all I can do is open the form, which opens the report and filter by surname. The other filter options will not work.

 

[jal]

Could this be a bug?

"Unfortunately, Service Pack 3 introduced a range of new Access bugs:
Combo boxes go blank if their RowSource is a table field that has something in its Format property."

http://allenbrowne.com/bug-Access2003SP3.html

 

[jal]

Can you upload the database? (I can only view MDB format, though).

 

[jal]

I have set the row source to the fields I want to filter with from the query on my combo's.

Just to be clear, you did the following:

cbo.RowSource = "SELECT FirstName, LastName FROM qryCustomers"

 

[fraser_lindsay]

Jal,

I have five fields. The one that works is 'Surname'. I use this on the rowsource for that combo:

SELECT DISTINCT qryEmployeeDetails2.Surname FROM qryEmployeeDetails2 ORDER BY qryEmployeeDetails2.Surname;


Then this one (BusinessName) that doesn't work on the form:

SELECT DISTINCT qryEmployeeDetails2.BusinessName FROM qryEmployeeDetails2 ORDER BY qryEmployeeDetails2.BusinessName;


I'm just trying to chop the DB down a bit so I can upload it....

 

[fraser_lindsay]

ok, here we go...

the offending forms are 'frmFilter' and 'frmFilterJSA'

 

[jal]

I don't have much time.

So far I opened JSa frm and it asked me for a parameter value. So I tried commenting out this line of code "DoCmd.OpenReport..." and then it let me see the form with the comboboxes. All of them had some data (well, most of them had numbers). The last one oddly had the first row blank (and then the numbers 1, 2, 3).

 

[jal]

I feel your pain - the last cbo should have Hazard names but instead it only has numbers. Hmm...

 

[jal]

In your table DesignView, I noticed that tblHazard or tblHazards(s) had a column called Hazard_Name defined as Numeric rather than Text. I'm guessing that's the problem? I think this would explain why you are getting numbers in the CBO instead of text.

 

[jal]

The same problem in tblJSA - you have a column called "BusinessName" defined as numeric.

 

[fraser_lindsay]

ah ok. They are numeric as they are lookup's from other tables. I maintain a table of 'hazards' and a table of 'BusinessName' separately which are used in the main table.

Thanks for checking it out. Shall I just edit my underlying query so it references the individual tables for Hazard, BusinessName etc?

EDIT - just tried that and it doesn't work

 

[jal]

ah ok. They are numeric as they are lookup's from other tables. I maintain a table of 'hazards' and a table of 'BusinessName' separately which are used in the main table.

Thanks for checking it out. Shall I just edit my underlying query so it references the individual tables for Hazard, BusinessName etc?

EDIT - just tried that and it doesn't work

Apparently, I am totally ignorant of what a "lookup" is or how it works. I was operating under the assumption that a column defined as "numeric" should never contain text. Frankly I don't get it. I'll have to do some research on this when I find the time. I hope you find a solution.

 

[fraser_lindsay]

Jal, thanks for spending time on it. You are correct, I don't believe you are supposed to put text in a field designated as numeric.

However, when you use the lookup wizard in table design it automatically defines the field as numeric after you finish it, but displays the values (text or otherwise) from your table you are looking up.

That's about as far as my understanding goes.

 

[fraser_lindsay]

Cracked it!

Right, it would appear that I did need to modify the underlying query some more. I added the individual tables that are used as lookup sources for the master table i.e one for location, one department etc

I made sure that the sources on the query fields reflected the indiviudal tables and it now works!