VBA in the form for 2 kinds of membership

[Frank Situmorang]

Hello,

In my church membership table I have 2 members category, my problem is how
to write VBA in order to:
1. every time i fill in the Cat1 membership computer will assign a number
"Last number of this category" +1
2. every time I fill in the Cat 1 membership number, computer will assign
number " Last nubmer of this category +1

Based of my form is a table/query which is linked by category and membership
table

Thanks in advance

 

[Al Campagna]

Frank,
On the AfterUpdate event of MemberType...
(I'm assuming MemberType is Text)
NZ(DMax("[MemberNo]","tblYourTable","MemberType = '" & MemberType &"'")) +1

Couldn't test, but that should do it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Frank Situmorang]

Quite complicated Al, because when Cat 1 is already baptized, he/she becomes
CAT2, what happened to the its number later. i do not need to make another
record, juts to update the same record.

Maybe the best way I will live the MemberNo ( in our langguage we call it
Nomor INduk) blank? coz we aready have member_id for each member

I appreciate your idea.

Many thanks

--
H. Frank Situmorang

Frank,
On the AfterUpdate event of MemberType...
(I'm assuming MemberType is Text)
NZ(DMax("[MemberNo]","tblYourTable","MemberType = '" & MemberType &"'")) +1

Couldn't test, but that should do it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Hello,

In my church membership table I have 2 members category, my problem is how
to write VBA in order to:
1. every time i fill in the Cat1 membership computer will assign a number
"Last number of this category" +1
2. every time I fill in the Cat 1 membership number, computer will assign
number " Last nubmer of this category +1

Based of my form is a table/query which is linked by category and
membership
table

Thanks in advance

 

[Frank Situmorang]

Al,
In fact the situation in my church is this:

I will only have the Registration No for members that are already baptized,
however for the sake of complete data base per familty, I have all members
and the running number is Record NO.

Now I want Reg. Number to be automatically ADDED when the member type is
"Anggota Jemaat" and for "Angggota Sekolah Sabat" the Reg.Number will remain
blank ( not required). Can your fommula still work?

Thanks very much for your help


--
H. Frank Situmorang

Frank,
On the AfterUpdate event of MemberType...
(I'm assuming MemberType is Text)
NZ(DMax("[MemberNo]","tblYourTable","MemberType = '" & MemberType &"'")) +1

Couldn't test, but that should do it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Hello,

In my church membership table I have 2 members category, my problem is how
to write VBA in order to:
1. every time i fill in the Cat1 membership computer will assign a number
"Last number of this category" +1
2. every time I fill in the Cat 1 membership number, computer will assign
number " Last nubmer of this category +1

Based of my form is a table/query which is linked by category and
membership
table

Thanks in advance

 

[Al Campagna]

Frank,
It's very confusing... sounds like your dealing with different fields,
and different logic in this question, as compared to your intial post. I
understand that there may be a language problem, so I'll do my best to
understand this latest question.
You wrote...

Now I want Reg. Number to be automatically ADDED when the member type is
"Anggota Jemaat"

Do you mean that you want the RegNo to be incremented... ex. add one to
the max of RegNo?

Private Sub MemberType_AfterUpdate()
If MemberType = "Anggota Jemaat" Then
RegNo = NZ(DMax("[RegNo]","tblYourTable")) +1
End If
End Sub

If you enter "Anggota Jemaat" in the MemberType field, the RegNo on that
record will be updated to the next RegNo value.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Al,
In fact the situation in my church is this:

I will only have the Registration No for members that are already
baptized,
however for the sake of complete data base per familty, I have all members
and the running number is Record NO.

Now I want Reg. Number to be automatically ADDED when the member type is
"Anggota Jemaat" and for "Angggota Sekolah Sabat" the Reg.Number will
remain
blank ( not required). Can your fommula still work?

Thanks very much for your help


--
H. Frank Situmorang

Frank,
On the AfterUpdate event of MemberType...
(I'm assuming MemberType is Text)
NZ(DMax("[MemberNo]","tblYourTable","MemberType = '" & MemberType &"'"))
+1

Couldn't test, but that should do it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Hello,

In my church membership table I have 2 members category, my problem is
how
to write VBA in order to:
1. every time i fill in the Cat1 membership computer will assign a
number
"Last number of this category" +1
2. every time I fill in the Cat 1 membership number, computer will
assign
number " Last nubmer of this category +1

Based of my form is a table/query which is linked by category and
membership
table

Thanks in advance

 

[Frank Situmorang]

Al, I have tested the data, I change the member Type (AngType) to be "
Anggota SS" ( SabbathSchool Member) it will not change the table.

While in practice the user could wrongly fileed in the type, so he found
later it is not the Anggota Jemat( the one which was baptized). and Secretary
will change is.

So what what would be the VBA, will it be in the Before update maybe?
Or maybe I change the method?, I changed it manual fill?, The problem is
that we do not know what is the latest number, because in this data base we
have 2 type of member ( anggota Jemaat who was baptized) needs NOIN ( Reg
Number) Anggota SS ( Which is not baptized yet) Needs no Reg.Number.

Below is my VBA:
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

--
H. Frank Situmorang

Frank,
It's very confusing... sounds like your dealing with different fields,
and different logic in this question, as compared to your intial post. I
understand that there may be a language problem, so I'll do my best to
understand this latest question.
You wrote...

Now I want Reg. Number to be automatically ADDED when the member type is
"Anggota Jemaat"

Do you mean that you want the RegNo to be incremented... ex. add one to
the max of RegNo?

Private Sub MemberType_AfterUpdate()
If MemberType = "Anggota Jemaat" Then
RegNo = NZ(DMax("[RegNo]","tblYourTable")) +1
End If
End Sub

If you enter "Anggota Jemaat" in the MemberType field, the RegNo on that
record will be updated to the next RegNo value.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Al,
In fact the situation in my church is this:

I will only have the Registration No for members that are already
baptized,
however for the sake of complete data base per familty, I have all members
and the running number is Record NO.

Now I want Reg. Number to be automatically ADDED when the member type is
"Anggota Jemaat" and for "Angggota Sekolah Sabat" the Reg.Number will
remain
blank ( not required). Can your fommula still work?

Thanks very much for your help


--
H. Frank Situmorang

Frank,
On the AfterUpdate event of MemberType...
(I'm assuming MemberType is Text)
NZ(DMax("[MemberNo]","tblYourTable","MemberType = '" & MemberType &"'"))
+1

Couldn't test, but that should do it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


Hello,

In my church membership table I have 2 members category, my problem is
how
to write VBA in order to:
1. every time i fill in the Cat1 membership computer will assign a
number
"Last number of this category" +1
2. every time I fill in the Cat 1 membership number, computer will
assign
number " Last nubmer of this category +1

Based of my form is a table/query which is linked by category and
membership
table

Thanks in advance