Big Problem

[nayfeh]

Hi,

I have a major problem that I need help with.
I have setup a form that generates a report with a command button. This reports calls information for a stored procedure in SQL. There is one main table that the infomation is from (tbl_Headers). I can link this table up to my database if it helps (it's on the SQL server right now). This table has 2 fields, one called "Printed" another called "Duplicate". Both are integer fields.

Here is my problem. I need these 2 fields to get updated when the report is printed. The first time it's printed, the "Printed" field should change from 0 (default) to 1. The next time the report is printed, the Duplicate field should change from 0 (default) to 1. That way I can place a text box on the report called DUPLICATE so that the user will know if the report has already been run.

Is this possible in any way. Your help is very much needed. If you need any further info, please let me know.

Thanks,
TN

 

[FancyPrairie]

You could just use 1 field to accomplish this. Just keep incrementing the Printed field. If it's greater than 1, then display Duplicate. But, anyway, in the OnClose event of the Report, open the table and change the value.

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset

rst.Open "Select Printed, Duplicate from tbl_Headers;", cnn, adOpenKeyset, adLockOptimistic

If (rst!Printed = 0) Then
rst!Printed = 1
Else
rst!Duplicate = 1
End If

rst.Update
rst.Close
Set rst = Nothing

I'm comfused. When they print the report the first time, everything is ok. Next time they get message duplicate? What if they want to print the report a couple days from now? Has the data changed in that time? I don't see how this is going to work.

 

[johnnymac43]

Hi FancyPrairie

Don't be confused. This scenario happens in many business systems, and should be constrained so that data cannot change after the original report is printed. A common example is an Invoice, where a marked duplicate is required for the paper file, and the ability to reprint another duplicate at a later time is required to cater for the loss of the original.

Cheers

John

 

[nayfeh]

John is exactly right. This report will be used as a Picking List, similiar to an invoice. The data will not change, but the report should note Duplicate if it's already been printed.

Thanks,
TN

 

[Craig0201]

But don't use a recordset. Use SQL. Won't clog the CPU.

Craig

 

[nayfeh]

I tried the above code in the OnClose event. But it did not work. The fields did not get updated. Am I missing anything? Here is the code I have included in my form's command button to run the report. This runs the stored procedure. I have a pass-through query for this procedure in my database.

strSql = "sp_NOPickList " & "'" & strStartDate & "', '" & strEndDate & "'"

Set qd = CurrentDb.QueryDefs("sp_NOPickList&quot
qd.SQL = strSql
qd.Close

Thanks again!
TN