Addnew only adds one record!!

[Lisad]

I have the following code which is supposed to insert a new record into TBLLetterHistory when a report is printed. The report is based on a Query which selects the records based on their postcode.

What actually happens is that only the first record in the query is inserted into the History Table. Any suggestions would be gratefully recieved as I have no idea, even after long searches in this forum!!


Private Sub Report_Activate()
Flag = 0
End Sub

Private Sub Report_Deactivate()
Flat = -1
End Sub

Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)
Dim dbs As DAO.Database, rst As DAO.Recordset
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TBLLetterHistory")

Flag = Flag + 1
' If the current value of Flag = 1, then a hard copy of the
' report is printing, so add a new record to the history table.
If Flag = 1 Then
rst.AddNew
rst!ReportName = "RPTLetterByPostcode"
rst!Date = Now
rst!LeadID = [QRYLetterByPostcode.LeadID]
rst.Update
rst.MoveNext
' Flag = 0

End If
End Sub

 

[ScottGem]

This would be easier using SQL

strSQL = "INSERT INTO TBLLetterHistory (LeadID, LtrDate, ReportName) "
strSQL = strSQL & "SELECT LeadID, Now() AS LtrDate, 'RPTLetterByPostcode' AS ReportName FROM QRYLetterByPostcode;"
CurrentDB.Execute strSQL

Note: I change the name for your date field. Date is a reserved word in Access and shouldn't be used as an object name.

 

[Lisad]

Too few parameters

I have put the SQL into the Report Footer's On Print. When I run the report and scroll through the letters I get an error that says 'Too Few Parameters. Expected 1.' and the Debug highlights CurrentDb.Execute strSQL. Any ideas?

 

[ScottGem]

You need to check the SQL you are generating. There is probably something wrong in the syntax.

 

[Lisad]

This is exactly what I have...

Option Compare Database


Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)

strSQL = "INSERT INTO TBLLetterHistory (LeadID, LtrDate, ReportName) "
strSQL = strSQL & "SELECT LeadID, Now() AS LtrDate, 'RPTLetterByPostcode' AS ReportName FROM QRYLetterByPostcode;"
CurrentDb.Execute strSQL

End Sub

I copied what you said - all I changed was the name of my Date field in TBLLetterHistory to LtrDate.

Any ideas?

 

[ScottGem]

That looks OK. TRY building the query in Query Design mode and see what the SQL looks like.

Open a New Query in Design mode and add QRYLetterByPostcode. Select the LeadID column as the first column. In the 2nd column put the expression:

LtrDate: Now()

In the 3rd column put the expression:

ReportName: "RPTLetterByPostcode"

Right click on the table pane and select Query Type and select Append. When prompted select TBLLetterHistory. This should add an AppendTo row and show the three fields from TBLLetterHistory. Go into SQL mode and see if the SQL matches what I gave you.

If you still don't see any problems, can you post a stripped down version of your database?

 

[Lisad]

All looks fine

Scott,
I did the append query and it looked exactly the same as what you suggested apart from it had QRYLetterByPoscode.LeadID instead of just LeadID. I changed it and it still came up with the 'Too Few Parameters' rubbish.

I've attached a copy of my DB. Thanks for having a look at this.

LisaD

 

[Lisad]

I've made some progress, but...

I have changed things a bit. This is the code I currently have. I have RPTGeneralLetter being ran based on a combo box to select the query it uses. I also changed to DoCmd.RunSQL and this works.

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = Forms!FRMLetter!ComboQRY
End Sub


Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)
Dim strSQL As String

strSQL = "INSERT INTO TBLLetterHistory (LeadID, LtrDate, ReportName) "
strSQL = strSQL & "SELECT LeadID, Now() AS LtrDate, 'General Letter' AS ReportName FROM QRYLetterByPostcode;"
DoCmd.RunSQL strSQL
End Sub

What happens now is I am prompted to enter the parameter value for the query twice, once when the report opens and once when the report prints. Apart from this, the data is inserted into the history table. How can I get the query to run just once?

 

[ScottGem]

Don't use a paremeter prompt. You are running this from a form. Input the parameter as a combo or textbox on the form and reference it in the query.

 

[Lisad]

This worked great - Thanks Scott!