Formating text field on run of a reaport

[Blazer Saga]

Ok this is a two prater.

First I need to figure out how to change the background color of a txt field in a report based on its value when the report is viewed.

More specifically, I have a report in witch I want to the add the functionality that if a field is blank, but they should have put info in it based on other selections, it's background will be yellow. I don't want to simply alter the Forms they use to generate this data to make the fields required, because in many cases threw out the form, these fields are hidden from view based on other selections and therefore they can not enter data into them.

I have red the help documentation about the BackColor value, but I am unable to get it to change the BackFill color even with a simple

Dim BGYellow As Long

BGYellow = RGB (255, 255, 0)

Me!Cmb_Font_Style.BackColor = BGYellow

I am also unsure witch function in a report I should place this under so it checks the data when it opens up. I am assuming the report Open() call is the correct one.

Now for the second part.

In the forms they fill out there are many Combo Boxes, these are linked to tables via a Number (Form side) - Autonumber (Table with the combo data) arrangement. So the value actually stored in the form I am generating this report from are numbers. I need to display the text stored in another filed in that table inside a text box on the report.

At first I tried making a query and pointing the text box to that, but I had no luck. So I am wondering how I should accomplish this if its even possible. Do I need to use some form of a query (If so is there some way to call it from code rather then make separate queries under the queries tab so I can keep the database looking nice) or is there another way?

 

[Moniker]

For the report, you use OnFormat. The OnOpen event sets global report settings like record source, filters, sorting, etc. OnFormat does the report detail that you see.

For the second part, the simplest answer is a DLookup, like this:

CustomerName = DLookup("[CustomerName]","Name_Of_Customer_Table","[CustomerID]=" & Value_From_ComboBox)

 

[Blazer Saga]

Thank you Moniker. What you suggested with the Dlookup seems like it will work fine, but I am having trouble grabbing the vale on the last parameter.

Following you suggestion.

Txt_Jersey_Type = DLookup "[Jersey Name]","Jerseys","[Jersey ID]=" & [Jersey Type])

Gives Error 2645:
Can’t find field [Jersey Type]

I also tried:
Txt_Jersey_Type = DLookup "[Jersey Name]","Jerseys","[Jersey ID]=" & [Forms]![Rpt Art Sheets]![Jersey Type])

And got Error 2540:
Can't find form [Rpt Art Sheets]

[Rpt Art Sheets] is the report I am designing, inside of with Ill place some sub reports with information based off some Sub forms of the form the report is pulling from.

Do I need to use something other then [Forms] to reference [Rpt Art Sheets], using me.[Jersey Type] has the same Error 2645 when I tried that, or am I approaching pointing to the value from the Combo Box the wrong way?

The Combo Box in the main for controls the value in [Jersey Type] on the [Art Sheet] Table. The record source for report is the [Art Sheet] Table, and the value of [Jersey Type] is a Number (Long Int).

Also for anyone else reading threw this. What is the proper way, if there is any at all, to set the BackColor of a text field? (Refer to first pos on this thread.) I still can't seem to get it to change the color using the method described in the online help.

 

[Blazer Saga]

Ok, I have figured out that the cause of the errors, at least as far as I can tell, is that [Jersey Type], while part of the record source, does not physically appear in the report. I have place it in the report and set is visible value to false for the time being, but is there a better way to do this?
For instance putting something in on the code side so that I don't have to place the field in the report just to have this work and then hide it since it is not really needed.