numbering a report for use of an invoice

  • Thread starter Thread starter fedum
  • Start date Start date
F

fedum

I would like to make an invoice with a report. How can I
get the invoice number on this report? I have a table with
the starting number and the last used number. Then I use a
query with all the fields that I use for this report. I
start with a form (not every form need to have an invoice)
if clicked on the button Invoice this report has to be
printed 2 times. The invoice number has to check the last
used number and add 1 to this number, put this number into
the table as last used number. The problem is how to read
the last used number into the report add 1 to it write
this number back into the table. I always get the
information that this field is not included.
Thanks,
Marc
 
I would like to make an invoice with a report. How can I
get the invoice number on this report? I have a table with
the starting number and the last used number. Then I use a
query with all the fields that I use for this report. I
start with a form (not every form need to have an invoice)
if clicked on the button Invoice this report has to be
printed 2 times. The invoice number has to check the last
used number and add 1 to this number, put this number into
the table as last used number. The problem is how to read
the last used number into the report add 1 to it write
this number back into the table. I always get the
information that this field is not included.
Thanks,
Marc
Click to expand...

You're talking about your reports and your forms - but what's the
structure of your invoice Table? You can't "read the last used number
into a report", in any practical way! You should have a Table
containing the information unique to your Invoice; you can use some
VBA code in the Form which you use to update this report. For
instance, in the Form's BeforeInsert event you can use code like:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iNext As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("LastUsedTable", dbOpenDynaset)
rs.MoveLast ' unless it's a one-record table, omit if so
iNext = rs!LastUsed + 1 ' or whatever your fieldname is
rs.Edit
rs!LastUsed = iNext
rs.Update
Me!txtInvoiceID = iNext
End Sub

THis will increment the value from the field LastUsed by one; store
that new value back into LastUsed, and store it as well in the textbox
txtInvoiceID on your form, which should be bound to the ID field in
your invoice table.
 
Thanks John for your reply.
The structure: I have a table with clients, a table with
the sellingfields (clientref, nr. doc, total cost etc), a
table with documentnumbers (I would like to store only the
last used number end the starting number), a table with
the sold articels). For the form I use these tables in a
query. Then have I made a seperate query for making the
invoice to use for my report.
Regards,
Marc
 
Thanks John for your reply.
The structure: I have a table with clients, a table with
the sellingfields (clientref, nr. doc, total cost etc), a
table with documentnumbers (I would like to store only the
last used number end the starting number), a table with
the sold articels). For the form I use these tables in a
query. Then have I made a seperate query for making the
invoice to use for my report.
Regards,
Marc
Click to expand...

Please post the SQL of this query. I'm not sure what you want can be
done using a single query, since you'll need a Select Query as the
recordsource for the Report, and an Update Query to update your
documentnumbers table. Perhaps you could use VBA code in the Report's
Open event, but since you're not recording the invoice number, it
becomes a real problem if you ever need to reprint one!
 
Well John I hope you will understand this sql of the
query. Some words are in Dutch but the structure stays.

SELECT tblVerkoopKassa.VerkoopKassaId, [Informatie over
mijn bedrijf].NaamBedrijf, [Informatie over mijn
bedrijf].Adres, [Informatie over mijn bedrijf]![Postcode]
& " " & [Informatie over mijn bedrijf]![Plaats] AS
GemeentePostcode, [Informatie over mijn
bedrijf].Telefoonnummer, [Informatie over mijn
bedrijf].Faxnummer, [Informatie over mijn
bedrijf].BTW_num, [Informatie over mijn bedrijf].HRH,
[Informatie over mijn bedrijf].e_mail, [Informatie over
mijn bedrijf].RekNr, [Informatie over mijn bedrijf].Logo,
[tblKlanten]![Naam] & " " & [tblKlanten]![Voornaam] AS
NaamKlant, [tblKlanten]![Straat] & " " & [tblKlanten]![Nr]
AS AdresKlant, [tblKlanten]![PostId] & " " & [tblKlanten]!
[Gemeente] AS GemeenteKlant, tblVerkoop.Omschrijving,
tblVerkoop.Stuks, tblVerkoop.Prijs, tblVerkoop.Korting,
tblVerkoopKassa.TotalePrijsIncl, tblVerkoopKassa.BTW,
tblVerkoopKassa.TotalePrijsExcl,
tblVerkoopKassa.Betalingswijze, tblVerkoopKassa.Faktuur,
tblVerkoopKassa.FaktuurNr,
tblNummeringDocumenten.BeginNrFactuur,
tblNummeringDocumenten.HuidigeNrFactuur,
tblNummeringDocumenten.BTW_Percentage,
Producten.PrijsPerEenheid, Producten.Productomschrijving
FROM [Informatie over mijn bedrijf],
tblNummeringDocumenten, (tblKlanten INNER JOIN
tblVerkoopKassa ON tblKlanten.Refklant =
tblVerkoopKassa.RefKlant) INNER JOIN (Producten INNER JOIN
tblVerkoop ON Producten.Serienummer = tblVerkoop.Barcode)
ON tblVerkoopKassa.VerkoopKassaId =
tblVerkoop.VerkoopKassaId
WHERE (((tblVerkoopKassa.Faktuur)<>0));

BeginNrFaktuur = StartingNumber
HuidigeNrFaktuur = CurrentNumber

If clicked on de button Invoice (Faktuur) then the field
Faktuur (boolean) gets True.
I would like to save the used number in the tabel tblKassa
but only if it is used. And that is my problem.
Hope you can help.
Thanks
Marc
 
Back
Top