Create an invoice from quotation

[Adam1970]

Hi


I currently have a quote table in access 2016 and am using the primary autonumber as the quote no. eg QU0001
I want to be able to convert the quote into an invoice & store the details in an invoice table and change from QU0001 to another auto number starting INV, it may not be 0001 as that may have already been taken.
Is there a way of doing this?
Sorry if it's confusing I'm more of an excel user but trying my hand at access.
Thanks
Adam

[Micron]

It's simple - do not use autonumbers for any meaningful data. You can relate ("link") whatever invoice number you want to give an invoice to the proper quotes by including the primary key field of the quote table as a related foreign key in the invoice table. The concept is covered in the study of db normalization, so if you're not familiar with it, that's something you should review.

[KNicholson]

Use VBA to create your next sequential number.
This is the invoice number displayed ; the autonumber invoice number is for your eyes only.

Here is some sample code from a command button I have on a continuous form to create a new invoice number:
(My invoice table has the id, date, and the status)

Dim mySQL As String
Dim dtToday As String, myNextSeq As String
Dim todayDate As Date
todayDate = CDate(Date)
dtToday = Format(Date, "YYYYMM")
myNextSeq = DMax("InvNum", "tblInv")
myNextSeq = Left(myNextSeq, 6)
If myNextSeq <> dtToday Then
myNextSeq = dtToday & "0001"
Else
myNextSeq = DMax("InvNum", "tblInv") + 1
End If
mySQL = "INSERT INTO tblInv (InvNum,InvDate,InvStat) VALUES (" & myNextSeq & ",#" & Format(todayDate, "mm\/dd\/yyyy") & "#,""In Process"")"
Debug.Print mySQL
DoCmd.SetWarnings False
DoCmd.RunSQL mySQL
DoCmd.SetWarnings True
Me.Requery
DoCmd.ShowAllRecords
DoCmd.GoToRecord , , acLast
End If

[dblife]

Personally, I can't see why this needs more tables unless one quote can turn into more than one order to be invoiced.
It's what they call a 'one to one' relationship and spreadsheets are fine for those types of relationships. Relational databases work best where 'one to many' or 'many to many' relationships exist.
I would just put some extra columns in the original table and record how, when and where that single quote turned into a single order to be invoiced?
But if you really want to use a db, and in case the above excellent explanations aren't simple enough, you could create a table for invoices.
Use an autonumber for the primary key in this new table if you wish but use the unique primary key from the quote table as a reference to the original quote. This is known as a foreign key. You can add extra columns as you see fit such as the person who made the sale, when it was made etc but don't copy the data across as this breaks what they call 'normalisation' rules and that's a big no-no that makes life very difficult and tedious further down the line.
You can then use a query to pull both tables together to get all the information about both quote and invoice.

[ArviLaanemets]

Personally, I can't see why this needs more tables unless one quote can turn into more than one order to be invoiced.

In real life, this will make the app very limited in case something is not going as planned!

A quote may not followed with an order at all (the quote was dropped off);
The may be an order without any quote at all (client is buying ad hoc);
The quantities/prices in order my not match with ones in quote for some reason;
Whatever was agreed in quote may be delivered in separate patches, and will have separate order for every delivery;
etc.

When you overwrite quote as order, even when there are no issues making this impossible, you will lose all history about your orders and quotes. And as orders are financial assets, there usually are very strict rules for them (E.g. when you need to correct an order, you can't delete it, or overwrite it - you have to add a special row instead.).

[CJ_London]

See similar threads at the bottom of this one. Almost exactly the same question asked