RE: Next Number and Unused Registration Number


Thanks Steve for your quite addvance VBA, I am affraid that I do not
understand well. Because we are Indonesian not so good in English

Do you mean to say that we can reuse the number for the next record?. I want
to make sure that we have the same meaning, because if I finish this
database, we have more than 100 churches and I want to donate my work to them.

Let me illustrate it.

Chruch clerck filled out the form in Mar 7, 2008 as follows:

Rec.No. NoInd Angtype Name
-------- -------- --------- --------
1 SS Member Boaz
2 1 Anggota Jemaat Sarah
3 2 Anggota Jemaat Elfrida
4. SS Member Henoch
5. 3 Anggota Jemaat Elyana

Later in Marh 9, 2008 Church clerck realized, Alas!! I mischoosed ( I used
combo on this field to choose) Elfrida should have been SS member, than he
changed it what we want is to make it blank or zero again

In March 10, church clerck fills in for the new member Johannes, and the
sistem will propose to use this Noind = 2, therefore the record wil show
Rec.No. NoInd Angtype Name
-------- -------- --------- --------
6. 2 Anggota Jemaat Johannes

Is this the VBA that you gave can do?

Sorry Steve to make you bussy, my expert in fact is Accounting, I am just
self thought on this. Yes... I took 30 year ago basic langguage for short
courxe but I alredy forgot it

Thanks for your help Steve

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