Need help with this code please

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi,

I have this frmpayment boubded to tblA. on this for i
have a feild called ReciptNumber. I want to use the code
below to generate these recipt numbers but I have to
define the first number so that the code can increment
it. We buy these recipt books from outside and each time
we buy one it contains a different 7 digits number. I
want to modify the code to take the first number entered
as ReceiptNumber and the increment it and so on. I think
defining a varibale should be one way to approach this
but given my migger knowledge of VB I don't knoe how to
do that. Please help resolving this issue with your
help/suggestion/recommendation or any available code that
does this trick. I thank you in advance for you rhelp.

here is the code that I have used for a different reason
in a different database:

Private Sub Form_Load()
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
Call PrductListReq
End Sub
 
Graham,


All I want to do is to have or have the code generate a
recipt number based on the numbers in our book. We
receive payments from customers and we give them a recipt
which has amount, name and a recipt number on it. Three
employee receive payments and issue recipts. At the end
of the day one enters all those recipts into this data
base and we want the recipt number on the data entry form
to be consistant withthe numbers on the actual paper
recipts.

But, at the end of the month when we buy another book,
that book has different series of recipt numbers on the
recipts in it. We want to be able to enter the frist
recipt number and then the code generate the next one
when the next recors is added or entered.

I hope that I have been able to explain the reason for
this. is there any other way or better way to do this?

Regards,

Mike
-----Original Message-----
Mike,

Why exactly do you want to do this? Do you have several invoices without
numbers, or are you simply wanting to create an invoice number every time
you create an invoice?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html


Mike said:
Hi,

I have this frmpayment boubded to tblA. on this for i
have a feild called ReciptNumber. I want to use the code
below to generate these recipt numbers but I have to
define the first number so that the code can increment
it. We buy these recipt books from outside and each time
we buy one it contains a different 7 digits number. I
want to modify the code to take the first number entered
as ReceiptNumber and the increment it and so on. I think
defining a varibale should be one way to approach this
but given my migger knowledge of VB I don't knoe how to
do that. Please help resolving this issue with your
help/suggestion/recommendation or any available code that
does this trick. I thank you in advance for you rhelp.

here is the code that I have used for a different reason
in a different database:

Private Sub Form_Load()
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
Call PrductListReq
End Sub
Click to expand...


.
Click to expand...
 
Mike,

Sorry for taking so long to get back to you. I had uni exams all day
yesterday.

I can see one danger; that being if you have receipt numbers to enter AFTER
you get the new book. If that's not a problem, then I'd create a new table,
maybe called tblPreferences. This table should have one field - LastInvNo,
which I'll assume is to be a Long Integer.

I would then add the following to the form's ReceiptNo textbox's
DefaultValue property:
=Nz(DLookup("[LastInvNo]", "tblPreferences"), 0) + 1

I would then create the following function, which you can probably put in
the form's class module:
Public Function UpdateInvNo(lngValue As Long)
On Error Resume Next

CurrentDb.Execute "UPDATE tblPreferences SET LastInvNo = " &
lngValue, dbFailOnError

If Err.Number <> 0 Then
DoCmd.Beep
MsgBox Err.Number & vbCrLf & Err.Description
End If
End Function

Lastly, place the following into the form's AfterUpdate and AfterInsert
properties (- the properties, not the event procedures):
=UpdateInv(Forms!frmMyForm!txtInvoiceNo)

However, if you're using a continuous form to enter invoices, put the above
code into the InvoiceNo textbox's AfterUpdate property - not in the form
properties.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Mike said:
Graham,


All I want to do is to have or have the code generate a
recipt number based on the numbers in our book. We
receive payments from customers and we give them a recipt
which has amount, name and a recipt number on it. Three
employee receive payments and issue recipts. At the end
of the day one enters all those recipts into this data
base and we want the recipt number on the data entry form
to be consistant withthe numbers on the actual paper
recipts.

But, at the end of the month when we buy another book,
that book has different series of recipt numbers on the
recipts in it. We want to be able to enter the frist
recipt number and then the code generate the next one
when the next recors is added or entered.

I hope that I have been able to explain the reason for
this. is there any other way or better way to do this?

Regards,

Mike
-----Original Message-----
Mike,

Why exactly do you want to do this? Do you have several invoices without
numbers, or are you simply wanting to create an invoice number every time
you create an invoice?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html


Mike said:
Hi,

I have this frmpayment boubded to tblA. on this for i
have a feild called ReciptNumber. I want to use the code
below to generate these recipt numbers but I have to
define the first number so that the code can increment
it. We buy these recipt books from outside and each time
we buy one it contains a different 7 digits number. I
want to modify the code to take the first number entered
as ReceiptNumber and the increment it and so on. I think
defining a varibale should be one way to approach this
but given my migger knowledge of VB I don't knoe how to
do that. Please help resolving this issue with your
help/suggestion/recommendation or any available code that
does this trick. I thank you in advance for you rhelp.

here is the code that I have used for a different reason
in a different database:

Private Sub Form_Load()
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
Call PrductListReq
End Sub
Click to expand...


.
Click to expand...
Click to expand...
 
hey no problem, I hope you exam was fine. I am following
your direction but when I put the Function in the Form's
Class module ( Iasssume is the module that I have access
create codes for the svae, add record, report preview,
etc. is) but the follwing line gets "Red"

CurrentDb.Execute "UPDATE tblPreferences SET LastInvNo
= " &

Any thoughts??

I know its my VB understanding!!

Regards,

Mike
-----Original Message-----
Mike,

Sorry for taking so long to get back to you. I had uni exams all day
yesterday.

I can see one danger; that being if you have receipt numbers to enter AFTER
you get the new book. If that's not a problem, then I'd create a new table,
maybe called tblPreferences. This table should have one field - LastInvNo,
which I'll assume is to be a Long Integer.

I would then add the following to the form's ReceiptNo textbox's
DefaultValue property:
=Nz(DLookup("[LastInvNo]", "tblPreferences"), 0) + 1

I would then create the following function, which you can probably put in
the form's class module:
Public Function UpdateInvNo(lngValue As Long)
On Error Resume Next

CurrentDb.Execute "UPDATE tblPreferences SET LastInvNo = " &
lngValue, dbFailOnError

If Err.Number <> 0 Then
DoCmd.Beep
MsgBox Err.Number & vbCrLf & Err.Description
End If
End Function

Lastly, place the following into the form's AfterUpdate and AfterInsert
properties (- the properties, not the event procedures):
=UpdateInv(Forms!frmMyForm!txtInvoiceNo)

However, if you're using a continuous form to enter invoices, put the above
code into the InvoiceNo textbox's AfterUpdate property - not in the form
properties.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html


Mike said:
Graham,


All I want to do is to have or have the code generate a
recipt number based on the numbers in our book. We
receive payments from customers and we give them a recipt
which has amount, name and a recipt number on it. Three
employee receive payments and issue recipts. At the end
of the day one enters all those recipts into this data
base and we want the recipt number on the data entry form
to be consistant withthe numbers on the actual paper
recipts.

But, at the end of the month when we buy another book,
that book has different series of recipt numbers on the
recipts in it. We want to be able to enter the frist
recipt number and then the code generate the next one
when the next recors is added or entered.

I hope that I have been able to explain the reason for
this. is there any other way or better way to do this?

Regards,

Mike
-----Original Message-----
Mike,

Why exactly do you want to do this? Do you have
Click to expand...
several
invoices without
numbers, or are you simply wanting to create an
Click to expand...
invoice
number every time
you create an invoice?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html


Hi,

I have this frmpayment boubded to tblA. on this for i
have a feild called ReciptNumber. I want to use the code
below to generate these recipt numbers but I have to
define the first number so that the code can increment
it. We buy these recipt books from outside and each time
we buy one it contains a different 7 digits number. I
want to modify the code to take the first number entered
as ReceiptNumber and the increment it and so on. I think
defining a varibale should be one way to approach this
but given my migger knowledge of VB I don't knoe how to
do that. Please help resolving this issue with your
help/suggestion/recommendation or any available code that
does this trick. I thank you in advance for you rhelp.

here is the code that I have used for a different reason
in a different database:

Private Sub Form_Load()
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
Call PrductListReq
End Sub


.
Click to expand...
Click to expand...


.
Click to expand...
 
ok, i PUT A dOUBLE "" and the red went a way and I get
no erros but it does not incremnet. I added a record and
gave it a recipte Number 57648 and added the second
record and it gave me a "1" as the ReciptNumber not the
next increment of 57648!

Please advise.

Regards,

Mike
-----Original Message-----
Mike,

Sorry for taking so long to get back to you. I had uni exams all day
yesterday.

I can see one danger; that being if you have receipt numbers to enter AFTER
you get the new book. If that's not a problem, then I'd create a new table,
maybe called tblPreferences. This table should have one field - LastInvNo,
which I'll assume is to be a Long Integer.

I would then add the following to the form's ReceiptNo textbox's
DefaultValue property:
=Nz(DLookup("[LastInvNo]", "tblPreferences"), 0) + 1

I would then create the following function, which you can probably put in
the form's class module:
Public Function UpdateInvNo(lngValue As Long)
On Error Resume Next

CurrentDb.Execute "UPDATE tblPreferences SET LastInvNo = " &
lngValue, dbFailOnError

If Err.Number <> 0 Then
DoCmd.Beep
MsgBox Err.Number & vbCrLf & Err.Description
End If
End Function

Lastly, place the following into the form's AfterUpdate and AfterInsert
properties (- the properties, not the event procedures):
=UpdateInv(Forms!frmMyForm!txtInvoiceNo)

However, if you're using a continuous form to enter invoices, put the above
code into the InvoiceNo textbox's AfterUpdate property - not in the form
properties.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html


Mike said:
Graham,


All I want to do is to have or have the code generate a
recipt number based on the numbers in our book. We
receive payments from customers and we give them a recipt
which has amount, name and a recipt number on it. Three
employee receive payments and issue recipts. At the end
of the day one enters all those recipts into this data
base and we want the recipt number on the data entry form
to be consistant withthe numbers on the actual paper
recipts.

But, at the end of the month when we buy another book,
that book has different series of recipt numbers on the
recipts in it. We want to be able to enter the frist
recipt number and then the code generate the next one
when the next recors is added or entered.

I hope that I have been able to explain the reason for
this. is there any other way or better way to do this?

Regards,

Mike
-----Original Message-----
Mike,

Why exactly do you want to do this? Do you have
Click to expand...
several
invoices without
numbers, or are you simply wanting to create an
Click to expand...
invoice
number every time
you create an invoice?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html


Hi,

I have this frmpayment boubded to tblA. on this for i
have a feild called ReciptNumber. I want to use the code
below to generate these recipt numbers but I have to
define the first number so that the code can increment
it. We buy these recipt books from outside and each time
we buy one it contains a different 7 digits number. I
want to modify the code to take the first number entered
as ReceiptNumber and the increment it and so on. I think
defining a varibale should be one way to approach this
but given my migger knowledge of VB I don't knoe how to
do that. Please help resolving this issue with your
help/suggestion/recommendation or any available code that
does this trick. I thank you in advance for you rhelp.

here is the code that I have used for a different reason
in a different database:

Private Sub Form_Load()
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
Call PrductListReq
End Sub


.
Click to expand...
Click to expand...


.
Click to expand...
 
Mike,

CurrentDb.Execute "UPDATE tblPreferences SET LastInvNo = " & _
lngValue, dbFailOnError

....should work without double-double quotes.

You say it doesn't increment. You should check its value at every stage.
What does the textbox say when you first open the form, or change to a new
record? Have you traced the UpdateInvNo() code step-by-step to see what it's
doing?

Although I'm sure you must have doe this already, you have to change all the
object names to reflect those that you have.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


[email protected] said:
ok, i PUT A dOUBLE "" and the red went a way and I get
no erros but it does not incremnet. I added a record and
gave it a recipte Number 57648 and added the second
record and it gave me a "1" as the ReciptNumber not the
next increment of 57648!

Please advise.

Regards,

Mike
-----Original Message-----
Mike,

Sorry for taking so long to get back to you. I had uni exams all day
yesterday.

I can see one danger; that being if you have receipt numbers to enter AFTER
you get the new book. If that's not a problem, then I'd create a new table,
maybe called tblPreferences. This table should have one field - LastInvNo,
which I'll assume is to be a Long Integer.

I would then add the following to the form's ReceiptNo textbox's
DefaultValue property:
=Nz(DLookup("[LastInvNo]", "tblPreferences"), 0) + 1

I would then create the following function, which you can probably put in
the form's class module:
Public Function UpdateInvNo(lngValue As Long)
On Error Resume Next

CurrentDb.Execute "UPDATE tblPreferences SET LastInvNo = " &
lngValue, dbFailOnError

If Err.Number <> 0 Then
DoCmd.Beep
MsgBox Err.Number & vbCrLf & Err.Description
End If
End Function

Lastly, place the following into the form's AfterUpdate and AfterInsert
properties (- the properties, not the event procedures):
=UpdateInv(Forms!frmMyForm!txtInvoiceNo)

However, if you're using a continuous form to enter invoices, put the above
code into the InvoiceNo textbox's AfterUpdate property - not in the form
properties.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html


Mike said:
Graham,


All I want to do is to have or have the code generate a
recipt number based on the numbers in our book. We
receive payments from customers and we give them a recipt
which has amount, name and a recipt number on it. Three
employee receive payments and issue recipts. At the end
of the day one enters all those recipts into this data
base and we want the recipt number on the data entry form
to be consistant withthe numbers on the actual paper
recipts.

But, at the end of the month when we buy another book,
that book has different series of recipt numbers on the
recipts in it. We want to be able to enter the frist
recipt number and then the code generate the next one
when the next recors is added or entered.

I hope that I have been able to explain the reason for
this. is there any other way or better way to do this?

Regards,

Mike

-----Original Message-----
Mike,

Why exactly do you want to do this? Do you have several
invoices without
numbers, or are you simply wanting to create an invoice
number every time
you create an invoice?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-
0764559036.html


Hi,

I have this frmpayment boubded to tblA. on this for i
have a feild called ReciptNumber. I want to use the
code
below to generate these recipt numbers but I have to
define the first number so that the code can increment
it. We buy these recipt books from outside and each
time
we buy one it contains a different 7 digits number. I
want to modify the code to take the first number
entered
as ReceiptNumber and the increment it and so on. I
think
defining a varibale should be one way to approach this
but given my migger knowledge of VB I don't knoe how to
do that. Please help resolving this issue with your
help/suggestion/recommendation or any available code
that
does this trick. I thank you in advance for you rhelp.

here is the code that I have used for a different
reason
in a different database:

Private Sub Form_Load()
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
Call PrductListReq
End Sub


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top