Steve,
NoInd is the name of the control, while NOIN is the field name.
Pls. disregard the workd "name", that is not the name of the actual field, I
just want to illustrate, the requirement of what I meant reusing the number
if in the middle of the road a clerck changed the member ype( AngtType)
I will try to put your codes
Thanks a lot for your help.
--
H. Frank Situmorang
:
Do not worry. Your English is much, much better than my Indonesian.
You said that the code you posted is working, so I did not want to change it
too much. If you change "Angtype" from "Anggota Jemaat" to "SS Member", the
value in
In table "bukuangkby", it looks like there is a field named "noin". But in
the code, what is "NoInd"? A field or a control? I think it is the field in
the table. So the code will have to be changed. See below.
By the way, "Name" is a reserved word in Access and shouldn't be used for
object names. Also, it is not descriptive - Name of what? A better field name
would be "FamilyName" or "LastName" or "L_Name".
**************
Do you mean to say that we can reuse the number for the next record?
Yes, you can reuse the number.
Since you provided example data, I modified my test database and tested the
code.
If I have the correct field names, the code works.
Here is the code again. Use this code:
(watch for line wrap)
'--------New code-----------------
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
'--------New code--------------
HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
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
:
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
rivate Sub AngtType_AfterUpdate()yellow color and it hangs and