ForeGround color in subform

[freefour]

Hi,

In my subform that displays all invoices for a particular customer, I want it so that if an invoice is paid, all the field in a row are green, and the same row to be red when it is not paid. I thought I had this acomplished with a couple simple IF...Then statements and using the .Foreground property control.

I have the IF...Then statements in my subforms On Current event. It works to a point. It takes the color conditions for the first row of data and uses that color for all the rest of them.

I am using ACCESS 97 and so I can not use that conditional formating under the Format menu. Can someone tell me which even to place this in or another way of doing this?

Thanks SO much!

--freefour

 

[kjv1611]

First off, you say "fields", do you mean "controls" on a form? Also, what type of form - basically, when you open the form in form view, do you see multiple records on one page, or just one? If just one, then try this (may work either way, but i know it works for single form):

Private Sub SomeEvent()
  Dim ctl as Control
  Dim frm as Form
  Set frm = frmMyFormName
  For Each ctl in frm.Controls
    If ctl.ForeGround = vbBlue Then
       ctl.ForeGround = vbWhite
    End If
  Next
End Sub

Basically, the code loops through all your controls on your form, and changes the forecolor if the current forecolor is blue (in this example) You could also add in th if/then statement for the type of control.. like this:

For Each ctl...
  If TypeOf ctl is TextBox or Typeof ctl is ComboBox Then
     ~other code here (from first example)..
  End If
Next

If this is not what you're looking for, try posting your code, and possibly being more specific? Or if need additional detail, ask.. Whatever the case, be sure to post back with results.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[freefour]

Stephen,

Well, this is a continous subform that displays:

InvoiceID, Date, Amount Paid, and Total

So this subforms shows all the different invoices for a particular customer.

If Amount Paid = Total, then I want them to be Green (for the entire row, all field's ForeColor that are shown)

if Amount Paid < Total or Amount Paid = 0 then I want that row to show Red.

Hope that helps explain.

 

[kjv1611]

Okay, well, if you are wanting to change all the textboxes, etc on your form, then adding your if statements in with the loop above should work.. try and post back with progress, or post back if need more detail..

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[freefour]

Stephen,

Yes they are controls, sorry I'm a newbie to all of this.

Here is my if then block (in the subform's On Current event):

Dim lngRed As Long, lngGreen As Long

lngRed = RGB(255, 0, 0)
lngGreen = RGB(0, 128, 0)

If Me.AmountPaid.Value = Me.Total.Value Then
Me.InvoiceID.ForeColor = lngGreen
Me.Date.ForeColor = lngGreen
Me.AmountPaid.ForeColor = lngGreen
Me.Total.ForeColor = lngGreen
End If

If Me.AmountPaid.Value < Me.Total.Value Then
Me.InvoiceID.ForeColor = lngRed
Me.Date.ForeColor = lngRed
Me.AmountPaid.ForeColor = lngRed
Me.Total.ForeColor = lngRed
End If


This is on a subform with the view set to Continous Forms. So, it shows all Invoices that are linked to the Customer on the parent form.

It looks at the first invoice in the subform (and say it's paid) then it shows every invoice Green. I want it to loop (as you say) and look at each record one by one.

I have tried for 2 hours now to get your For Next loop to work and still am having no luck.

Any other ideas?

--freefour

 

[TheAceMan1]

How are ya freefour . . . . .

I seem to remember(I could be wrong) AN [blue]API[/blue] routine that would allow you to do this in Access 97, but I've yet been able to find it. Other than that, there's nothing you can do outside of upgrading to 2000, which considering the state of affairs with Access, would be a [blue]worthy[/blue] thing to do!

See Ya! . . . . . .

 

[freefour]

TheAceMan1,

I am writing this for a friend as a favor, and you're right, upgrading seems logical to me, but my friend is going through some hard times and that isn't an option because of cost. Doing this color coding would be a great enhancement, but it's not an absolute necessity. If you run across that API routine, Iwould be very thankful. I still appreciate Stephen and your help. Thanks again,

--freefour

 

[willir]

I seem to recall both the Cajun and PHV had posted solutions to this type of thing. Tek-Tips took a couple of steps backwards with their summer upgrade, and the search function is one of themm, so searching within Tek-Tips has to be done externally. (But Tek-Tips has been addressing some of the functionaility lost with the upgrade - yeah!)

And the Ace is right, it was in conjuction with an external program.

There is a work-around. You can create a few helper controls on the main form...
- unbound text box displays total amount owing
- unbound text box displays total number of invoices not paid
- have an unbound combo box, or option group to select only unpaid invoices, unpiad for 60 days, recently paid invoices, credit notes, or all invoicing history.

Unlike the contineous forms, you can use colour coding for the unbound text boxes. For example, if there are unpaid invoices that are older than 60 days, you can flag amount owing using red.

There are variations to this okay, but you are right - the use of colour is very effective for flagging records that need closer attention.

The code to this type of thing is fairly simple...

Create an SQL statement for the subform, and then requery the subform...

Dim strSQL as String
strSQL = "Select * from YourInvoiceDetailTable Where CustomerID = " _
& Me.CustomerID
Me.YourInoviceSubForm.Form.RecordSource = strSQL
Me.YourInoviceSubForm.Requery

I am assuming Me.CustomerID is on the main form. You will have to substitute your table and field names where appropriate.

Richard

 

[RoyVidar]

Never done it, I don't work with 97 anymore, but you could try some of the routines from TheAccessWeb Colors and Continuous forms. Perhaps one of the two downloads might assist.

Search function works from where I am, lots of threads on conditional formatting in continuous forms. Just a bit hard to differensiate between 97 and later versions.

Roy-Vidar