Pull next project number each time the form is opened

[JeffreyDavid]

I have a form (ProposedProject) with a subform and each time I open the form, it checks the Orders table, finds the last project number and adds 1 and that is my new project number. Heres the code I used.
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Orders", dbOpenDynaset)
rs.MoveLast
Me!txtProjectNo = rs("ProjectNo") + 1
Forms![NewProject]![Project subform]![ProjectNo] = rs("ProjectNo") + 1

I also have a different form (ExistingProject) so I can enter existing projects and that form lets me manually enter the project number.
After I enter an ExistingProject, which I manually enter the project number, I open the ProposedProject form, which is suposed to find the last project number and add 1, the number is one I have already used and not consistent.

How can I reset the numbers it pulls or tell it to go to the last record and add 1 again?

 

[WayneRyan]

Jeffrey,

When you open a recordset like this:

Set rs = db.OpenRecordset("Orders", dbOpenDynaset)

You have no idea in what order the recordset will be presented. The
.MoveLast moves to a random record.

If you are using a custom number, then you need to use the DMax function
and add 1 to it.

The values for your subform's keys can just have a default value of:

=Forms![YourMainForm]![YourMainKey]

Wayne

 

[JeffreyDavid]

Jeffrey,

When you open a recordset like this:

Set rs = db.OpenRecordset("Orders", dbOpenDynaset)

You have no idea in what order the recordset will be presented. The
.MoveLast moves to a random record.

If you are using a custom number, then you need to use the DMax function
and add 1 to it.

The values for your subform's keys can just have a default value of:

=Forms![YourMainForm]![YourMainKey]

Wayne

Wayne,
I'm not sure if I have the syntax correct, here is my code, my ProjectNo is a 4 digit number which resides in the Orders table and the control on my form is txtProjectNo.

Dim Ords As Long
Ords = DMax("txtProjectNo", "Orders", "[ProjectNo] + 1")

I get an error message saying that invalid use of null. The ProjectNo is the primary key in the Orders table.

 

[ghudson]

Try...

Dim Ords As Long
Ords = DMax("[txtProjectNo]", "Orders", "[ProjectNo]") + 1

 

[JeffreyDavid]

Try...

Dim Ords As Long
Ords = DMax("[txtProjectNo]", "Orders", "[ProjectNo]") + 1

I keep getting that error message, invalid use of null. I have this code in the OnOpen event of the main form. So when I open the main form it goes to the 'Orders' table, looks at the 'ProjectNo', determines the highest number, adds 1 to it and puts it in the 'txtProjectNo'. If it can't find the highest number and add 1 to it, because ProjectNo is the Primary Key in the Orders table, it can't open the form without that ProjectNo.
Should it try to put it elsewhere, a different EventProdcedure maybe?

 

[ghudson]

Try this in the forms OnCurrent event...

Private Sub Form_Current()
On Error GoTo Err_Form_Current
    
    If Me.NewRecord Then
        Dim Ords As Long
        Ords = DMax("[txtProjectNo]", "Orders", "[ProjectNo]") + 1
    Else
        'do nothing
    End If
    
Exit_Form_Current:
    Exit Sub

Err_Form_Current:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_Form_Current
    
End Sub

 

[JeffreyDavid]

Thanks for everyones help.
I ended up putting as hidden text box on the form with the code:
= DMax("[txtProjectNo]", "Orders")
Then in the OnOpen event of the form put ,
Me.txtProjectNo = Me.HiddenTextBox + 1
And All is well.