MS Access 2000 VB HELP!

[Guest]

I am posting the code used in my project for an update event.

MS Access 2000 on Win2k box.



Obejctive is to first find if an issue id already exists, and if so, find
out how many such exists. Each issue id has its own unique sequence number.
While it may be convenient to assume that the sequence number follows in
sequence, it does not necessarily have to. I do have to find out the highest
number used and then increment it by one for the current record (assuming it
matches the issue id). If no match for the current issue id, then I assign
the sequence number to be 1.



Code:



Dim intnewrec As Integer
Dim varX As Variant
Dim counter As Integer
Dim rstSeqNum As ADODB.recordset
Dim Conn As ADODB.Connection
Dim strConn As String
Dim max As Integer






Private Sub ActivityDesc_AfterUpdate()

intnewrec = Me.NewRecord

If intnewrec = -1 Then

max = 0
counter = 0

' strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
' "Data Source=C:\Documents and Settings\SRSupport\" & _
' "My Documents\db1.mdb;"
' Set Conn = New ADODB.Connection
' Conn.Open strConn

' Set rstSeqNum = New ADODB.recordset
' rstSeqNum.CursorType = adOpenKeyset
' rstSeqNum.LockType = adLockOptimistic
' rstSeqNum.Open "TableActivity", strConn, adOpenKeyset,
adLockOptimistic, adCmdTable
' rstSeqNum.MoveLast
' rstSeqNum.MoveFirst


(Developer notes: I had to comment these out, because Access did not support
conn.open function and the rstseqnum.open command. I tried using the docmd
but I suspect each time it loops through, it finds the first record only and
returns whatever the sequence number happens to be there.)




DoCmd.FindRecord "IssueID = " & Me.IssueID, acStart, , acDown, ,
acCurrent, findfirst


Do Until counter = 100 (arbitrary number just to find out if the
loop works)

varX = DLookup("[activityseqnum]", "TableActivity", "IssueID = "
& Me.IssueID)
counter = counter + 1
Label54.Caption = counter
If varX > max Then
max = varX
End If
DoCmd.FindNext
If varX = Null Then Exit Do
Loop


If max > 0 Then
ActivitySeqNum.Value = max + 1
Else
ActivitySeqNum.Value = 1
End If

End If

Label53.Caption = intnewrec

End Sub


End code



I am really at my wits end trying to find out how to continue with this. Any
help will be sincerely appreciated.



Thanks

Shankar raman

 

[Guest]

The technique for that is to use the DMax function to find the highest number
for the issue id and add 1 to it. If the issue id is new, the DMax will
return a Null, so use the Nz function to convert the Null to a 0, Then add
one to the results
Example:

lngNextNum = Nz(DMax("[SeqNum]", "TableName","[Issue] = " &
Me.IssueID),0) + 1
--
Dave Hargis, Microsoft Access MVP

I am posting the code used in my project for an update event.

MS Access 2000 on Win2k box.



Obejctive is to first find if an issue id already exists, and if so, find
out how many such exists. Each issue id has its own unique sequence number.
While it may be convenient to assume that the sequence number follows in
sequence, it does not necessarily have to. I do have to find out the highest
number used and then increment it by one for the current record (assuming it
matches the issue id). If no match for the current issue id, then I assign
the sequence number to be 1.



Code:



Dim intnewrec As Integer
Dim varX As Variant
Dim counter As Integer
Dim rstSeqNum As ADODB.recordset
Dim Conn As ADODB.Connection
Dim strConn As String
Dim max As Integer






Private Sub ActivityDesc_AfterUpdate()

intnewrec = Me.NewRecord

If intnewrec = -1 Then

max = 0
counter = 0

' strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
' "Data Source=C:\Documents and Settings\SRSupport\" & _
' "My Documents\db1.mdb;"
' Set Conn = New ADODB.Connection
' Conn.Open strConn

' Set rstSeqNum = New ADODB.recordset
' rstSeqNum.CursorType = adOpenKeyset
' rstSeqNum.LockType = adLockOptimistic
' rstSeqNum.Open "TableActivity", strConn, adOpenKeyset,
adLockOptimistic, adCmdTable
' rstSeqNum.MoveLast
' rstSeqNum.MoveFirst


(Developer notes: I had to comment these out, because Access did not support
conn.open function and the rstseqnum.open command. I tried using the docmd
but I suspect each time it loops through, it finds the first record only and
returns whatever the sequence number happens to be there.)




DoCmd.FindRecord "IssueID = " & Me.IssueID, acStart, , acDown, ,
acCurrent, findfirst


Do Until counter = 100 (arbitrary number just to find out if the
loop works)

varX = DLookup("[activityseqnum]", "TableActivity", "IssueID = "
& Me.IssueID)
counter = counter + 1
Label54.Caption = counter
If varX > max Then
max = varX
End If
DoCmd.FindNext
If varX = Null Then Exit Do
Loop


If max > 0 Then
ActivitySeqNum.Value = max + 1
Else
ActivitySeqNum.Value = 1
End If

End If

Label53.Caption = intnewrec

End Sub


End code



I am really at my wits end trying to find out how to continue with this. Any
help will be sincerely appreciated.



Thanks

Shankar raman