Incrementing Report Count

[andrewneal]

I am working on a report that gets printed once or twice a week. The manager would like this report to print the year and the increment number (for that year) on the report. For instance, when the first one for 2007 is printed the increment number will look something like 2007001 and the second will look like 2007002. This increment would only be the number of times they have printed/viewed this report for that year and would need to be automatically refreshed on the 1st of each year.

I thought about making a table that has this increment count in it. Then write a code that will update that table with a number "1" each time the report is printed/viewed. I would imagine I could write this into the report's "on open" function, but I would have no idea how to do this. I could then build a query that counts the total numbers in this table and put that query number in the report.

Any help would be much appreciated!

 

[gemma-the-husky]

andrew

perhaps have a table of all your reports - then you can store the count, who did it last , and when etc

if you want to be really careful you could keep all this info for each preinting of the report

as you say in the open event you just need to retrieve the current counter for this report, and then increment it, and write back any other data back into the counter table.

dlookup to get the data
sql statement to update the values

 

[grnzbra]

Create your table
Year
Count

In the OnOpen statement,

Public Funtion NewIncrement() as string
dim db as database
dim rs as recordset
dim sqlstr as string
dim cnt as long

sqlstr = "Select * From Table Where Year = Year(Date()) OrderBy Count Desc" (Or something like that)
set db = currentdb
set rs = db.openrecordset(sqlstr)

If rs.eof Then
NewIncrement = Year(Date()) & "001"
Else
rs.movefirst
If rs.Year <> Year(Date()) Then
cnt = 1
NewIncrement = Year(Date()) & "001"
Else
cnt = rs.count + 1
NewIncrement = Year(Date()) & Format(cstr(cnt),check for proper format to add leading zeros)
End If
End If
rs.addnew
rs!Year = Year(Date())
rs!Count = cnt
rs.update

I expect that is full of syntax errors, but that is a general method for doing it.

 

[andrewneal]

Great help! Got me off to a wonderful start; thanks! Here is what I got: Table named "tbl Count" with 2 columns..."Year" and "Count" (nothing else). The format for the "Count" column is "0000" and is formating the numbers correctly as 0001 automatically. However, the report is acting stupid and getting an #Error when I try to attach a control source for the text box in the report. Also, it is not updating the table. What am I missing?

Public Function NewIncrement() As String

End Function

Dim db As Database
Dim rs As Recordset
Dim sqlstr As String
Dim cnt As Long

sqlstr = "Select * From Table Where Year = Year(Date()) OrderBy Count Desc"
Set db = CurrentDb
Set rs = db.OpenRecordset(sqlstr)

If rs.EOF Then
NewIncrement = Year(Date) & "001"
Else
rs.MoveFirst
If rs.Year <> Year(Date) Then
cnt = 1
NewIncrement = Year(Date) & "001"
Else
cnt = rs.Count + 1
NewIncrement = Year(Date) & Format(CStr(cnt))
End If
End If
rs.AddNew
rs!Year = Year(Date)
rs!Count = cnt
rs.Update

 

[grnzbra]

First of all, I should have mentioned not to use Count, Year and Date for field names. These are reserved words and might cause problems. I used them for illustrative purposes.

Gemma's solution does an update whereas mine adds a new record. If you want to keep a history of publications with increment number and other stuff such as date, use the update. Otherwise, go with the update.

As far as setting the text box controls source, you should be able to set it to the function.
= NewIncrement()

By the way, if that is the exact code you have, you have nothing inside the function. The End Function line should be at the end.

 

[andrewneal]

First, I sincerly appreciate the help with this! I am learning a lot by playing around with this and I appreciate your assistance!

Now let me say "Duh; what a yo-yo!" I put the End Function at the end; can't believe I missed that; thanks! I changed to column names to "Yr" and "Cnt."

I have no need to "track" the data, since it is only 1 report that has filtered data that will be printed on it. I like the idea of just updating the table; which I can delete from as needed through an append query.

I put "=NewIncrement()" in the control source but it still gives #Error; ugh!

I also get a compile error (Method or data member not found) on line "If rs.Year <> Year(Date) Then." It highlights the ".Year" on that line.

I attached a copy of what I am looking at...

 

[andrewneal]

Sorry; column name is "Number" not "Cnt."

 

[grnzbra]

AARGH! I blew that one.
Set the onopen event of the report to

Me.Label.Caption = NewIncrement()

That should call the function and the label which is to display the 2007001 will get set to that.

try !year instead of .year.

To update instead of add a record, try

rs.edit
rs!year = Year(Date())
rs!count = cnt
rs.update

 

[jwhite]

Well, a little late but.... here's my 2 cents worth... I'm an 'Intermediate' fellow, so if the code below can be written better, please advise! Of course, you could move all of it to a Function.

Table Name: _IncrementingReportCount
CurYear = Current Year value
Counter = Counter of how many reports/views executed

In Report : lblCounter, no special formatting or anything.

Option Compare Database
Option Explicit

Private Sub Report_Open(Cancel As Integer)
    Dim strCurYear As String
    Dim strTstYear As String
    Dim dbs As Database, rst As Recordset
    Set dbs = OpenDatabase(CurrentDb.Name, dbOpenTable)
    Set rst = dbs.OpenRecordset("_IncrementingReportCount", dbOpenTable)
    
    strTstYear = Format(Date, "yyyy")
    
    With rst
        .MoveFirst
        strCurYear = !CurYear
        If strCurYear = strTstYear Then
            Me!lblCounter.Caption = !Counter + 1
            .Edit
            !Counter = Me!lblCounter.Caption
            .Update
         Else
            .Edit
            !CurYear = strTstYear
            !Counter = 1
            .Update
            Me!lblCounter.Caption = 1
        End If
    End With
    dbs.Close
    Me!lblCounter.Caption = strTstYear + Format(Me!lblCounter.Caption, "000")
    strCurYear = ""
    strTstYear = ""
End Sub

 

[andrewneal]

Now I am getting a Run-time error '3421': Data type conversion error on the red part...

Else
.Edit
!CurYear = strTstYear
!Counter = 1
.Update
Me!lblCounter.Caption = 1

 

[grnzbra]

What kind of field is CurYear in the table. If it is a Date/Time field, it may not take strTstYear which is defined as a string.

 

[andrewneal]

I think I got the string error fixed, I did have it set to Date/Time; changed it to number and it works. But, it says the object does not support this property or method:

.MoveFirst
strCurYear = !CurYear
If strCurYear = strTstYear Then
Me!lblCounter.Caption = !Counter + 1
.Edit
!Counter = Me!lblCounter.Caption
.Update

Attached is a picture of the lblCounter Textbox properties...

Table Name: _IncrementingReportCount
CurYear set to Text (with no special formatting)
Counter set to number (with no special formatting)

Should I have something that tells the code to "Dim Counter As Long?"

 

[grnzbra]

You are using a text box for Me!lblCounter. Text boxes don't have Caption properties. Use a label; they do have a Caption property. (In order to get it to stay there when you first create the label, you may have to put some letter into it. Go ahead because the VBA will put the text you want in there.). Also, you may need to convert !Counter + 1 to Cstr(!counter = 1) because the caption may be limited to strings and !counter must be a number in order to add 1 to it.

If Counter is a field name in the table and is set as a number, it should default to long.

 

[andrewneal]

As soon as I changed it from "text box" to a "label" as you said...it worked! Thanks again everyone for helping me do this!