Adding Invoice Number to Reports 1

[Tash]

I need advice for a Shipping and Invoice database I have created. I have tables for the shipping data, vendors, location, Purchase Orders, etc. Then it all comes together by date range (they do invoices once a week)and location/vendor in a Invoice I created in Reports.

The problem I am having now is that we need to auto generate an Invoice number for this group of records that will be put back into a table for future reference. How can I do this since the report is not actually linked together except by date range?

Thanks in advance for any help!

 

[MajP]

Why not some calculated field of "start date" "end date" and "vendor location"? That sounds like you would have all the data needed to recreate the query.

 

[Tash]

That would be a great idea, however, I guess they need the Invoice numbers to begin at a specific number, namely 90,000 and up.

 

[MajP]

What about this idea. Your invoice is built on some group of criteria. Lets say an invoice can be generated by a vendor ID, a start Date, and a end Date. As your invoice opens up you could write to a table "tblInvoices"

tblInvoices
autoInvoiceID (start the auto number at 90,000)
strVendorID
dtmStartDate
dtmEndDate

If the vendorID, startDate and endDate uniquely define an invoice query then make sure to make them a compound index that must be unique.
Now you can look up an index, and use its fields as parameters for a query. If this approach makes sense, I can write you some code to write to the table and load from the table.

I do something a little similar. I have a list of organizations (several thousand). The user needs to make rosters from these list. He gives the roster a name and clicks on all the organizations he needs. To save the roster I export the roster name and the id's of the organizations selected. Then to load a roster, I read the Id's that match a specific roster name and by code select them from the list.

 

[Tash]

That is a GREAT idea! I was about to give up on this idea, but your post makes perfect sense. I would appreciate the code to write to the table, too - I think this would be an obstacle for me, since I am pretty new to writing code. Thanks MajP!

 

[ClydeData]

Try this sample fro Rogers access Library

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Next Number (NextNum.mdb)'

It will enable you to start your invoice number whereever you want

Hope this helps

Jimmy

 

[MajP]

You will have to give me some details of how you want it to work. For example, does the user start from a form where he/she selects the vendor, start, stop dates, and a report is built. In my roster example I start with a form with a tree view. The user checks the units he wants. When he closes the form it prompts him if he wants to save the roster. He gives the roster a unique name, and the unit primary key are saved to a table (tblImportExport). When they open the application the user is prompted if they want to open a saved roster. They pick the roster name, and I read through the table checking the treeview with the appropriate units.
I also need relevant table names, and names of fields that are the primary keys and foriegn keys. Depending on your buisness model, it may not make sense to save the parameters of the query that define an inventory. You may need to save primary keys. For example lets say an invoice is uniquely defined by a vendor, start date, and end date. If after the invoice goes out someone tries to cover their butt after the fact by adding a record that meets this criteria. Then it would appear as if it was part of the invoice next time I load the query. It is dynamic so there is no real "history". If you save the primary keys of the relevant tables instead of parameters of a query, you would have a real "snapshot" in time. If you go with primary keys instead of paramaters the table will grow very quickly, but it is only primary keys. Instead of one record per invoice, you will have a record in this table for each record in your invoice.
Provide the details. I can help you with the solution. I think either one of these is a correct approach.
A modification of the first approach is to save the actual sql string that makes up the invoice instead of the parameters in the invoice table. So all you have is two columns:

tblInvoice
autoInvoiceID
strInvoiceSQL

 

[Tash]

Thanks so much for your ideas, I really appreciate your input and help with this. The user starts from a Pop Up form and selects the store, start date and end date, then clicks on a button to view the Report/Invoice. I see what you mean about linking to a primary key instead of the data. That would be a good way to safeguard the information so that somebody doesn’t go back in and try to add the Invoice number in once it’s been posted. I think I would add an additional button on this Pop Up form that would link to the code so that they could manually post the Invoice Number and then open up the report with the existing button.

These are the fields for the pop up window:
[Forms]![FormPrintInvoice]![Enterstore]
[Forms]![FormPrintInvoice]![EnterBeginningDate] [Forms]![FormPrintInvoice]![EnterEndDate]

The query for the Report/Invoice is based off these 3 tables (with TableDeliveryInformation being the main table with Primary Key)
TableDeliveryInformation- Primary Key name is [ContainerID]
TableStore - [TableDeliveryInformation]![ContainerDestination] linked to [TableStore]![StoreName]
TableConatinerContents - [TableConatinerContents]![ContainerContentsID] linked to [TableDeliveryInformation]![ContainerID]

Besides these tables, I would have to add in another table to capture the Invoice number and primary keys? Again, I really appreciate your help and direction with this!

 

[MajP]

This may be simpler than I suggested. Each Container is unique, and I think it can only be part of one invoice.
Could table tableDeliveryInformation have an Invoice ID field (a foriegn key)? You could do your above query (also checking that the containers are not assigned to an invoice already), and then prompt the users to create the invoice. Then in code each Container then gets the same Invoice ID, (basically a foriegn key) that is pulled from the Invoice table "tblInvoice". tblInvoice has these fields.

tblInvoice
autoInvoiceID
storeID
dtmStartDate
dtmEndDate
others

 

[MajP]

If the above makes sense then. You could have a bound form that is bound to the tblInvoice and it opens in add record. This form could have a subform. On the form is the fields you mentioned: Store, Start, End, and the Invoice ID.
In the before update event, it will populate the subform with the results of the query showing all containers in the invoice, then prompt the user to save the invoice. If the user says no, the update event is cancelled. If the user says yes, a record is added to the invoice table, and then the invoice ID is inserted into tblDeliveryInformation for this group of records returned by the query..
You can then use this same form to view other invoices.

 

[Tash]

Yes, a container can only be part of one Invoice, although an Invoice may contain several containers. So all I would have to do is to prompt the user to create an Invoice number, then use the code to put the Invoice # from TblInvoice into the TableDeliveryInformation table. Ok, I am beginning to see the light! The only thing I am wondering, is if a person repeated the procedure, how could I avoid re-issuing another Invoice number? I guess I would have to just check to see if the field was already filled before proceeding to insert the Invoice#.

 

[MajP]

Once you build table invoice run this code.

Public Sub setAutoID()
CurrentDb.Execute "ALTER TABLE tblInvoice ALTER COLUMN autoInvoiceID COUNTER(90000,1)"
End Sub

This will set the "autoInvoiceID" to start at 90000.

 

[Tash]

Ok, I think I got it, so actually a very simple procedure with limited code building. WOW! That's great. Thank you so much for your help! I'll have to try it out this evening and see if I can get this all to work, but you have solved the hardest part for me already. Thanks again!

 

[MajP]

Really all you have is a traditional one to many relationship. The twist is that normally you build the "parent" first and then assign "child" records. Here is the opposite. You build all of the containers first, then figure out what the parent Invoice will look like. Then you assign the children containers to the parent invoice.

 

[Tash]

Thanks, I just saw the code to start my AutoID at 90,000!

 

[Tash]

Hi again -

I've had some time to work on some of the solutions listed above and I'm just running into one problem. The code to start the Auto ID at 90,000 works beautifully, by the way - thank you!

I have a bound form that is bound to the tblInvoice and it opens in add record. I then have a subform that populates with the containers that resulted from the query. The part I am not sure of, which is a result from my limited programming experience, is getting the tblInvoice number to the selected records. I've been using the "setvalue" function in the Macro, and it only updates the first record in the subform, but not the rest of the set. I've tried it with code ([Form2].Form![InvoiceNo] = Me.FinalInvoice) but have had mixed errors with it.

Thanks again for all your help!

 

[MajP]

There are many ways to do this. The cleanest would probably be to use an update query. Since I do not have all the information on how you build your query and how your form works, I looped it instead.
Here are my assumptions. You have a form that you add Invoice information. Somehow you can generate records in the subform that you want associated to the Invoice.
For my example I have button on the main form that assigns the records in the subform to the invoice on the main form. I have "tblInvoice" and "tblDeliveryInformation". tblInvoice has a primary key "autoInvoiceID" and tblDeliveryInformation has a foriegn key "intFkeyInvoiceID". My main form has a sub form control called "subFormDeliveryInformation"

Private Sub cmdAssign_Click()
  Dim intInvoiceID As Integer
  Dim rsRecordsToAssign As DAO.Recordset
  If Not IsNull(Me.autoInvoiceID) Then
    intInvoiceID = Me.autoInvoiceID
    If MsgBox("Are you sure you want to assign the Records in the Sub Form to Invoice " & intInvoiceID & "?", vbYesNo) = vbYes Then
      Set rsRecordsToAssign = Me.subFrmDeliveryInformation.Form.Recordset
      Do While Not rsRecordsToAssign.EOF
          rsRecordsToAssign.Edit
          rsRecordsToAssign.Fields("intFkeyInvoiceID") = intInvoiceID
          rsRecordsToAssign.Update
          rsRecordsToAssign.MoveNext
      Loop
      msgBox "Records Assigned"
    End If
  Else
    MsgBox "Must add Invoice information to generate new Invoice ID"
  End If
End Sub

 

[Tash]

Thanks for the code, it works great, however I am getting a Run Time error 6, Overflow when I run the procedure on my Invoice number which begins at 90,000. It only seems to work if I have a number that is below 30,000. Not sure why?

 

[Tash]

Ok, ignore the last post I made. I had to change the "integer" in the code to "Long" so that it would accept the higher number. It is working perfectly, now! Thanks so much for your extensive help - you have gotten me through a huge hurdle on my database!

 

[MajP]

Cool.