Records Count

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Why does this does not work.... When I run thios Code on a Form... it
automaticly changes the end of the Code from "End Sub" to "End Function"

Is there another alternative to extract the value of the Record Count
Function "DCount" to utilize it on a LostFocus Event???

Private Sub Personel_Type_LostFocus()

Public Function IdCount(ByVal strPT As String) As Integer
IdCountLO = DCount("[Personnel Type]", "Personel Data Table", _
"[Personnel Type] = '" & strPT)


If Personnel_Type.Value = "Loan Officer" Then
: IdCount = "Loan Officer"
ID.Value = IdCount + 101
End If
If Personnel_Type.Value = "Mortgage Broker" Then
: IdCount = "Mortgage Broker"
ID.Value = IdCount + 201
End If
If Personnel_Type.Value = "Loan Processor" Then
: IdCount = "Loan Processor"
ID.Value = IdCount + 301
End If
If Personnel_Type.Value = "Admin Staff" Then
: IdCount = "Admin Staff"
ID.Value = IdCount + 401
End If


End Sub
 
There are a number of problems. First, to answer your question about the
changing of the End Sub to End Functoin is because you are trying to embedd a
function in a sub. There is no End Sub for the
Private Sub Personel_Type_LostFocus()

If what you are trying to do is call the function from the Lost Focus event
of the Personel_Type control, it should be:

Private Sub Personel_Type_LostFocus()
Me.ID = IdCount(strPt)
End Sub

You don't need to use the Value of the ID control, but it you should specify
the form.

In reality, there is no need to create a separate function here unless you
will be calling it from another place in your application. And, if the other
place is not in your form, then it should be a Public function in a standard
module. And, I will show you how to use the Select Case statement. It is
much better for a situation where you want to take different paths based on
the value of a control, a variable, or a field.

Also, single line If statements using : are a very bad habit. It makes the
code more difficult to read. Use a multiline structure and indenting to make
to code clearly understandable.

It is not necessary to specify a field in a DCount and in fact makes it
slower.
In the DCount function, you only have one delimiter for strPT, you need two.

Your function is defined as an Intger, but you are trying to put a string in
it. I am confused about what you are really wanting to return, but it
appears you really do want to return the numeric value, so I will write it
that way.

Public Function IdCount(ByVal strPT As String) As Integer

IdCountLO = DCount("*", "Personel Data Table", _
"[Personnel Type] = '" & strPT & "'")

Select Case Me.Personnel_Type
Case "Loan Officer"
IdCount = 101
Case "Mortgage Broker"
IdCount = 201
Case "Loan Processor"
IdCount = 301
Case "Admin Staff"
IdCount = 401
End Select
End Function

I still don't know if this will solve the problem. It is unclear what you
are doing with ID or IdCountLo.

Other than that, it looks fine :)

--
Dave Hargis, Microsoft Access MVP


HELPMEMEME said:
Why does this does not work.... When I run thios Code on a Form... it
automaticly changes the end of the Code from "End Sub" to "End Function"

Is there another alternative to extract the value of the Record Count
Function "DCount" to utilize it on a LostFocus Event???

Private Sub Personel_Type_LostFocus()

Public Function IdCount(ByVal strPT As String) As Integer
IdCountLO = DCount("[Personnel Type]", "Personel Data Table", _
"[Personnel Type] = '" & strPT)


If Personnel_Type.Value = "Loan Officer" Then
: IdCount = "Loan Officer"
ID.Value = IdCount + 101
End If
If Personnel_Type.Value = "Mortgage Broker" Then
: IdCount = "Mortgage Broker"
ID.Value = IdCount + 201
End If
If Personnel_Type.Value = "Loan Processor" Then
: IdCount = "Loan Processor"
ID.Value = IdCount + 301
End If
If Personnel_Type.Value = "Admin Staff" Then
: IdCount = "Admin Staff"
ID.Value = IdCount + 401
End If


End Sub
Click to expand...
 
Ok... I think I understand what you said...

Here is the scenario of what I wanted it to do....

When "Loan Officer" is selected from a Drop down list.... and then the Focus
is Lost then the "ID" field will be enumerated in a range in between 100 and
199... but in order to still keep it unique I have to make sure there are no
other records with that number... (Virtualy undeletable records if I may
add)... so I resourced to the Dcount as an Option... in order to count the
amount of records in the "Personel Data Table" that containing the Value
"Loan Officer" under the "Personel Type Field"... and so returning the number
of records so that I may add 101... for the new record... making sure that
way my New Loan Officer gets an Id around the 100's :)

The porpuse is to make sure that my Loan Officers Get an ID on the 100's

Mortgage Brokers on the 200's

and so on...

Sorry About "IdCountLO" it was a typo of the first way I tried to do
this... its really "IdCount"... with the intent to relate it directly to the
Function "IdCount" if it was ment to work like that that is...

Is hard for me to see what u did on the second code you wrote... maybe if
you can explain what the Code Line Case is doing in particular... :)

Thank you so much Mr. Klatuu...



Klatuu said:
There are a number of problems. First, to answer your question about the
changing of the End Sub to End Functoin is because you are trying to embedd a
function in a sub. There is no End Sub for the
Private Sub Personel_Type_LostFocus()

If what you are trying to do is call the function from the Lost Focus event
of the Personel_Type control, it should be:

Private Sub Personel_Type_LostFocus()
Me.ID = IdCount(strPt)
End Sub

You don't need to use the Value of the ID control, but it you should specify
the form.

In reality, there is no need to create a separate function here unless you
will be calling it from another place in your application. And, if the other
place is not in your form, then it should be a Public function in a standard
module. And, I will show you how to use the Select Case statement. It is
much better for a situation where you want to take different paths based on
the value of a control, a variable, or a field.

Also, single line If statements using : are a very bad habit. It makes the
code more difficult to read. Use a multiline structure and indenting to make
to code clearly understandable.

It is not necessary to specify a field in a DCount and in fact makes it
slower.
In the DCount function, you only have one delimiter for strPT, you need two.

Your function is defined as an Intger, but you are trying to put a string in
it. I am confused about what you are really wanting to return, but it
appears you really do want to return the numeric value, so I will write it
that way.

Public Function IdCount(ByVal strPT As String) As Integer

IdCountLO = DCount("*", "Personel Data Table", _
"[Personnel Type] = '" & strPT & "'")

Select Case Me.Personnel_Type
Case "Loan Officer"
IdCount = 101
Case "Mortgage Broker"
IdCount = 201
Case "Loan Processor"
IdCount = 301
Case "Admin Staff"
IdCount = 401
End Select
End Function

I still don't know if this will solve the problem. It is unclear what you
are doing with ID or IdCountLo.

Other than that, it looks fine :)

--
Dave Hargis, Microsoft Access MVP


HELPMEMEME said:
Why does this does not work.... When I run thios Code on a Form... it
automaticly changes the end of the Code from "End Sub" to "End Function"

Is there another alternative to extract the value of the Record Count
Function "DCount" to utilize it on a LostFocus Event???

Private Sub Personel_Type_LostFocus()

Public Function IdCount(ByVal strPT As String) As Integer
IdCountLO = DCount("[Personnel Type]", "Personel Data Table", _
"[Personnel Type] = '" & strPT)


If Personnel_Type.Value = "Loan Officer" Then
: IdCount = "Loan Officer"
ID.Value = IdCount + 101
End If
If Personnel_Type.Value = "Mortgage Broker" Then
: IdCount = "Mortgage Broker"
ID.Value = IdCount + 201
End If
If Personnel_Type.Value = "Loan Processor" Then
: IdCount = "Loan Processor"
ID.Value = IdCount + 301
End If
If Personnel_Type.Value = "Admin Staff" Then
: IdCount = "Admin Staff"
ID.Value = IdCount + 401
End If


End Sub
Click to expand...
Click to expand...
 
Okay, There is a better way to do this. I will explain, but first, about the
Select Case. I would suggest reading up on it in VBA Help. It explains it
pretty well, but basically, each Case line specifies a value to look for in
the Select Case line.
So, it looks at the Personnel_Type control. It starts down the list
comparing each value to the object in the Select Case line. It then executes
the code in the lines below the case statement. It stops executing code when
it hits the next Case line.
If none of the comparisons evaluate to True, it does nothing unless you
include a Case Else line as the last Case line before the End Select line.
Then if all conditions are false, the code for the Else will execute.

Now, as to creating an id number for each employee based on job type (what
happens if someone gets promoted?), you can do that more efficiently like
this:
It should be in the After Update event. What is happening here is we are
looking to see what the highest number is for any employee int the selected
Personnel type. We use the Nz function so that if no employee of that type
exists, it will return 0 instread of Null. We then add 1 to it to make it 1
more than the existing highest number. Then we test to see if it is 1. If
so, that means this is the first employee of that type. In this case, we
need to add the appropriate number based on the personnel type to get the
number we want.

Private Sub Personel_Type_AfterUpdate()
Dim IdNumber as Long

idNumber = Nz(DMax("[ID]", "tblEmployee", "[Personnel_Type] = '" &
Me.Personnel_Type & "'"), 0) +1

If idNumber = 1 Then
Select Case Me.Personnel_Type
Case "Loan Officer"
IdCount = idCount + 100
Case "Mortgage Broker"
IdCount = idCount + 200
Case "Loan Processor"
IdCount = idCount + 300
Case "Admin Staff"
IdCount = idCount + 400
End Select
End If
Me.Id = idCount
End Sub
---
Dave Hargis, Microsoft Access MVP


HELPMEMEME said:
Ok... I think I understand what you said...

Here is the scenario of what I wanted it to do....

When "Loan Officer" is selected from a Drop down list.... and then the Focus
is Lost then the "ID" field will be enumerated in a range in between 100 and
199... but in order to still keep it unique I have to make sure there are no
other records with that number... (Virtualy undeletable records if I may
add)... so I resourced to the Dcount as an Option... in order to count the
amount of records in the "Personel Data Table" that containing the Value
"Loan Officer" under the "Personel Type Field"... and so returning the number
of records so that I may add 101... for the new record... making sure that
way my New Loan Officer gets an Id around the 100's :)

The porpuse is to make sure that my Loan Officers Get an ID on the 100's

Mortgage Brokers on the 200's

and so on...

Sorry About "IdCountLO" it was a typo of the first way I tried to do
this... its really "IdCount"... with the intent to relate it directly to the
Function "IdCount" if it was ment to work like that that is...

Is hard for me to see what u did on the second code you wrote... maybe if
you can explain what the Code Line Case is doing in particular... :)

Thank you so much Mr. Klatuu...



Klatuu said:
There are a number of problems. First, to answer your question about the
changing of the End Sub to End Functoin is because you are trying to embedd a
function in a sub. There is no End Sub for the
Private Sub Personel_Type_LostFocus()

If what you are trying to do is call the function from the Lost Focus event
of the Personel_Type control, it should be:

Private Sub Personel_Type_LostFocus()
Me.ID = IdCount(strPt)
End Sub

You don't need to use the Value of the ID control, but it you should specify
the form.

In reality, there is no need to create a separate function here unless you
will be calling it from another place in your application. And, if the other
place is not in your form, then it should be a Public function in a standard
module. And, I will show you how to use the Select Case statement. It is
much better for a situation where you want to take different paths based on
the value of a control, a variable, or a field.

Also, single line If statements using : are a very bad habit. It makes the
code more difficult to read. Use a multiline structure and indenting to make
to code clearly understandable.

It is not necessary to specify a field in a DCount and in fact makes it
slower.
In the DCount function, you only have one delimiter for strPT, you need two.

Your function is defined as an Intger, but you are trying to put a string in
it. I am confused about what you are really wanting to return, but it
appears you really do want to return the numeric value, so I will write it
that way.

Public Function IdCount(ByVal strPT As String) As Integer

IdCountLO = DCount("*", "Personel Data Table", _
"[Personnel Type] = '" & strPT & "'")

Select Case Me.Personnel_Type
Case "Loan Officer"
IdCount = 101
Case "Mortgage Broker"
IdCount = 201
Case "Loan Processor"
IdCount = 301
Case "Admin Staff"
IdCount = 401
End Select
End Function

I still don't know if this will solve the problem. It is unclear what you
are doing with ID or IdCountLo.

Other than that, it looks fine :)

--
Dave Hargis, Microsoft Access MVP


HELPMEMEME said:
Why does this does not work.... When I run thios Code on a Form... it
automaticly changes the end of the Code from "End Sub" to "End Function"

Is there another alternative to extract the value of the Record Count
Function "DCount" to utilize it on a LostFocus Event???

Private Sub Personel_Type_LostFocus()

Public Function IdCount(ByVal strPT As String) As Integer
IdCountLO = DCount("[Personnel Type]", "Personel Data Table", _
"[Personnel Type] = '" & strPT)


If Personnel_Type.Value = "Loan Officer" Then
: IdCount = "Loan Officer"
ID.Value = IdCount + 101
End If
If Personnel_Type.Value = "Mortgage Broker" Then
: IdCount = "Mortgage Broker"
ID.Value = IdCount + 201
End If
If Personnel_Type.Value = "Loan Processor" Then
: IdCount = "Loan Processor"
ID.Value = IdCount + 301
End If
If Personnel_Type.Value = "Admin Staff" Then
: IdCount = "Admin Staff"
ID.Value = IdCount + 401
End If


End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top