Update to 2 tables (1 Viewer)

[Mclaren]

I have 4 tables :

tbl_Quotes
tbl_Quote_Details
tbl_Invoices
tbl_Invoice_Details

I also have 4 forms, 2 mains and two subs for the above tables.
I want to create a button (Create Invoice) on the main Quotes form that automatically updates all the records for the quote and quote details to the invoice and invoice details tables.

 

[MarkK]

I presume the data structure of your quote and your invoice are almost identical. If so there is no need to have separate tables for quotes and invoices. Just have a single field called IsInvoiced and set it to true. *Poof* the item is not a quote anymore.

 

[Khalid_Afridi]

Did you normalized your database properly? did you create relationship between your tables? are you using the concept of primary and foreign keys?

 

[Mclaren]

yes i have normalised the DB, primary keys and foreing kkeys, the whole shenbang.

why i cannot use one table is because the quote No's and invoice No's must run in sequence and are not always the same, ie I may issue a quote but not make the sale, or i may make a sale without a quote.

 

[Mclaren]

Also, yes the field are identicle except for a few extra fields on the invoice.

 

[MarkK]

I would run quote numbers and invoice numbers in sequence in the same table. When you create a quote give it the next highest available quote number. When you create an invoice give it the next highest available invoice number. This seems much simpler to me than moving the quote to an invoice table that has almost exactly the same structure.

 

[JANR]

I agree with Lagbolt, use the Dmax()+ 1 to get the next sequence number, something like this:

Private Sub MakeInvoice()
Dim InvoiceNumber As Long
Dim InvoiceDte As Date
Dim strSQL As String
 
InvoiceNumber = Dmax("InvoiceNo", "tbl_Quotes", "[QuoteNum]=" & [COLOR=red]Me!ControlQuoteOnForm[/COLOR][COLOR=black]) + 1
[/COLOR]InvoiceDte = Date [COLOR=lime] [/COLOR][COLOR=orange]'Current date[/COLOR]
 
strSQL = ""
strSQL = strSQL & " UPDATE tblQuote SET InvoiceNum = InviceNumber, InvoiceDate = InvoiceDte"
strSQL = strSQL & " WHERE QuoteNum =" & Me!ControlQuoteOnForm & ";"
 
Currentdb.Execute strSQL, dbFailOnError
 
End Sub

JR

 

[Mclaren]

One more problem with this solution :

What happens if a client only takes part of the quote ? or prices change between the quote date and the invoice date.

I need to ensure that once a quote is flagged as printed no alterations are made to prices etc. Should i then wish to change a price during invoicing this will mean the quote details change, unless i have seperate field in the same table for quote prices and invoices prices.

i would really prefer for the quotes and the invoices to be kept apart. Thus i can guarantee no changes to quote details once issued.

 

[JANR]

What happens if a client only takes part of the quote ?

Surly this is reflected in the Quotes_detail table, so any changes must be done BEFORE an invice is made.

I need to ensure that once a quote is flagged as printed no alterations are made to prices etc

To set the flag you could include a yes/no field in the table, or as the invoicenumber field is NOT NULL the set the invoiceforms AllowEdit property to false where invoicenumber is not null.

i would really prefer for the quotes and the invoices to be kept apart

If you still want to do this, then the easiest would be to create and Append query based on your qoute table and set the where clause to the qoutenumber you wish to transfer.

JR

 

[Mclaren]

Surly this is reflected in the Quotes_detail table, so any changes must be done BEFORE an invice is made.

Once a quote is printed a quote cannot be changed. Quotes must always reflect the original pricing as provided at time of quote. Should i need to reprint a quote for legal reasons, i will need to ensure the data is still what the client recieved. However, invoices can be adjusted after quoting to reflect new pricing or even negotiated prices between the customer and the vendor. This is why it is imparetive for quote data to be separate from invoice data.

To set the flag you could include a yes/no field in the table, or as the invoicenumber field is NOT NULL the set the invoiceforms AllowEdit property to false where invoicenumber is not null.

I already have the flag set and thus my quotes, once printed cannot be altered. Should i need to requote with new values (prior to the client accepting any offer of services) i would need to create a whole new quote with new pricing etc. Once a client has accepted the quote an invoice is generated, however a client may wish to add or delete items from the invoice or we may need to invoice the client for unforseen issues, this is where data may differ on quotes compared to invoices. this being said, I am going to test using one table for invoice and quote data and see if i can get it to work, as i really want to keep it as simple as possible and see the pros to keeping it all in one table. I am just a little cautious though.

If you still want to do this, then the easiest would be to create and Append query based on your qoute table and set the where clause to the qoutenumber you wish to transfer.

JR