Codes _ ineed help please

[Mike]

Hi,

I have been using this below code to generate invoice
numbers.

I have tblSeries and then tblInvoice in order to have
same generated number in tblseries to be written in
tblInvoice as well. I really don't know if this is a
right approach or not but I neeed some way beside the
AutoNumber to generate an Invoice Number automatically. I
have posted the code below and would really appreciate
your help and input. If the code is wrong or should there
be better way to do this. This invoice number will appear
on a form and ultimately on a report which is the invoice.


I have put it in the OnLoad Property of the frmInvoive.


Dim rst As Recordset, db As Database
Dim lngNextNumber As Long
Set db = CurrentDb

'Open tblSeries, lock, read next number, increment,
update and unlock.
Set rst = db.OpenRecordset("tblSeries", , dbDenyRead)
With rst
.MoveFirst
.Edit
lngNextNumber = ![NextNumber]
![NextNumber] = lngNextNumber + 1
.Update
Set rst = db.OpenRecordset("tblInvoice", , dbDenyRead)
With rst
..MoveFirst
..Edit
lngNextNumber = lngNextNumber
![InvoiceNumber] = lngNextNumber + 1
.AddNew
![InvoiceNumber] = lngNextNumber + 1
..Update

End With
End With
rst.Close
Set db = Nothing

End Sub

Regards,

Mike

 

[John Vinson]

Hi,

I have been using this below code to generate invoice
numbers.

I have tblSeries and then tblInvoice in order to have
same generated number in tblseries to be written in
tblInvoice as well. I really don't know if this is a
right approach or not but I neeed some way beside the
AutoNumber to generate an Invoice Number automatically. I
have posted the code below and would really appreciate
your help and input. If the code is wrong or should there
be better way to do this. This invoice number will appear
on a form and ultimately on a report which is the invoice.


I have put it in the OnLoad Property of the frmInvoive.

I'd use the BeforeInsert property instead. The OnLoad will work only
once every time you open the form.

Dim rst As Recordset, db As Database
Dim lngNextNumber As Long
Set db = CurrentDb

'Open tblSeries, lock, read next number, increment,
update and unlock.
Set rst = db.OpenRecordset("tblSeries", , dbDenyRead)
With rst
.MoveFirst
.Edit
lngNextNumber = ![NextNumber]
![NextNumber] = lngNextNumber + 1
.Update
Set rst = db.OpenRecordset("tblInvoice", , dbDenyRead)
With rst
.MoveFirst
.Edit
lngNextNumber = lngNextNumber
![InvoiceNumber] = lngNextNumber + 1

This is obviously wrong! It will open tblInvoice; open the very first
invoice in the table for editing; and set its InvoiceNumber to one
more than lngNextNumber!

.AddNew
![InvoiceNumber] = lngNextNumber + 1
.Update

This is a bit better. I'd lose the .Edit, the totally meaningless line
setting lngNextNumber to itself, and the first !InvoiceNumber = line.

 

[Mike]

Thanks John,

So it must be like this:

Dim rst As Recordset, db As Database
Dim lngNextNumber As Long
Set db = CurrentDb

'Open tblSeries, lock, read next number, increment,

update and unlock.
Set rst = db.OpenRecordset("tblSeries", , dbDenyRead)
With rst
.MoveFirst
.Edit
lngNextNumber = ![NextNumber]
![NextNumber] = lngNextNumber + 1
.Update
Set rst = db.OpenRecordset("tblInvoice", , dbDenyRead)
With rst
..MoveFirst

.AddNew
![InvoiceNumber] = lngNextNumber + 1
..Update

End With
End With
Set db = Nothing
End Sub

Please let me know and thanks again.

Regards,

Mike

-----Original Message-----

Hi,

I have been using this below code to generate invoice
numbers.

I have tblSeries and then tblInvoice in order to have
same generated number in tblseries to be written in
tblInvoice as well. I really don't know if this is a
right approach or not but I neeed some way beside the
AutoNumber to generate an Invoice Number automatically. I
have posted the code below and would really appreciate
your help and input. If the code is wrong or should there
be better way to do this. This invoice number will appear
on a form and ultimately on a report which is the invoice.


I have put it in the OnLoad Property of the frmInvoive.

I'd use the BeforeInsert property instead. The OnLoad will work only
once every time you open the form.

Dim rst As Recordset, db As Database
Dim lngNextNumber As Long
Set db = CurrentDb

'Open tblSeries, lock, read next number, increment,
update and unlock.
Set rst = db.OpenRecordset("tblSeries", , dbDenyRead)
With rst
.MoveFirst
.Edit
lngNextNumber = ![NextNumber]
![NextNumber] = lngNextNumber + 1
.Update
Set rst = db.OpenRecordset("tblInvoice", , dbDenyRead)
With rst
.MoveFirst
.Edit
lngNextNumber = lngNextNumber
![InvoiceNumber] = lngNextNumber + 1

This is obviously wrong! It will open tblInvoice; open the very first
invoice in the table for editing; and set its InvoiceNumber to one
more than lngNextNumber!

.AddNew
![InvoiceNumber] = lngNextNumber + 1
.Update

This is a bit better. I'd lose the .Edit, the totally meaningless line
setting lngNextNumber to itself, and the first ! InvoiceNumber = line.

End With
End With
rst.Close
Set db = Nothing

End Sub

Regards,

Mike

.

 

[Mike]

John,

As soon as I want to enter data into the form, the
program halts and the .MoveFirst is Highlighted. I mean
the codes that I just sent you in my post reply.

I also put the code in the BeforeInsert Property of the
Form.

Any thoughts??

Thanks,

Mike

-----Original Message-----

Hi,

I have been using this below code to generate invoice
numbers.

I have tblSeries and then tblInvoice in order to have
same generated number in tblseries to be written in
tblInvoice as well. I really don't know if this is a
right approach or not but I neeed some way beside the
AutoNumber to generate an Invoice Number automatically. I
have posted the code below and would really appreciate
your help and input. If the code is wrong or should there
be better way to do this. This invoice number will appear
on a form and ultimately on a report which is the invoice.


I have put it in the OnLoad Property of the frmInvoive.

I'd use the BeforeInsert property instead. The OnLoad will work only
once every time you open the form.

Dim rst As Recordset, db As Database
Dim lngNextNumber As Long
Set db = CurrentDb

'Open tblSeries, lock, read next number, increment,
update and unlock.
Set rst = db.OpenRecordset("tblSeries", , dbDenyRead)
With rst
.MoveFirst
.Edit
lngNextNumber = ![NextNumber]
![NextNumber] = lngNextNumber + 1
.Update
Set rst = db.OpenRecordset("tblInvoice", , dbDenyRead)
With rst
.MoveFirst
.Edit
lngNextNumber = lngNextNumber
![InvoiceNumber] = lngNextNumber + 1

This is obviously wrong! It will open tblInvoice; open the very first
invoice in the table for editing; and set its InvoiceNumber to one
more than lngNextNumber!

.AddNew
![InvoiceNumber] = lngNextNumber + 1
.Update

This is a bit better. I'd lose the .Edit, the totally meaningless line
setting lngNextNumber to itself, and the first ! InvoiceNumber = line.

End With
End With
rst.Close
Set db = Nothing

End Sub

Regards,

Mike

.

 

[John Vinson]

As soon as I want to enter data into the form, the
program halts and the .MoveFirst is Highlighted. I mean
the codes that I just sent you in my post reply.

I also put the code in the BeforeInsert Property of the
Form.

Any thoughts??

If all you want to do is add a new record there's no reason to
MoveFirst. Just remove that line.

 

[SteveS]

Mike,

Try this code. I threw together a form and two tables. It seems to work.
NOTE: I used the form BeforeUpdate instead if beforeInsert because if
you decide to cancel the invoice, you won't have a missing invoice
number. But this code should work fine in the BeforeInsert event.


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rst As Recordset, db As Database
Dim lngNextNumber As Long
Set db = CurrentDb

'Open tblSeries, lock, read next number, increment, update and unlock.
Set rst = db.OpenRecordset("tblSeries", , dbDenyRead)
With rst
.MoveFirst
lngNextNumber = ![NextNumber]
.Edit
![NextNumber] = lngNextNumber + 1
.Update
End With
rst.Close
Set rst = Nothing
Set db = Nothing
' set the control on the form to the invoice number
' if the control is not named InvoiceNumber change
' Me.InvoiceNumber to the name of your control
Me.InvoiceNumber = lngNextNumber
End Sub


HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

John,

As soon as I want to enter data into the form, the
program halts and the .MoveFirst is Highlighted. I mean
the codes that I just sent you in my post reply.

I also put the code in the BeforeInsert Property of the
Form.

Any thoughts??

Thanks,

Mike

-----Original Message-----
On Wed, 7 Jan 2004 20:29:56 -0800, "Mike"

I

there

appear

invoice.


I'd use the BeforeInsert property instead. The OnLoad

will work only

once every time you open the form.

Dim rst As Recordset, db As Database
Dim lngNextNumber As Long
Set db = CurrentDb

'Open tblSeries, lock, read next number, increment,
update and unlock.
Set rst = db.OpenRecordset("tblSeries", , dbDenyRead)
With rst
.MoveFirst
.Edit
lngNextNumber = ![NextNumber]
![NextNumber] = lngNextNumber + 1
.Update
Set rst = db.OpenRecordset("tblInvoice", , dbDenyRead)
With rst
.MoveFirst
.Edit
lngNextNumber = lngNextNumber
![InvoiceNumber] = lngNextNumber + 1

This is obviously wrong! It will open tblInvoice; open

the very first

invoice in the table for editing; and set its

InvoiceNumber to one

more than lngNextNumber!

.AddNew
![InvoiceNumber] = lngNextNumber + 1
.Update

This is a bit better. I'd lose the .Edit, the totally

meaningless line

setting lngNextNumber to itself, and the first !

InvoiceNumber = line.

.