Subform using DMAX

[LearnersPermit]

I have merged two databases, a clients database and a book orders database. I have now created an order form which is automatically opened with the client information from a form used to add new clients or look up current clients information. This part does work just fine.

I also have a subform used to order new books. The majority of the form works fine. It is the order # that is the problem. The order # is no longer an autonumber and I am using DMax in an attempt to force the order to form to select the next number. This has not been working. I receive the warning that the number cannot be a null. I have set the default for the order # to -1, 0 and 1, all without success.

The following is my code on the main form (the one that shows the client name and client ID).

Private Sub Form_Open(Cancel As Integer)
Dim Order_ID_Var As Long
Dim SQL As String

Order_ID_Var = -1
If (Nz(DMax("[Order_ID]", "[tblOrders]&quot) = -1) Then
Order_ID_Var = 1
Else
Order_ID_Var = Nz(DMax("[Order_ID]", "[tblOrders]&quot) + 1
End If

SQL = "INSERT INTO tblOrders (Order_ID, Client_ID) Values (" & Order_ID_Var & "," & Client_ID & " )"
CurrentDb.Execute SQL, dbFailOnError

End Sub


And I have used the following code on the subform.



Private Sub Form_Load()
Dim intNextNum As Variant
intNextNum = Nz(DMax("[Order_ID]", "[tblOrders]&quot)

End Sub

ANY SUGGESTIONS?

Thanks in advance for your help

 

[jimmythegeek]

What exactly is the order of things?

Click a button, open a form (add mode?), populate a record ???

I am not clear on what your process is here. Are you opening a form and trying to add a record and assigning the OrderID to the max value in a table + 1? Jim Lunde
compugeeks@hotmail.com
We all agree that your theory is crazy, but is it crazy enough?

 

[LearnersPermit]

The user will open the database at a user interface which will give them several options, however the one that I am dealing with uses the following process.

1. Open Client Entry Form (they will be able to view current client information or enter new client information as required).

2. Click on a button to make an order.

3. The order form then opens with the client information in a main form (this works). There is a subform which holds the Order Id and is based on tblOrders (this is the order no. that I'm trying to get to work). Within this subform there is another subform which allows users to actually place orders (the only problem that I have with this portion is that it's still holding previous orders for this client and I have not yet tried to correct this problem).

Obviously I'm somewhat out of my depth with this problem, can you help me?

Thanks