Back in Dec 03 I was working on an app which included invoicing.
With some help from Allen Browne I got something that has worked with no
probs.
I modifyed the origional app last March to handle a milk processing business
I am involved in.
Again this has been fine with invoice number over 500.
Last week it started producing duplicate invoice number.
The invoice table is correct but It appears to repeat the customer`s last
invoice number in the invoicedetail table.
I have not altered anything for over 2 months.
The setup on asking for an invoice is
1 Grap the details from invoice transactions table and put them in invoice
detail table
2 create a record or records in the invoicetable and the autonumber field
creates the invoice number
3 pick up the invoice number from the invoicetable and update the records
in invoicedetail with the relevant invoice number
The problem appears to be with 3
sql below
1 INSERT INTO invoicedetail ( Activitydate, Activityname, Activitydetail,
starttime, quantity, Priceeach, resourcegroup, customerid, batchid,
transactiontblid )
SELECT invtransactionsq.Activitydate, invtransactionsq.Activityname,
invtransactionsq.Activitydetail, invtransactionsq.starttime,
invtransactionsq.quantity, invtransactionsq.Priceeach,
invtransactionsq.resourcegroup, invtransactionsq.customerid, [Maxbatchid]+1
AS batchid, invtransactionsq.itemnumber
FROM batchidmax, invtransactionsq
WHERE (((invtransactionsq.Activitydate) Between
[Forms]![collectforinvoicefrm]![startdate] And
[Forms]![collectforinvoicefrm]![enddate]) AND
((invtransactionsq.quantity)<>0));
2 INSERT INTO invoicetable ( batchid, custid, invdate )
SELECT Max(invoicedetail.batchid) AS MaxOfbatchid, invoicedetail.customerid,
Date() AS Expr1
FROM invoicedetail
GROUP BY invoicedetail.customerid, Date(), invoicedetail.invoicenum
HAVING (((Max(invoicedetail.batchid))>0) AND
((invoicedetail.invoicenum)=0));
3 UPDATE invoicetable INNER JOIN invoicedetail ON invoicetable.custid =
invoicedetail.customerid SET invoicedetail.invoicenum = [invoicenumber]
WHERE (((invoicedetail.invoicenum)=0) AND ((invoicedetail.Activitydate)
Between [Forms]![collectforinvoicefrm]![startdate] And
[Forms]![collectforinvoicefrm]![enddate]));
Would be grateful for any thoughts on what is going wrong
TIA
David B
