sequencial number not autonumber

  • Thread starter Thread starter ljg08
  • Start date Start date
L

ljg08

Hi Guys,

Looking for help with this issue. I have an invoice form with a field
[invoice_num] and want, on save, to create a sequential number. If user
cancels the record any point before save I do not want to save the number to
ensure sequence for invoices is consistent.

Any suggestion would be most helpful.

TIA
 
Hi Bruce and thanks for that, However, I have tried this and not having any
sucess with this method, seems simple enough, but cannot get it to work.

I am putting this code just as it is in the example and substituting the
field and table names for my own, any suggestions as to why this would not
work for me?

Regards
Les

BruceM said:
This may be just what you need:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

Note that you can format the number if you want leading zeros. In the
text box Format property:
00000
will turn 1 in 00001; 101 will be 00101.

ljg08 said:
Hi Guys,

Looking for help with this issue. I have an invoice form with a field
[invoice_num] and want, on save, to create a sequential number. If user
cancels the record any point before save I do not want to save the number
to ensure sequence for invoices is consistent.

Any suggestion would be most helpful.

TIA
Click to expand...
Click to expand...
 
No idea without more information. Post the table structure, the expression
you are using to increment the number and where the expession resides, and
an explanation of the way in which it does not work (error message, nothing
at all, #Error in the text box, or whatever).

ljg08 said:
Hi Bruce and thanks for that, However, I have tried this and not having
any sucess with this method, seems simple enough, but cannot get it to
work.

I am putting this code just as it is in the example and substituting the
field and table names for my own, any suggestions as to why this would not
work for me?

Regards
Les

BruceM said:
This may be just what you need:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

Note that you can format the number if you want leading zeros. In the
text box Format property:
00000
will turn 1 in 00001; 101 will be 00101.

ljg08 said:
Hi Guys,

Looking for help with this issue. I have an invoice form with a field
[invoice_num] and want, on save, to create a sequential number. If user
cancels the record any point before save I do not want to save the
number to ensure sequence for invoices is consistent.

Any suggestion would be most helpful.

TIA
Click to expand...
Click to expand...
Click to expand...
 
Hi Bruce,

I have also found another method, which when I test with a simple form and
add a record it works, however, when I put it on my form, which is opened
from another form via :

Private Sub Command15_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "TempCustomerPriceCheck"
DoCmd.OpenForm "frmOrderPlacement", acNormal, "", "", acFormAdd,
acWindowNormal
Forms!frmOrderPlacement!accountID = accountID '.Value
Forms!frmOrderPlacement!txtcustID = CustID '.Value
Forms!frmOrderPlacement!txtaddress1 = Address2 '.Value
Forms!frmOrderPlacement!txtaddress2 = Address1 '.Value
Forms!frmOrderPlacement!txtaddress3 = Address3 '.Value '
Forms!frmOrderPlacement!txtName = CustName '.Value
Forms!frmOrderPlacement!txtpostcode = Post_Code '.Value
DoCmd.Close acForm, "frmAccountCustomers"
Forms!frmOrderPlacement.SetFocus
End Sub

I have added this code to the onCurrent event:

If Me.NewRecord Then
Me!invoice.DefaultValue = Nz(DMax("[invoice]", "orders"), 0) + 1
End If
Me.refUser = GetUsrFullName
End Sub

Any suggestions would be appreciated.

Regards
Les



BruceM said:
No idea without more information. Post the table structure, the
expression you are using to increment the number and where the expession
resides, and an explanation of the way in which it does not work (error
message, nothing at all, #Error in the text box, or whatever).

ljg08 said:
Hi Bruce and thanks for that, However, I have tried this and not having
any sucess with this method, seems simple enough, but cannot get it to
work.

I am putting this code just as it is in the example and substituting the
field and table names for my own, any suggestions as to why this would
not work for me?

Regards
Les

BruceM said:
This may be just what you need:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

Note that you can format the number if you want leading zeros. In the
text box Format property:
00000
will turn 1 in 00001; 101 will be 00101.

Hi Guys,

Looking for help with this issue. I have an invoice form with a field
[invoice_num] and want, on save, to create a sequential number. If
user cancels the record any point before save I do not want to save the
number to ensure sequence for invoices is consistent.

Any suggestion would be most helpful.

TIA
Click to expand...
Click to expand...
Click to expand...
 
What is the other method to which you refer? Why have you set warnings to
false, and why have you not set them back to true? Why are you opening a
query? Why are you writing values from the current form (the one on which
Command15 is located) to frmOrderPlacement (I assume that's what you are
trying to do)? What is frmAccountCustomers, and why are you closing it?
Remember, only you can see your project, so code without context, especially
unconventional code, is difficult to decipher.

In short, what are you trying to do with the command button code?

What happens with the code in the Current event? In which form's Current
event is the code located?

ljg said:
Hi Bruce,

I have also found another method, which when I test with a simple form and
add a record it works, however, when I put it on my form, which is opened
from another form via :

Private Sub Command15_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "TempCustomerPriceCheck"
DoCmd.OpenForm "frmOrderPlacement", acNormal, "", "", acFormAdd,
acWindowNormal
Forms!frmOrderPlacement!accountID = accountID '.Value
Forms!frmOrderPlacement!txtcustID = CustID '.Value
Forms!frmOrderPlacement!txtaddress1 = Address2 '.Value
Forms!frmOrderPlacement!txtaddress2 = Address1 '.Value
Forms!frmOrderPlacement!txtaddress3 = Address3 '.Value '
Forms!frmOrderPlacement!txtName = CustName '.Value
Forms!frmOrderPlacement!txtpostcode = Post_Code '.Value
DoCmd.Close acForm, "frmAccountCustomers"
Forms!frmOrderPlacement.SetFocus
End Sub

I have added this code to the onCurrent event:

If Me.NewRecord Then
Me!invoice.DefaultValue = Nz(DMax("[invoice]", "orders"), 0) + 1
End If
Me.refUser = GetUsrFullName
End Sub

Any suggestions would be appreciated.

Regards
Les



BruceM said:
No idea without more information. Post the table structure, the
expression you are using to increment the number and where the expession
resides, and an explanation of the way in which it does not work (error
message, nothing at all, #Error in the text box, or whatever).

ljg08 said:
Hi Bruce and thanks for that, However, I have tried this and not having
any sucess with this method, seems simple enough, but cannot get it to
work.

I am putting this code just as it is in the example and substituting the
field and table names for my own, any suggestions as to why this would
not work for me?

Regards
Les

This may be just what you need:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

Note that you can format the number if you want leading zeros. In the
text box Format property:
00000
will turn 1 in 00001; 101 will be 00101.

Hi Guys,

Looking for help with this issue. I have an invoice form with a field
[invoice_num] and want, on save, to create a sequential number. If
user cancels the record any point before save I do not want to save
the number to ensure sequence for invoices is consistent.

Any suggestion would be most helpful.

TIA
Click to expand...
Click to expand...
Click to expand...
 
Hi Bruce,

The method I am referring to is:

Me!invoice.DefaultValue = Nz(DMax("[invoice]", "orders"), 0) + 1

The form 'frmOrderPlacement' not only allows the user to create an invoice.
each customers has a unique list of prices which it creates a temp table for
the specific customer when the form opens. 'frmAccountCustomers' is the form
the user selects the customer from to create an invoice for., so we close
that form when they want to create the invoice.

When closing frmOrderPlacement the close button runs another update query to
modify the available stock levels:

Private Sub buttonClose_Click()
Dim rsOrderLineItems As DAO.Recordset
Dim rsCSInventory As DAO.Recordset

Dim strQty As String
Dim strWeight As String
Dim strSQl As String

Dim ctlQty As Control
Dim ctlWeight As Control


Me.txtAmt = Me.frmOrderItems!txtTotal
Me.txtbalance = Me.frmOrderItems!txtTotal

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70


Set rsOrderLineItems = CurrentDb.OpenRecordset("orderlineitems",
dbOpenDynaset)
Set rsCSInventory = CurrentDb.OpenRecordset("tblinventory",
dbOpenDynaset)


DoCmd.SetWarnings False

strSQl = "UPDATE tblinventory " & vbCrLf
strSQl = strSQl & " INNER JOIN orderlineitems " & vbCrLf
strSQl = strSQl & " ON tblinventory.productID =
orderlineitems.productID SET tblinventory.SumOfqty =
[tblinventory]![SumOfqty]-[orderlineitems]![qty]" & vbCrLf
strSQl = strSQl & " WHERE
(((orderlineitems.custID)=[forms]![frmOrderPlacement]![txtcustid]) " &
vbCrLf
strSQl = strSQl & " AND
((orderlineitems.orderID)=[forms]![frmOrderPlacement]![orderid]));"

DoCmd.RunSQL strSQl


rsCSInventory.Close
rsOrderLineItems.Close

Set rsCSInventory = Nothing
Set rsOrderLineItems = Nothing

DoCmd.Close
End Sub

The onCurrent code is from frmOrderPlacement, so once the form is open, it
adds a user name 'Me.refUser = GetUsrFullName' to the table orders to log
the user who created the order.

The next part of this is the method I was talking about, and simply want
this to create a new invoice number when the form opens

Hope that makes sense?

Regards
Les

BruceM said:
What is the other method to which you refer? Why have you set warnings to
false, and why have you not set them back to true? Why are you opening a
query? Why are you writing values from the current form (the one on which
Command15 is located) to frmOrderPlacement (I assume that's what you are
trying to do)? What is frmAccountCustomers, and why are you closing it?
Remember, only you can see your project, so code without context,
especially unconventional code, is difficult to decipher.

In short, what are you trying to do with the command button code?

What happens with the code in the Current event? In which form's Current
event is the code located?

ljg said:
Hi Bruce,

I have also found another method, which when I test with a simple form
and add a record it works, however, when I put it on my form, which is
opened from another form via :

Private Sub Command15_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "TempCustomerPriceCheck"
DoCmd.OpenForm "frmOrderPlacement", acNormal, "", "", acFormAdd,
acWindowNormal
Forms!frmOrderPlacement!accountID = accountID '.Value
Forms!frmOrderPlacement!txtcustID = CustID '.Value
Forms!frmOrderPlacement!txtaddress1 = Address2 '.Value
Forms!frmOrderPlacement!txtaddress2 = Address1 '.Value
Forms!frmOrderPlacement!txtaddress3 = Address3 '.Value '
Forms!frmOrderPlacement!txtName = CustName '.Value
Forms!frmOrderPlacement!txtpostcode = Post_Code '.Value
DoCmd.Close acForm, "frmAccountCustomers"
Forms!frmOrderPlacement.SetFocus
End Sub

I have added this code to the onCurrent event:

If Me.NewRecord Then
Me!invoice.DefaultValue = Nz(DMax("[invoice]", "orders"), 0) + 1
End If
Me.refUser = GetUsrFullName
End Sub

Any suggestions would be appreciated.

Regards
Les



BruceM said:
No idea without more information. Post the table structure, the
expression you are using to increment the number and where the expession
resides, and an explanation of the way in which it does not work (error
message, nothing at all, #Error in the text box, or whatever).

Hi Bruce and thanks for that, However, I have tried this and not having
any sucess with this method, seems simple enough, but cannot get it to
work.

I am putting this code just as it is in the example and substituting
the field and table names for my own, any suggestions as to why this
would not work for me?

Regards
Les

This may be just what you need:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

Note that you can format the number if you want leading zeros. In the
text box Format property:
00000
will turn 1 in 00001; 101 will be 00101.

Hi Guys,

Looking for help with this issue. I have an invoice form with a field
[invoice_num] and want, on save, to create a sequential number. If
user cancels the record any point before save I do not want to save
the number to ensure sequence for invoices is consistent.

Any suggestion would be most helpful.

TIA
Click to expand...
Click to expand...
Click to expand...
 
All you need to do is to place something like this as the Default Value of a
text box bound to the Invoice field:
= Nz(DMax("[invoice]", "orders"), 0) + 1

Give the text box a format such as 00000 if you want leading zeros.

I'm not sure what you're tryiing to do with the rest of the code, but it
seems you are overcomplicating something. I would just select the customer
from a combo box on the invoice form. The Row Source for the combo box is
the Customer table. The CustomerID (the bound column from this combo box)
is stored in the Invoice table.

I am not familiar with all the details of updaing a recordset through code.
However, most developers prefer something like this instead of DoCmd.RunSQL
strSQl:
DBEngine(0)(0).Execute strSQL, dbFailOnError

Having said that, you seem to have opened two recordsets, then run some SQL
on a table, then closed the recordsets, but from what I can tell you never
performed any actions on the recordsets. For instance:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Orders", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Invoice = Nz(DMax("[invoice]", "orders"), 0) + 1
rs.Update
rs.Close

I don't know if the above would work in your case; I am posting it as a sort
of example only.

In any case, I don't see the point of manipulating a recordset through code
when you are going to open the form anyhow to complete the order. Maybe the
rest of the order information is in the code you posted, so that you never
open the form, but I don't think so.

I would be inclined to store the ID of the person who created the invoice
rather than the full name, so that if the name changes in the future the
record will change too. I don't know exactly what GetUsrFullName
accomplishes, but my guess would be that it extracts the full name from the
Employee table (or something).

To save a record:
Me.Dirty = False
or
If Me.Dirty Then Me.Dirty = False
Either is adequate, and much simpler than what the wizard generates
(assuming Access 2000 or later, I think).

You have several things such as Dim strQty as String, but you don't use the
variable that I can see. In general there seems to be some extra code
floating around.

ljg said:
Hi Bruce,

The method I am referring to is:

Me!invoice.DefaultValue = Nz(DMax("[invoice]", "orders"), 0) + 1

The form 'frmOrderPlacement' not only allows the user to create an
invoice. each customers has a unique list of prices which it creates a
temp table for the specific customer when the form opens.
'frmAccountCustomers' is the form the user selects the customer from to
create an invoice for., so we close that form when they want to create the
invoice.

When closing frmOrderPlacement the close button runs another update query
to modify the available stock levels:

Private Sub buttonClose_Click()
Dim rsOrderLineItems As DAO.Recordset
Dim rsCSInventory As DAO.Recordset

Dim strQty As String
Dim strWeight As String
Dim strSQl As String

Dim ctlQty As Control
Dim ctlWeight As Control


Me.txtAmt = Me.frmOrderItems!txtTotal
Me.txtbalance = Me.frmOrderItems!txtTotal

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70


Set rsOrderLineItems = CurrentDb.OpenRecordset("orderlineitems",
dbOpenDynaset)
Set rsCSInventory = CurrentDb.OpenRecordset("tblinventory",
dbOpenDynaset)


DoCmd.SetWarnings False

strSQl = "UPDATE tblinventory " & vbCrLf
strSQl = strSQl & " INNER JOIN orderlineitems " & vbCrLf
strSQl = strSQl & " ON tblinventory.productID =
orderlineitems.productID SET tblinventory.SumOfqty =
[tblinventory]![SumOfqty]-[orderlineitems]![qty]" & vbCrLf
strSQl = strSQl & " WHERE
(((orderlineitems.custID)=[forms]![frmOrderPlacement]![txtcustid]) " &
vbCrLf
strSQl = strSQl & " AND
((orderlineitems.orderID)=[forms]![frmOrderPlacement]![orderid]));"

DoCmd.RunSQL strSQl


rsCSInventory.Close
rsOrderLineItems.Close

Set rsCSInventory = Nothing
Set rsOrderLineItems = Nothing

DoCmd.Close
End Sub

The onCurrent code is from frmOrderPlacement, so once the form is open, it
adds a user name 'Me.refUser = GetUsrFullName' to the table orders to log
the user who created the order.

The next part of this is the method I was talking about, and simply want
this to create a new invoice number when the form opens

Hope that makes sense?

Regards
Les

BruceM said:
What is the other method to which you refer? Why have you set warnings
to false, and why have you not set them back to true? Why are you
opening a query? Why are you writing values from the current form (the
one on which Command15 is located) to frmOrderPlacement (I assume that's
what you are trying to do)? What is frmAccountCustomers, and why are you
closing it? Remember, only you can see your project, so code without
context, especially unconventional code, is difficult to decipher.

In short, what are you trying to do with the command button code?

What happens with the code in the Current event? In which form's Current
event is the code located?

ljg said:
Hi Bruce,

I have also found another method, which when I test with a simple form
and add a record it works, however, when I put it on my form, which is
opened from another form via :

Private Sub Command15_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "TempCustomerPriceCheck"
DoCmd.OpenForm "frmOrderPlacement", acNormal, "", "", acFormAdd,
acWindowNormal
Forms!frmOrderPlacement!accountID = accountID '.Value
Forms!frmOrderPlacement!txtcustID = CustID '.Value
Forms!frmOrderPlacement!txtaddress1 = Address2 '.Value
Forms!frmOrderPlacement!txtaddress2 = Address1 '.Value
Forms!frmOrderPlacement!txtaddress3 = Address3 '.Value '
Forms!frmOrderPlacement!txtName = CustName '.Value
Forms!frmOrderPlacement!txtpostcode = Post_Code '.Value
DoCmd.Close acForm, "frmAccountCustomers"
Forms!frmOrderPlacement.SetFocus
End Sub

I have added this code to the onCurrent event:

If Me.NewRecord Then
Me!invoice.DefaultValue = Nz(DMax("[invoice]", "orders"), 0) + 1
End If
Me.refUser = GetUsrFullName
End Sub

Any suggestions would be appreciated.

Regards
Les



No idea without more information. Post the table structure, the
expression you are using to increment the number and where the
expession resides, and an explanation of the way in which it does not
work (error message, nothing at all, #Error in the text box, or
whatever).

Hi Bruce and thanks for that, However, I have tried this and not
having any sucess with this method, seems simple enough, but cannot
get it to work.

I am putting this code just as it is in the example and substituting
the field and table names for my own, any suggestions as to why this
would not work for me?

Regards
Les

This may be just what you need:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

Note that you can format the number if you want leading zeros. In
the text box Format property:
00000
will turn 1 in 00001; 101 will be 00101.

Hi Guys,

Looking for help with this issue. I have an invoice form with a
field [invoice_num] and want, on save, to create a sequential
number. If user cancels the record any point before save I do not
want to save the number to ensure sequence for invoices is
consistent.

Any suggestion would be most helpful.

TIA
Click to expand...
Click to expand...
Click to expand...
 
Cheers Bruce,

Yes, I had a clear thought yesterday and added that in and all working fine
now.

Many thanks for your help.

Regards
Les
BruceM said:
All you need to do is to place something like this as the Default Value of
a text box bound to the Invoice field:
= Nz(DMax("[invoice]", "orders"), 0) + 1

Give the text box a format such as 00000 if you want leading zeros.

I'm not sure what you're tryiing to do with the rest of the code, but it
seems you are overcomplicating something. I would just select the
customer from a combo box on the invoice form. The Row Source for the
combo box is the Customer table. The CustomerID (the bound column from
this combo box) is stored in the Invoice table.

I am not familiar with all the details of updaing a recordset through
code. However, most developers prefer something like this instead of
DoCmd.RunSQL strSQl:
DBEngine(0)(0).Execute strSQL, dbFailOnError

Having said that, you seem to have opened two recordsets, then run some
SQL on a table, then closed the recordsets, but from what I can tell you
never performed any actions on the recordsets. For instance:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Orders", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Invoice = Nz(DMax("[invoice]", "orders"), 0) + 1
rs.Update
rs.Close

I don't know if the above would work in your case; I am posting it as a
sort of example only.

In any case, I don't see the point of manipulating a recordset through
code when you are going to open the form anyhow to complete the order.
Maybe the rest of the order information is in the code you posted, so that
you never open the form, but I don't think so.

I would be inclined to store the ID of the person who created the invoice
rather than the full name, so that if the name changes in the future the
record will change too. I don't know exactly what GetUsrFullName
accomplishes, but my guess would be that it extracts the full name from
the Employee table (or something).

To save a record:
Me.Dirty = False
or
If Me.Dirty Then Me.Dirty = False
Either is adequate, and much simpler than what the wizard generates
(assuming Access 2000 or later, I think).

You have several things such as Dim strQty as String, but you don't use
the variable that I can see. In general there seems to be some extra code
floating around.

ljg said:
Hi Bruce,

The method I am referring to is:

Me!invoice.DefaultValue = Nz(DMax("[invoice]", "orders"), 0) + 1

The form 'frmOrderPlacement' not only allows the user to create an
invoice. each customers has a unique list of prices which it creates a
temp table for the specific customer when the form opens.
'frmAccountCustomers' is the form the user selects the customer from to
create an invoice for., so we close that form when they want to create
the invoice.

When closing frmOrderPlacement the close button runs another update query
to modify the available stock levels:

Private Sub buttonClose_Click()
Dim rsOrderLineItems As DAO.Recordset
Dim rsCSInventory As DAO.Recordset

Dim strQty As String
Dim strWeight As String
Dim strSQl As String

Dim ctlQty As Control
Dim ctlWeight As Control


Me.txtAmt = Me.frmOrderItems!txtTotal
Me.txtbalance = Me.frmOrderItems!txtTotal

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70


Set rsOrderLineItems = CurrentDb.OpenRecordset("orderlineitems",
dbOpenDynaset)
Set rsCSInventory = CurrentDb.OpenRecordset("tblinventory",
dbOpenDynaset)


DoCmd.SetWarnings False

strSQl = "UPDATE tblinventory " & vbCrLf
strSQl = strSQl & " INNER JOIN orderlineitems " & vbCrLf
strSQl = strSQl & " ON tblinventory.productID =
orderlineitems.productID SET tblinventory.SumOfqty =
[tblinventory]![SumOfqty]-[orderlineitems]![qty]" & vbCrLf
strSQl = strSQl & " WHERE
(((orderlineitems.custID)=[forms]![frmOrderPlacement]![txtcustid]) " &
vbCrLf
strSQl = strSQl & " AND
((orderlineitems.orderID)=[forms]![frmOrderPlacement]![orderid]));"

DoCmd.RunSQL strSQl


rsCSInventory.Close
rsOrderLineItems.Close

Set rsCSInventory = Nothing
Set rsOrderLineItems = Nothing

DoCmd.Close
End Sub

The onCurrent code is from frmOrderPlacement, so once the form is open,
it adds a user name 'Me.refUser = GetUsrFullName' to the table orders to
log the user who created the order.

The next part of this is the method I was talking about, and simply want
this to create a new invoice number when the form opens

Hope that makes sense?

Regards
Les

BruceM said:
What is the other method to which you refer? Why have you set warnings
to false, and why have you not set them back to true? Why are you
opening a query? Why are you writing values from the current form (the
one on which Command15 is located) to frmOrderPlacement (I assume that's
what you are trying to do)? What is frmAccountCustomers, and why are
you closing it? Remember, only you can see your project, so code without
context, especially unconventional code, is difficult to decipher.

In short, what are you trying to do with the command button code?

What happens with the code in the Current event? In which form's
Current event is the code located?

Hi Bruce,

I have also found another method, which when I test with a simple form
and add a record it works, however, when I put it on my form, which is
opened from another form via :

Private Sub Command15_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "TempCustomerPriceCheck"
DoCmd.OpenForm "frmOrderPlacement", acNormal, "", "", acFormAdd,
acWindowNormal
Forms!frmOrderPlacement!accountID = accountID '.Value
Forms!frmOrderPlacement!txtcustID = CustID '.Value
Forms!frmOrderPlacement!txtaddress1 = Address2 '.Value
Forms!frmOrderPlacement!txtaddress2 = Address1 '.Value
Forms!frmOrderPlacement!txtaddress3 = Address3 '.Value '
Forms!frmOrderPlacement!txtName = CustName '.Value
Forms!frmOrderPlacement!txtpostcode = Post_Code '.Value
DoCmd.Close acForm, "frmAccountCustomers"
Forms!frmOrderPlacement.SetFocus
End Sub

I have added this code to the onCurrent event:

If Me.NewRecord Then
Me!invoice.DefaultValue = Nz(DMax("[invoice]", "orders"), 0) + 1
End If
Me.refUser = GetUsrFullName
End Sub

Any suggestions would be appreciated.

Regards
Les



No idea without more information. Post the table structure, the
expression you are using to increment the number and where the
expession resides, and an explanation of the way in which it does not
work (error message, nothing at all, #Error in the text box, or
whatever).

Hi Bruce and thanks for that, However, I have tried this and not
having any sucess with this method, seems simple enough, but cannot
get it to work.

I am putting this code just as it is in the example and substituting
the field and table names for my own, any suggestions as to why this
would not work for me?

Regards
Les

This may be just what you need:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

Note that you can format the number if you want leading zeros. In
the text box Format property:
00000
will turn 1 in 00001; 101 will be 00101.

Hi Guys,

Looking for help with this issue. I have an invoice form with a
field [invoice_num] and want, on save, to create a sequential
number. If user cancels the record any point before save I do not
want to save the number to ensure sequence for invoices is
consistent.

Any suggestion would be most helpful.

TIA
Click to expand...
Click to expand...
Click to expand...
 
ljg08 said:
Hi Guys,

Looking for help with this issue. I have an invoice form with a field
[invoice_num] and want, on save, to create a sequential number. If user
cancels the record any point before save I do not want to save the number
to ensure sequence for invoices is consistent.

Any suggestion would be most helpful.

TIA
Click to expand...
 
See if a search on "custom autonumber" is any help...

Regards

Jeff Boyce
Microsoft Office/Access MVP

ptm said:
ljg08 said:
Hi Guys,

Looking for help with this issue. I have an invoice form with a field
[invoice_num] and want, on save, to create a sequential number. If user
cancels the record any point before save I do not want to save the number
to ensure sequence for invoices is consistent.

Any suggestion would be most helpful.

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