Pass label colors from form to report

[jw5107]

How can I pass the forecolor of a label on a form to a label on a report? For textboxes I know you can use: =Forms!SomeForm!TextboxName
Is there a way to do something similiar with forecolors of labels? The form gets label color changed based on a query, and code that matches the value in the query with the Caption of the label control.
Can you refer to a label control Caption on a form to get the label to be the same color on the report?

Need some direction!!! I have tried for hours doing searches within tek-tips and looks like nobody has tried this before??? Or am I just missing something really easy here??
Any suggestions or exammples!!
Thanks in advance!!
jw

 

[dhookom]

Do you want to "glue" your report to a form? Or, is there data, code, functions that allow you to determine the color?

I think you can use a similar solution to what you are using on your form to set the color on your report. However, we don't know specifically how you are doing this on your form.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jw5107]

dhookum,
Below is an example of code that is used to change the label forecolor on the form (the same form I want the report to mirror).
Currently the report works similar to the form, and utilizes the same table --"AllocationStatusMain" for the label colors to match. The problem with this is if another user is in the form at the same time, the report labels will be colored differently because of 2 different users viewing different parts at the same time.

Thanks for checkin' into it!!
jw

Sample code: via click event of cmdbutton --
Dim rstAlloc As New ADODB.Recordset
Dim ctrl As Control
Dim i As Integer

rstAlloc.Open "AllocationStatusMain", _
CurrentProject.Connection, adOpenKeyset, dLockOptimistic
DoCmd.Hourglass True
If rstAlloc.RecordCount = 0 Then
DisplayMessage "There is NO stock status for this part."
End If
DoCmd.Hourglass False
Me!ParkCsr.SetFocus
If rstAlloc.EOF Then
Exit Sub
Else
For Each ctrl In Me.Controls
If TypeOf ctrl Is Label Then
With rstAlloc
.MoveFirst
For i = 1 To .RecordCount
Select Case ctrl.Caption
Case Is = !Allocated
ctrl.BackStyle = 1
ctrl.ForeColor = 16711680
ctrl.FontBold = True
Case Is = !OverAllocated
ctrl.BackStyle = 1
ctrl.BackColor = 12058623
ctrl.FontBold = True
Case Is = !OnBackOrder
ctrl.BackStyle = 1
ctrl.BackColor = 8434687
ctrl.FontBold = True
Case Is = !PooledGTWYs
ctrl.BackStyle = 1
ctrl.BackColor = 11983506
ctrl.FontBold = True
End Select
.MoveNext
Next i
End With
End If
Next
End If

 

[dhookom]

Each user should have their own frontend mdb. However since the report is not opened in design view, you shouldn't have any issues with multiple users.

I really have trouble following the logic of your code. Apparently you feel you need to open a recordset and loop through it to find some values. You can do this in the On Format event of your report detail section. You can't set focus to a control. I don't think you want a message box in report code like this.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jw5107]

If you know of a thread or place where I can learn more about FE's & BE's over a network/server AND how to set it up with multiple users... I'm on it!!

The form is basically a map of the world. With airport identifiers(labels) placed all over it accordingly. Each of the labels captions are named by airport ID - Like: CGN,JFK,LAX,DFW,YMX,DEN,etc.... The user inputs a part#, then a Dlookup function is used to populate unbound txtboxes to view the parts data. Also a table is built that is populated with the airport IDs. Each field represents the part status at the airports. Like: Allocated,BackOrdered,OverAllocated, etc... A cmdbutton is used to run the code I posted - which taps into the labels properties, bla, bla, yaddi.... This code is a hybrid mixture of tek-tips help and self taught nonsense. Anyhow - the form basically needs to be a report so it can be emailed as a .snp & printed. I have the form set up to maximize to desktop settings with no toolbars (better for viewing the world!!). I have everything in place, the same as the form - in a report. Just needed for it mirror the form!!!
But if the FE & BE thing is all I need to do.... then heck!!
Thanks for your help!!
jw

 

[dhookom]

I'm not sure what your table structures are but I would include X and Y coordinates in the table of airports. These coordinates would correspond with positions on the report page. Then, I would have a table of parts and airports. Each record would consist of a partnumber, airport code, and status/quantity or whatever.

Then make a report with a detail section as large as possible. The record source would be the parts and airports tables where the criteria filters by the part. Bind the coordinates and other fields to controls in the detail section. Use code in the On Format event to move some of the controls to the proper coordinates.

Then, you want to overlay every detail section for each record on top of each other. This is done with a single line of code in the On Format event of the detail section:

     Me.MoveLayout = False

Your recordset is derived from the report's record source. There is a similar solution report in the Corp Tech Demos that plots Height vs. Weight of people. The demo can be found at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jw5107]

I think I understand the coordinates you explaing - after viewing the example from the link you provided.

The part I'm having troubles with is where you say: "Bind the coordinates and other fields to controls in the detail section. Use code in the On Format event to move some of the controls to the proper coordinates".
The report is a map of the world just like the form. When you have an airport like ANC (which Anchorage, Alaska), how do I get the code to move the control to represent where ANC is at in Alaska? The map is just an image from a powerpoint slide, pasted on to the report detail section. So - I'm just confused as to how I get the coordinates right, move the control to that location, and then have it printed in different colors to represent the different part status's from the airport table...
Man - I like your example. Thanks for turning me on to some new moves!! Thanks for you help!
jw

 

[dhookom]

Assuming your map is in the detail section of your landscape report. ANC might be 2" from the left and 1.5" from the top. MSP might be 3" and 2". This suggests in your table of airports, you would have a record like:
[tt][blue]
tblAirportCodes
Code City State X Y
===== =========== ======== ==== ====
ANC Anchorage AK 2880 2160
MSP Minneapolis MN 4320 2880
[/blue][/tt]

Make sure you include tblAirportCodes in your report's record source query. Also, bind X and Y to controls in the detail section. They should be invisible.

Assuming you have a text box named txtMyValue that you want to "move" to the airport location in the detail section, your code would look like:

    Me.txtMyValue.Left = Me.X
    Me.txtMyValue.Top = Me.Y
    Me.MoveLayout = False

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]