Hiding #Name

[Freshman]

Hi all,
Not sure how I'm going to explain this problem but I've got a form based on a crosstab query which result gives me a number of data fields which I then refer to in the form.
Depending on the resulting data of the query some fields exist and others not. I have no way of knowing which will and will not before hand.

On the Open event I set each field to the control irrespective it they exist in the query or not.

eg:

Me.Ctl01.ControlSource = "01"
Me.Ctl02.ControlSource = "03"
Me.Ctl03.ControlSource = "03"
etc

The problem is that I'm getting #Name in the fields that don't have any bound control.

I've tried all the functions I know eg: IsNull, IsEmpty, IsError etc but no trick will do.

Any idea on resolving this.

In otherwords I only want to set the control source of a particular field if the control does indeed exist.

Thanks
Pierre

 

[plog]

My only suggestion is to redo your query so that you know what column names you will be working with. For example if you wanted to cross tab this data:

Player, Score, ScoreMonth
James, 123, Feb
James, 281, Mar
Sally, 115, Feb
Sally, 99, Jan
David, 104, Jan

Like this:

Player, Jan, Feb, Mar, Apr
James, , 123, 281,
Sally, 99, 115, ,
David, 104, , ,

You could do it without a cross tab by using a series of Iff statements for each month:

SELECT Player, Sum(Iff(ScoreMonth='Jan', Score,0)) AS Jan, Sum(Iff(ScoreMonth='Feb', Score,0)) AS Feb, Sum(Iff(ScoreMonth='Mar', Score,0)) AS Mar, Sum(Iff(ScoreMonth='Apr', Score,0)) AS Apr
FROM TableNameHere
GROUP BY Player

That way all fields are accounted for and your form will find them. Of course this only works if you know what the fields will be.

 

[spikepl]

1. Do not confuse fields and controls. Controls are entities on a form /report to display information and in some cases bound to a field in a recordset (soas to show/modify the value held in that field). A field is an entity for holding data in a record set

2. See what fields you have available: http://stackoverflow.com/questions/...dset-how-can-i-view-all-the-fields-in-the-ret

 

[Freshman]

Hi - thanks for the replies

plog - you right - I do need to fix it from the query side but it is a complex qeury not designed by me so I need to figure out what is what first.

spike - I do know all the field names and they are all bound to their source but depending on the outcome of the query, some sources exist and some don't.
So running through the form to give me to fields will only give me known info - I've tried that route.
Like plog suggested I must try and get all the sources to display no matter if they have data or not.

Cheers guys
Pierre

 

[spikepl]

Again - do not confuse controls and fields. You clearly do NOT know what fields the query puts in the recordset so you'd need to find out. That would require reading the link I gave you, which you for some reason did not do. The link concerns itself with finding out what is in a recordset.