Invoice Numbering

[cfobare79]

I have a report that has "Text137" that has my ReportNum (="ITS" & "-" & "ECS" & "-" & [AgencyID] & "-" & "000"). I would like to have the ReportNum increase by 1. [AgencyID] is a text box. I also have a Button that stores parts of the report into a table called "Report". Below is the code I'm currently using to load the Report fields into the Table "Report". SO the fix I need would increase the "ReportNum/Text137" by 1 and also change the code below to allow it to be imported into the Table "Report". Thank you for any help.

Private Sub Command192_Click()
CurrentDb.Execute "INSERT INTO Report ( ReportNum, AgencyID, AgencyName, ServiceGroup, StartDate, EndDate, ServiceName ,Total) " & _
" VALUES ('" & [Text137] & "'," & [AgencyID] & ",'" & [AgencyName] & "','" & _
[ServiceGroup] & "',#" & [Text106] & "#,#" & [Text0] & "#,' " & [Label197].[Caption] & " ', ' " & [Text188] & " ')"


End Sub

[June7]

Generating custom unique identifier is common topic. Here is one for start https://www.accessforums.net/databas...try-21975.html

[cfobare79]

I see lots of Threads of using a table to create the identifier. I have a report I would like to create the identifier and then transfer it to a table.

[June7]

A table is always involved if ID is incremented. Code would build new ID based on the latest ID in table. First code has to pull up the last ID and then increment. A review of links should have led you to example code in http://forums.aspfree.com/microsoft-...ta-403208.html

What do you mean by 'load the Report fields into the Table "Report"'? Why are you not just using a query as the report data source?

[cfobare79]

I have a query that pulls from multiple tables and creates the report. I then have a table that is created from important information from the report. This report does include the ReportNum along with some other info.

[June7]

You are assigning ID number for each report? Don't think ever seen this. What if you want to reprint the report? Don't want to generate a new ID just for reprint. So the real trick is determining when and where to run code that generates ID.

[cfobare79]

The Reports are being saved as pdf. The table is so that the pdf can be searched more easily.

[June7]

Report to PDF is one way to go. In my database, a report can be reprinted just be re-rendering the report in Access with the same filter criteria.

Regardless, when to generate the unique ID and commit record is still issue. Perhaps in the code behind form that opens report or in the report Close event.

[cfobare79]

I'm trying to figure out how to use the link you sent me. I'm not even sure if I should be using both parts. I have a Form called "Filter" that has a button that creates the report/(invoice). The invoice number(ReportNum) appears as ITS-ESC-[AgencyID]-0001. The table that the ReportNum is loaded into and should be reference is called "Report". In the invoice the ReportNum is located in [text137]. Please help

[June7]

First, I would not build invoice numbers that use other data in its structure. AgencyID is agency being billed?

The GetNewLabNumber() function is the code that actually generates the new ID. The other procedure is example of calling the function. Adapt and use as appropriate for your situation.