RE: Next Number and Unused Registration Number

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Frank,

Yes you can. Open the form in design view. Click on the control "NoInd" and
open properties. On the "DATA" tab, set the property LOCKED to "YES".

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


"Frank Situmorang" wrote:

Steve,

I fortgot to ask you this.

Can I block the control NoInd ( Registration No) to avoid user to fill it
manually?

Thanks in advance for your advice
--
H. Frank Situmorang


"Steve Sanford" wrote:

Frank,

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

The "***" is how I show new (added) lines.

The comments ' 1, ' 2 ,... is how I keep track of nested "If...ELSE...END
IF" statements.

Example:
IF Then ' 1
IF Then '2
IF Then ' 3
End IF '3
End if ' 2
Else ' 1

End if ' 1


Also should I put the function in the module of the database or leave it as
a sub?

Your code is a SUB. Replace your current Sub with the one in my post.


Mine is a FUNCTION. It MUST stay as a Function.

Paste the "FUNCTION GetNumber()" below the Sub AngtType_AfterUpdate()

Just like you see it in the post.

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




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
.

Relevant Pages

  • RE: Next Number and Unused Registration Number
    ... Thanks Steve it is OK now. ... Your code is a SUB. ... Public Function GetNextNumber() ... Thi is the VBA to add the next number if we fill the member type as Hurch ...
    (microsoft.public.access.forms)
  • Re: Sues Thanksgiving 2008 Trip - Day 6 of 7
    ... Who: Sue, DVC member ... Plan: Stormalong Bay, Epcot ... Steve P, Angela and Layden! ... Carol & me, and then did the same to Steve & Layden, lol! ...
    (rec.arts.disney.parks)
  • Re: Copying text from an editable text box to a table cell
    ... Thanks Steve, ... Sub DemoIt() ... separate tables on slide 3 of the same presentation. ... Dim oSource_table As Table ...
    (microsoft.public.powerpoint)
  • Re: Modules not working or Its impossible to do the task
    ... There *was* a default value of zero ... > I'm sorry, Steve, but in the interest of accuracy and to avoid confusion I ... > Public Sub TestIt() ... > GlobalSign digital certificate is a forgery and should be deleted without ...
    (microsoft.public.access.forms)
  • Re: An error handler problem ...
    ... Actually Steve, what I'm describing is well known to the long-time VB ... I don't know what your language experience is like but ... might as well code it as a sub. ... > examining the Err object in my present code, it had been zeroed out by ...
    (microsoft.public.vb.enterprise)