Create a unique record number

[Guest]

I have aproblem with my form "frmOrders" .

The form is based on an underlying query of three tables "tblOrders",
"tblCustomers" and "tblCustomerTypes". "tblOrders" and "tblCustomers" are
linked by the "CustomerID" field. "tblCustomers" and "tblCustomerTypes" are
linked by "CustomerTypeID". There is a subform based on a query of two other
tables "tblOrderDetails" and "tblProducts". These two tables are linked by
the "ProductID" field. The sub form and main form are linked by the
"OrderID" field.

I am trying to generate unique sequential OrderID numbers by counting
records in a table "tblOrders" and incrementing the count by 1. I then wish
to display the number in the "OrderID" control on the form "frmOrders" for
each new record and save this in the "OrderID" field of a new record in table.

My idea is to have a button that will update stock levels in "tblProducts"
and record the information on the form and subform in the underlying tables
and clear the form for a new record to be entered with and incremented
"OrderID" control.

I am stuck on the form at this point.

The problem I am having is the following error occurs.
Run Time Error 2465
Can't find field OrderID

The code I have put together is as follows for when the form is opened.

Private Sub Orders_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim OrdersCount As Integer

stDocName = "frmOrders"
DoCmd.OpenForm stDocName, , , stLinkCriteria
'Me!OrderID = DCount("*", "tblOrders")
OrdersCount = 1 + DCount("OrderID", "tblOrders")
Me!CustomerID.SetFocus
Exit_Orders_Click:
Exit Sub

Err_Orders_Click:
MsgBox Err.Description
Resume Exit_Orders_Click

End Sub


Any help greatley appreciated

Cheers

 

[Guest]

Private Sub Orders_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim OrdersCount As Integer

stDocName = "frmOrders"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Me!OrderID = DMAX("[OrderID]", "tblOrders")
OrdersCount = Me!OrderID + 1
Me!CustomerID.SetFocus
Exit_Orders_Click:
Exit Sub

Err_Orders_Click:
MsgBox Err.Description
Resume Exit_Orders_Click

End Sub

 

[Guest]

Thanks that is much appreciated
--
paulu

Private Sub Orders_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim OrdersCount As Integer

stDocName = "frmOrders"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Me!OrderID = DMAX("[OrderID]", "tblOrders")
OrdersCount = Me!OrderID + 1
Me!CustomerID.SetFocus
Exit_Orders_Click:
Exit Sub

Err_Orders_Click:
MsgBox Err.Description
Resume Exit_Orders_Click

End Sub

I have aproblem with my form "frmOrders" .

The form is based on an underlying query of three tables "tblOrders",
"tblCustomers" and "tblCustomerTypes". "tblOrders" and "tblCustomers" are
linked by the "CustomerID" field. "tblCustomers" and "tblCustomerTypes" are
linked by "CustomerTypeID". There is a subform based on a query of two other
tables "tblOrderDetails" and "tblProducts". These two tables are linked by
the "ProductID" field. The sub form and main form are linked by the
"OrderID" field.

I am trying to generate unique sequential OrderID numbers by counting
records in a table "tblOrders" and incrementing the count by 1. I then wish
to display the number in the "OrderID" control on the form "frmOrders" for
each new record and save this in the "OrderID" field of a new record in table.

My idea is to have a button that will update stock levels in "tblProducts"
and record the information on the form and subform in the underlying tables
and clear the form for a new record to be entered with and incremented
"OrderID" control.

I am stuck on the form at this point.

The problem I am having is the following error occurs.
Run Time Error 2465
Can't find field OrderID

The code I have put together is as follows for when the form is opened.

Private Sub Orders_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim OrdersCount As Integer

stDocName = "frmOrders"
DoCmd.OpenForm stDocName, , , stLinkCriteria
'Me!OrderID = DCount("*", "tblOrders")
OrdersCount = 1 + DCount("OrderID", "tblOrders")
Me!CustomerID.SetFocus
Exit_Orders_Click:
Exit Sub

Err_Orders_Click:
MsgBox Err.Description
Resume Exit_Orders_Click

End Sub


Any help greatley appreciated

Cheers