Populate a form with Max Recordset

[Moose3]

Hey Gang,

I'm trying to pull a project number sequentially without using an autonumber field type. I tried to pull a recordset using a SQL string pulling the max of my project# field. All I really need is to take the result of the recordset and set the field in my form equal to it. It should be easy, but I can't figure it out. Here is my code.

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

Dim rsMax As New ADODB.Recordset
Set rsMax = New ADODB.Recordset

Dim strSQL As String
strSQL = "SELECT Max([10-Project Table].[Project#]) FROM [10-Project Table];"
rsMax.Open strSQL, cnn, adOpenStatic, adLockReadOnly
rsMax.MoveLast

DoCmd.GoToRecord , "", acNewRec --This will create the new record
Me.Project_ = rsMax -- This needs to populate Project_ field with the recordset value

Any ideas?

 

[Moose3]

Nevermind...I figured it out. I used the DMax function instead. I had never heard of it, and still don't really understand the difference between Max() and DMax(), but this works. Hope it helps someone else.

Me.Project_ = DMax("[Project#]", "[10-Project Table]")

It was very simple once I found that function.