Updating a table based on a report print event 
Author Message
 Updating a table based on a report print event

Anyone any good at VBA in Access reports?

I want to update a record in a table when a report for that record is
printed.

I've coded the reportdetail_print event as follows:

    Dim rs As Recordset
    Dim Current As String

    Current = Report.CurrentRecord
    Set rs = CurrentDb.OpenRecordset("Locations", dbOpenTable)

        rs.Edit
        rs.Fields("IndexPrinted") = Now
        rs.Update

This updates the first record in the table "Locations" but I can't work
out how to get it to find the record that = the current record on the
report and update that. I assume you use the rs.findfirst function, but
can't work out the syntax. Anyone any ideas?

Thanks in advance! :-)

Julia Cracknell
Quality System Developer
Rockwell-Collins (UK) Ltd.

Sent via Deja.com http://www.*-*-*.com/
Before you buy.



Sun, 27 Apr 2003 03:00:00 GMT  
 Updating a table based on a report print event
You need to have a unique field in the detail section of the report. It can
be invisible.
If the primary key field is IDField then the following code should work.

     Dim strSQL As String
     strSQL "Update Locations SET IndexPrinted = #" & Now() & "# " & _
          "WHERE IDField = " & Me.IDField
     Currentdb.Execute strSQL

If the IDField is text, then you will need to use:

     Dim strSQL As String
     strSQL "Update Locations SET IndexPrinted = #" & Now() & "# " & _
          "WHERE IDField = """ & Me.IDField & """"
     Currentdb.Execute strSQL

Duane


| Anyone any good at vba in Access reports?
|
| I want to update a record in a table when a report for that record is
| printed.
|
| I've coded the reportdetail_print event as follows:
|
|     Dim rs As Recordset
|     Dim Current As String
|
|     Current = Report.CurrentRecord
|     Set rs = CurrentDb.OpenRecordset("Locations", dbOpenTable)
|
|         rs.Edit
|         rs.Fields("IndexPrinted") = Now
|         rs.Update
|
| This updates the first record in the table "Locations" but I can't work
| out how to get it to find the record that = the current record on the
| report and update that. I assume you use the rs.findfirst function, but
| can't work out the syntax. Anyone any ideas?
|
| Thanks in advance! :-)
|
| Julia Cracknell
| Quality System Developer
| Rockwell-Collins (UK) Ltd.
|
|
| Sent via Deja.com http://www.deja.com/
| Before you buy.



Sun, 27 Apr 2003 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. How to print Report based on Access table

2. Update table based on data in another table

3. printing without crystal report or data report for non data base reports

4. how to update base table

5. how to update base table in Access 97

6. Insufficient base table information for updating or refreshing.

7. Help .....insufficient base table information for updating or refreshing

8. Unable to Update SQL recordset: Insufficient base table information

9. Insufficient base table information for updating or refreshing

10. Error: Insufficient base table information for updating or refreshing

11. Insufficient base table information for updating or refreshing

12. Insufficient base table information for updating or refreshing.

 

 
Powered by phpBB® Forum Software