How to Update Access Table From Report? 1

[snehakevin]

My Access Report uses a field value from a table as a starting number and I want to store the last generated number (say invoice number) into the table, to be used when the report is run the nexttime.

 

[PaulBricker]

One way to do this would be to use an Insert Into statement in the Format Event for the report section the number is in.
mysql = "Insert Into tblName(NumberField) Values (& NumberField & )"

Assuming you have incrementing numbers and you only want the last, you would have to insert all the numbers in the table and then create a query that extracted just the MaxofNumber to return just the last one.

Paul

 

[snehakevin]

Thank you Paul.

Where to put this insert into statement ?
The number is printed from a text box, there is no format event in the properties window.

 

[PaulBricker]

If the textbox is in the Detail section then you would put your code in the Format event for the Detail section. It would look like this.

Dim mysql as String
mysql = "Insert Into tblName(NumberField) Values(& rptFieldName & )"
DoCmd.SetWarnings False
DoCmd.RunSQL mysql
DoCmd.SetWarnings True

Each time the textbox is formatted (once for each record) it will insert the current value for the textbox into your table. Then you would use a query to extract the MaxofNumber from your table.

Paul

 

[snehakevin]

Thank you Paul...
Instead of Insert statement I used Update statement. I have put this code in the report footer, so the table will have the last generated number which my query will pick the fist time and increment it....
Thank you once again

 

[PaulBricker]

Seems like a very reasonable solution. Thanks for posting that. It will probably help someone else.

Paul