Counting and Assigning Numbers


C

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
 
Ad

Advertisements

J

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
..
 
S

Stuart McCall

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

The sub isn't an event procedure..
 
C

Crystal

John,

It worked! Thank You so Much for your help!

John Spencer said:
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
..

Crystal said:
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
 
Ad

Advertisements

J

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
..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top