Query/formatting problem in report

mega · August 12th, 2004, 07:16 AM

Hi.
I'm trying to make some sort of color formatting in my report, but can't get the field from my query evaluated. So far this is what I have come up with:

Code:

Private Sub Detaljesektion_Format(Cancel As Integer, FormatCount As Integer)
  Const Green = 8454016
  Const White = 16777215

  Select Case ([qrySamleaftaler].[HarAreal]) 'this is my query
    Case True
      Me.FillColor = Green
    Case False
      Me.FillColor = White
    Case Else
      Me.FillColor = White
  End Select
End Sub

I get a run-time error '2465' when I try to use it. Any help to this newbie problem would be great!

- mega

DBoulos · August 14th, 2004, 03:19 PM

Assuming qrySamleaftaler, is the datasource, for your report, to reference a control...

Instead of..
Select Case ([qrySamleaftaler].[HarAreal]) 'this is my query
try...
Select Case Me.HarAreal

Or, if your query is based on more than one table, and more than one control has the same name, you would use
Me.[tblTable.Field] (I believe, that is the proper syntax), or something similiar.

...either way, your original is incorrect.

Hope this helps.

mega · August 18th, 2004, 09:53 AM

Hmm.. Thank you for the reply, but I still get the same error message.
Here is my SQL:

Code:

SELECT Data.UdlejningsenhedsNr, Data.LejeaftaleNr,
 Data.Lejer, Bem.BemNavn, Data.Samleaftale,
 Data.Type, Data.HarAreal
FROM Bem RIGHT JOIN Data ON Bem.BemID=Data.BemÃ¦rkning
WHERE (((Data.Samleaftale) Is Not Null) AND ((Data.IgangvÃ¦rende)=False));

I thought I might declare the query first:

Code:

Private Sub Detaljesektion_Format(Cancel As Integer, FormatCount As Integer)
  Const Green = 8454016
  Const White = 16777215
  Dim blnHarAreal As Query 'trying to declare...
  blnHarAreal = Me.[qrySamleaftaler.HarAreal]
  
  Select Case (blnHarAreal) 'this is my query
    Case True
      Me.FillColor = Green
    Case False
      Me.FillColor = White
    Case Else
      Me.FillColor = White
  End Select
End Sub

That didn't work.. Do you know where I can find a good tutorial on Access VBA - report formatting?

- mega

DBoulos · August 18th, 2004, 07:12 PM

I don't know of one, exclusively for "report formatting".

But mega, I think you need to open a filtered report, for what you are trying to do.
(I didn't follow your objective, till now).

Something like this...

Private Sub Report_Open(Cancel As Integer)
Dim SQL As String

SQL = "SELECT Data.UdlejningsenhedsNr, Data.LejeaftaleNr, Data.Lejer, Bem.BemNavn, Data.Samleaftale, " & _
"Data.Type, Data.HarAreal " & _
"FROM Bem RIGHT JOIN Data ON Bem.BemID=Data.BemÃ¦rkning " & _
"WHERE (((Data.Samleaftale) Is Not Null) AND ((Data.IgangvÃ¦rende)=False));"

Me.RecordSource = SQL
End Sub

Then...

Private Sub Detaljesektion_Format(Cancel As Integer, FormatCount As Integer)
  Const Green = 8454016
  Const White = 16777215

  Select Case HarAreal 'this should be, name of textbox (assuming, it has true/false value?)
    Case True
      Me.FillColor = Green
    Case False
      Me.FillColor = White
    Case Else
      Me.FillColor = White
  End Select
End Sub

This is one option.
I haven't used Report filter before, but this should work.
Good Luck, I'll stay posted.

mega · September 1st, 2004, 04:38 AM

What I really want to do is not showing HarAreal and Type, but change the background color of the detail section according to those 2 (HarAreal and Type) true/false fields. The question is how do I refer to the query on which my report is build upon?

- mega

ara_gupta · September 1st, 2004, 11:27 AM

Please explain more details in ur question. I have a alternate code.
Select case()
    Case True
      RPT.FillColor = Green
    Case False
      RPT.FillColor = White
    Case Else
      RPT.FillColor = White
  End Select

aranibhash

DBoulos · September 1st, 2004, 11:40 PM

Niet te Geloven!, Ik ben stom, denke ik?

Me.Detail.BackColor = vbGreen

Heel makelijk!

DBoulos · September 1st, 2004, 11:52 PM

Probeer deze....

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

  Select Case HarAreal
    Case True
      Me.Detail.BackColor = vbGreen

        Case Else
      Me.Detail.BackColor = vbWhite

  End Select
End Sub

mega · September 16th, 2004, 04:29 AM

Tanks for the replies.
I got my report up and working based on a stored procedure (RecordSource = qrySamleaftaler).
In the stored proc I get all the fields I want, filtered and joint. It's only the presentation of the data that's troubling me. Right now I've made some extra fields in the report (Data.Type, Data.HarAreal), I don't want to display those as fields but as different bgcolors. I figured if I could get just one the fields working, I could easily make my formatting from there. But right now I can not figure out how to accomplish this.

Cheers.

- mega