Identifying Revisions In a Report with Bolding 1

[Shaz1]

Hi all,

Does anyone have any suggestions for a recent new user?

In Access 2000 I am designing a report to show revisions or changes. Example:

Multiple revisions of a record added to a table with different revisions (A, B, C ect

In the report I need to print the the latest revision (queried by latest date) and Identify the changes that incurred on the new revision. There currently are 21 columns in the report and any given column of data could change. I would like to bold the changed data in the table but Access will change all the text to the format that is selected.

Any ideas

Shaz1

 

[BoxHead]

Shaz1,

You don't explain how you determine whether something has been changed so I'll assume you have a way to do that.

On the Format event for the report section, you should be able to use VBA to reformat controls using If...Then...Else coding.

If textBox1 <> t1OldValue Then
textBox1.FontBold = true
Else
textBox1.FontBold = False
End If

HTH


John

Use what you have,
Learn what you can,
Create what you need.

 

[Shaz1]

Hi Boxhead,

Thanks for the code! I will try it out and get back to you today.

YOu guys are awsome!

Thanks a bunch.

Shaz1

 

[Shaz1]

Hi Boxhead,

I must explain that I am very new at VB and experimented with using "*" for all the textBoxes. But I seem to get a compile error with the wildcard. Do you know the best way to identify all of the textBox's?

Option Compare Database

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If textBox* <> t* OldValue Then
textBox*.FontBold = True
Else
textBox*.FontBold = False

End If

End Sub

 

[BoxHead]

How do you determine a record has been revised?

John

Use what you have,
Learn what you can,
Create what you need.

 

[Shaz1]

BoxHead,

If a new revision/record is added then we assume that their is some modification to either fields. I plan on using MaxOnDate to identify those changes.

Shaz1

 

[BoxHead]

Shaz1,

To check all textboxes, you have to loop through the controls collection.

I set up a report with four fields in a tabular format. The first column is a date field (as I assume your MaxOnDate would be).

The following code makes each of the textboxes in records with a date field greater than 4/11/2005 to bold.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim myCtl As Control
For Each myCtl In Me.Controls
If myCtl.ControlType = acTextBox Then
If myVal > #4/11/2005# Then
myCtl.FontBold = True
Else
myCtl.FontBold = False
End If
End If
Next myCtl
End Sub

the line with If myVal > #4/11/2005# Then is the line that you would revise for your report. myVal would be the name of the textbox that has the date you want to compare against your criteria and the date #4/11/2005# would be replaced with your criteria.

HTH


John

Use what you have,
Learn what you can,
Create what you need.

 

[Shaz1]

Good Morning BoxHead,

I made the date field (MaxOfDate) the first column in my report and added the code. I cannot see any change in formatting on the report. Perhaps I am missing something.
I've attached the query for you to view.



SELECT Max(CABLES.REVDATE) AS MaxOfREVDATE, CABLES.[CONDUIT OR CABLE NO], CABLES.APPLICATION, CABLES.HP_KW_kVA, CABLES.VOLTAGE, CABLES.[FROM EQUIPMENT NO], CABLES.[FROM DESCRIPTION], CABLES.[FROM CABLE CONNECTOR], CABLES.VIA, CABLES.[TO EQUIPMENT NO], CABLES.[TO DESCRIPTION], CABLES.[TO CABLE CONNECTOR], CABLES.[TYPE/SIZE], CABLES.[EST LENGTH -METERS], CABLES.[SIZE AWG], CABLES.[QTY OF CONDUCTORS], CABLES.INSULATION, CABLES.[MAX VOLT], CABLES.[SPARE CONDUCTORS], CABLES.REMARKS, CABLES.CWP, CABLES.REVISION
FROM CABLES
GROUP BY CABLES.[CONDUIT OR CABLE NO], CABLES.APPLICATION, CABLES.HP_KW_kVA, CABLES.VOLTAGE, CABLES.[FROM EQUIPMENT NO], CABLES.[FROM DESCRIPTION], CABLES.[FROM CABLE CONNECTOR], CABLES.VIA, CABLES.[TO EQUIPMENT NO], CABLES.[TO DESCRIPTION], CABLES.[TO CABLE CONNECTOR], CABLES.[TYPE/SIZE], CABLES.[EST LENGTH -METERS], CABLES.[SIZE AWG], CABLES.[QTY OF CONDUCTORS], CABLES.INSULATION, CABLES.[MAX VOLT], CABLES.[SPARE CONDUCTORS], CABLES.REMARKS, CABLES.CWP, CABLES.REVISION, CABLES.REVDATE
HAVING (((Max(CABLES.REVDATE))>#4/10/2005#) AND ((CABLES.[CONDUIT OR CABLE NO]) Like "230*" Or (CABLES.[CONDUIT OR CABLE NO]) Like "240*" Or (CABLES.[CONDUIT OR CABLE NO]) Like "300*"));

 

[BoxHead]

Shaz,

It looks to me like this query would only return the last revision date for each record unless Cables.Revision is your revision date by record???

If that's the case, replace If myVal > #4/11/2005# Then
with

If [Revision] = [MaxOfREVDATE] Then

Let me know if I'm understanding this correctly. Also, is the >#4/10/2005# in your query something you added because of my post or did we just happen to have an example within 24 hours of the real thing?




John

Use what you have,
Learn what you can,
Create what you need.

 

[Shaz1]

Hi Boxhead,

Thanks for responding so quickly. Yes,this query was set up to show records with criteria >#4/10/2005# to check the formatting if it had changed.
Let's say I have 10 records with Rev A. A report is then issued. Then a user comes in and updates some of the records to Rev B. Note:They are expected to insert a new record so we can track each revision and corresponding data. I am trying to pull only the latest records and identify those changes. The report must contain all Rev A (Non changed data) and new Rev B records. So on the date that the user inputs the changes I will query that data along with the unchanged data and report with that. I hope this helps you.

Shaz1

 

[BoxHead]

What is Rev A? Is that a field? Is that the value of a field? Which field?

If you print your report right now, and went through with a pencil to underline the records that you want in bold, how would you know which ones to underline?

John

Use what you have,
Learn what you can,
Create what you need.

 

[Shaz1]

Hi Boxhead,

Rev A,B and C is a value in the Field [Revision]. The report shall consist of all Rev A and only the next Revision ie; B. All the additional Revisions like the new Rev B changes are what I would like to have formatted in Bold.

 

[BoxHead]

in the code, use

If [Revision] = "B" Then

John

Use what you have,
Learn what you can,
Create what you need.

 

[Shaz1]

Boxhead,

I forgot to mention that the Date field is set to now() so I had hoped to pull out the latest date where there is a duplicate record with another Revision.

 

[Shaz1]

Boxhead,

By gully you got it! Thank you so much for your patience and perserverance. You have helped me tremendously with this problem!


Shaz1

 

[BoxHead]

Glad to help, Shaz1!



John

Use what you have,
Learn what you can,
Create what you need.