RE: Next Number and Unused Registration Number

Steve, waht is the meaning of ' 1; 1 *** and all '2 ... at the end?

Also should I put the function in the module of the database or leave it as
a sub?
--
H. Frank Situmorang


"Steve Sanford" wrote:

Hi Frank,

If I understand right, if AngtType = "SS member", you want to set NoInd = 0.

And you want to reuse the number if you change AngtType from "Anggota
Jemaat" to "SS member".


Here is your modified sub and a function to generate the next number:
(Watch for line wrap)

'--------code beg -------------------
Private Sub AngtType_AfterUpdate()
If AngtType = "Anggota Jemaat" Then ' 1
' NoInd = Nz(DMax("[NoIn]", "bukuangkby")) + 1 ***
NoInd = GetNextNumber
If Not IsNull(Me.[AngtType].OldValue) Then '2
If Me.[AngtType] <> Me.[AngtType].OldValue Then '3
If MsgBox("Anda telah merobah!!, apakah sengaja mau merobah?",
vbYesNo + vbDefaultButton2 + vbQuestion) = vbNo Then ' 4
' Undo the changes
Me.AngtType = Me.AngtType.OldValue
End If '4
End If '3
End If ' 2
Else ' 1
'If you want the field NULL, uncomment the this line
' NoInd=NULL
' and comment the the this line
NoInd = 0 '***
End If '1
End Sub


Public Function GetNextNumber()
'Function to generate the next number
Dim sSQL As String
Dim r As DAO.Recordset
Dim s As Byte

'create a recordset
sSQL = "select top 1 bukuangkby.noin + 1 as NN,"
sSQL = sSQL & " (select min(t.noin) - 1 from bukuangkby as t where
t.noin > bukuangkby.noin)"
sSQL = sSQL & " from bukuangkby"
sSQL = sSQL & " where not exists (select t.noin from bukuangkby as t
where t.noin = bukuangkby.noin + 1)"
sSQL = sSQL & " AND bukuangkby.noin< (select Max(s.noin)+1 from
bukuangkby as s)"
sSQL = sSQL & " Order By noin;"

Set r = CurrentDb.OpenRecordset(sSQL)
If r.RecordCount = 1 Then
r.MoveFirst
'return the number
GetNextNumber = r.Fields(0)
Else
' no number - let them know
MsgBox "ERROR! Number not generated!!"
End If

'clean up
r.Close
Set r = Nothing

End Function
'--------code end -------------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Frank Situmorang" wrote:

"Anggota Jemaat" and for SS member( Sabbath School Member/not baptized yet)
we do not assingn the number ( live it blank/zero).

Thi is the VBA to add the next number if we fill the member type as Hurch
member
Private Sub AngtType_AfterUpdate()
If AngtType = "Anggota Jemaat" Then
NoInd = Nz(DMax("[NoIn]", "bukuangkby")) + 1
End If
If Not IsNull(Me.[AngtType].OldValue) Then
If Me.[AngtType] <> Me.[AngtType].OldValue Then
If MsgBox("Anda telah merobah!!, apakah sengaja mau merobah?",
vbYesNo + vbDefaultButton2 + vbQuestion) = vbNo Then
' Undo the changes
Me.AngtType = Me.AngtType.OldValue
End If
End If
End If
End Sub

My question is what is the VBA if later we find out there is mischoice, it
should have been SS member we want to make it to ZERO.

Since the record now is among many records, what is the VBA so that system
will propose again this unused number is the next fill of church member.

Thanks for any idea provided.

--
H. Frank Situmorang
.