Counting and Assigning Numbers

[Crystal]

Help Please. I have the following code and need help figuring out what I'm
missing. I need to find the Highest Number and add 1 looping thru the
remaining fields that are Not Null.

Private Sub Close_Form_Click()

Dim db As Database, rst As Recordset, lTotal As Long, lCounter As Long

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM [Set Priority Level] WHERE ((([Set
Priority Level].[PRIORITY]) Is Null));", dbOpenDynaset)

'return number of records in recordset
lTotal = rst(0)

Dim HighestNumber As Long
HighestNumber = DMax("[PRIORITY]", "HWIL - Set Priority Level", "[PRIORITY]
is Not Null")


For lCounter = 1 To lTotal
Do While Not rst.EOF

rst.Edit
rst!PRIORITY = HighestNumber + 1
rst.Update
rst.MoveNext

Loop
Next lCounter


rst.Close
Set db = Nothing
Set rst = Nothing


DoCmd.RunMacro "Priority"
DoCmd.Close

End Sub

 

[John Spencer]

Private Sub Close_Form_Click()

Dim db As Database, rst As Recordset, lTotal As Long, lCounter As Long

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM [Set Priority Level] WHERE ((([Set
Priority Level].[PRIORITY]) Is Null));", dbOpenDynaset)

Dim HighestNumber As Long
HighestNumber = DMax("[PRIORITY]", "HWIL - Set Priority Level", "[PRIORITY]
is Not Null")

Do While Not rst.EOF
LCounter = 1 + LCounter
rst.Edit
rst!PRIORITY = HighestNumber + LCounter
rst.Update
rst.MoveNext

Loop

rst.Close
Set db = Nothing
Set rst = Nothing


DoCmd.RunMacro "Priority"
'DoCmd.Close '<<<< ??? you are already closing the form

End Sub

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Stuart McCall]

'DoCmd.Close '<<<< ??? you are already closing the form

The sub isn't an event procedure..

 

[Crystal]

John,

It worked! Thank You so Much for your help!

Private Sub Close_Form_Click()

Dim db As Database, rst As Recordset, lTotal As Long, lCounter As Long

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM [Set Priority Level] WHERE ((([Set
Priority Level].[PRIORITY]) Is Null));", dbOpenDynaset)

Dim HighestNumber As Long
HighestNumber = DMax("[PRIORITY]", "HWIL - Set Priority Level", "[PRIORITY]
is Not Null")

Do While Not rst.EOF
LCounter = 1 + LCounter
rst.Edit
rst!PRIORITY = HighestNumber + LCounter
rst.Update
rst.MoveNext

Loop

rst.Close
Set db = Nothing
Set rst = Nothing


DoCmd.RunMacro "Priority"
'DoCmd.Close '<<<< ??? you are already closing the form

End Sub

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Help Please. I have the following code and need help figuring out what
I'm
missing. I need to find the Highest Number and add 1 looping thru the
remaining fields that are Not Null.

Private Sub Close_Form_Click()

Dim db As Database, rst As Recordset, lTotal As Long, lCounter As Long

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM [Set Priority Level] WHERE
((([Set
Priority Level].[PRIORITY]) Is Null));", dbOpenDynaset)

'return number of records in recordset
lTotal = rst(0)

Dim HighestNumber As Long
HighestNumber = DMax("[PRIORITY]", "HWIL - Set Priority Level",
"[PRIORITY]
is Not Null")


For lCounter = 1 To lTotal
Do While Not rst.EOF

rst.Edit
rst!PRIORITY = HighestNumber + 1
rst.Update
rst.MoveNext

Loop
Next lCounter


rst.Close
Set db = Nothing
Set rst = Nothing


DoCmd.RunMacro "Priority"
DoCmd.Close

End Sub

 

[John Spencer]

Right, I misread the sub.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..