Expression in SQL or user-defined function?

[BruceM]

I have a main table for EquipmentType, with a child table for EquipmentItem.
The equipment is identified in EquipmentType by department code and an
incremented number. Equipment for the machining department is numbered
M-0001, M-0002, etc. For welding, W-0001, etc.
The related EquipmentItem table contains a letter. Let's say that M-0001 is
a clamp, and that there are three of them. They are numbered M-0001-A,
M-0001-B, and M-0001-C. When a new clamp is needed, it will be M-0001-D.
In order that the incrementing may be done automatically, the letter is
actually stored as a number, and converted to a letter for display. The
outline of how I go about this is as follows:

varSeqLetter and varSeqCount are declared as variants in the subform's code
module Declarations. strSeqNumber and strDeptCode are declared as strings.
In the subform's Current event:

' Find the highest SeqLetter for this equipment
varSeqLetter = DMax("SeqLetter", "tblEquipItem", _
"EI_EquipID = " & Me.Parent.EquipID)
' Determine whether this is the first record using this EquipID
varSeqCount = DCount("*", "tblEquipItem", _
"EI_EquipID = " & Me.Parent.EquipID)

strSeqNumber = Format(Me.Parent.SeqNumber, "0000")
strDeptCode = Me.Parent.T_DeptCode

' User-defined function in the subform's module
SequenceLetter

Public Function SequenceLetter()

' Chr(65) = "A", so 1 is rendered as "A", 2 as "B", etc.
SequenceLetter = Chr(varSeqLetter + 64)

' Place the full number into an unbound text box on the main form
Me.Parent.txtEquipNumber = "Equipment ID #" & _
strDeptCode & "-" & strSeqNumber & "-" & _
SequenceLetter

End Function

Perhaps I should explain that the actual code is more involved. For
instance, SequenceLetter contains provisions to go from "Z" to "AA", then
all the way to "ZZ"; otherwise I would just use the expression
Chr(varSeqLetter + 64) rather than creating the function.

This works as intended in this situation. However, this number will need to
appear in many different forms and reports. The form/subform structure as
described presents the information in this way:
M-0001
A
B
C
M-0002
A
W-0001
A

Another way of presenting the information is to combine the two tables into
one query (I will call it qryListing) so that there is no distinction
between main records and related records:
M-0001-A
M-0001-B
etc.

I could create an expression in the query to convert M, 1, and 1 to
M-0001-A, etc., but I wonder if it is the best way, or whether instead I
should use a user-defined function to do the combining. If I use a
user-defined function I suppose it would include arguments along the lines
of:
FullNumber(DeptCode as String, SeqNumber as Long, SeqLetter as Long) as
String
but I am wandering into rather new territory with this approach. I have
been experimenting, and can pass the variables to the function in a
particular situation such as in the subform code, but I am having trouble
coming up with a way to pass the variables in a variety of situations, such
as in the query qryListing as described above.

 

[Klatuu]

Since you have a function that works for forms and reports, you can use the
same function in a query. This will give you consistency and make your
maintenance easier. To call the function from a query, create a calculated
field with the function in the calculated field and pass the fields necessary
to the function. The function must be a public function in a standard module:

EquipItem: FullNumber([DeptCode], [SeqNumber], [SeqLetter])

I just left your names, but what you want in each of the arguments is the
name of the field in the table/query that relates to those data items.

 

[BruceM]

Thanks for the reply. I was overcomplicating it in part, because when I did
as you suggested in a query that combines both tables everything worked as
it should. What I seemed to be stumbling over was how to pass the variables
to the function in the subform's Current event.

tblEquipType
EquipID (PK)
DeptCode
SeqNumber

tblEquipItem
EquipItemID (PK)
EquipID (FK)
SeqLetter

In my function I have something like this:

Public Function EquipNumber (strDeptCode as String, lngSeqNumber as Long,
lngSeqLetter as Long) as
String

EquipNumber = strDeptCode & "-" & lngSeqNumber & "-" Chr(lngSeqLetter +
64)

End Function

In the query I can have:
FullNumber: EquipNumber([DeptCode],[SeqNumber],[SeqLetter])

However, in the subform the variables for strDeptCode and lngSeqNumber are
in the main form, so I suppose I just need to grab those values from the
main form and pass them to the function as either a string or long, as
needed. I think I understand, but my brain must have locked up. I am at
the end of my work day, but I will give it a try tomorrow and let you know
how it goes.

Since you have a function that works for forms and reports, you can use
the
same function in a query. This will give you consistency and make your
maintenance easier. To call the function from a query, create a
calculated
field with the function in the calculated field and pass the fields
necessary
to the function. The function must be a public function in a standard
module:

EquipItem: FullNumber([DeptCode], [SeqNumber], [SeqLetter])

I just left your names, but what you want in each of the arguments is the
name of the field in the table/query that relates to those data items.

--
Dave Hargis, Microsoft Access MVP

I have a main table for EquipmentType, with a child table for
EquipmentItem.
The equipment is identified in EquipmentType by department code and an
incremented number. Equipment for the machining department is numbered
M-0001, M-0002, etc. For welding, W-0001, etc.
The related EquipmentItem table contains a letter. Let's say that M-0001
is
a clamp, and that there are three of them. They are numbered M-0001-A,
M-0001-B, and M-0001-C. When a new clamp is needed, it will be M-0001-D.
In order that the incrementing may be done automatically, the letter is
actually stored as a number, and converted to a letter for display. The
outline of how I go about this is as follows:

varSeqLetter and varSeqCount are declared as variants in the subform's
code
module Declarations. strSeqNumber and strDeptCode are declared as
strings.
In the subform's Current event:

' Find the highest SeqLetter for this equipment
varSeqLetter = DMax("SeqLetter", "tblEquipItem", _
"EI_EquipID = " & Me.Parent.EquipID)
' Determine whether this is the first record using this EquipID
varSeqCount = DCount("*", "tblEquipItem", _
"EI_EquipID = " & Me.Parent.EquipID)

strSeqNumber = Format(Me.Parent.SeqNumber, "0000")
strDeptCode = Me.Parent.T_DeptCode

' User-defined function in the subform's module
SequenceLetter

Public Function SequenceLetter()

' Chr(65) = "A", so 1 is rendered as "A", 2 as "B", etc.
SequenceLetter = Chr(varSeqLetter + 64)

' Place the full number into an unbound text box on the main form
Me.Parent.txtEquipNumber = "Equipment ID #" & _
strDeptCode & "-" & strSeqNumber & "-" & _
SequenceLetter

End Function

Perhaps I should explain that the actual code is more involved. For
instance, SequenceLetter contains provisions to go from "Z" to "AA", then
all the way to "ZZ"; otherwise I would just use the expression
Chr(varSeqLetter + 64) rather than creating the function.

This works as intended in this situation. However, this number will need
to
appear in many different forms and reports. The form/subform structure
as
described presents the information in this way:
M-0001
A
B
C
M-0002
A
W-0001
A

Another way of presenting the information is to combine the two tables
into
one query (I will call it qryListing) so that there is no distinction
between main records and related records:
M-0001-A
M-0001-B
etc.

I could create an expression in the query to convert M, 1, and 1 to
M-0001-A, etc., but I wonder if it is the best way, or whether instead I
should use a user-defined function to do the combining. If I use a
user-defined function I suppose it would include arguments along the
lines
of:
FullNumber(DeptCode as String, SeqNumber as Long, SeqLetter as Long) as
String
but I am wandering into rather new territory with this approach. I have
been experimenting, and can pass the variables to the function in a
particular situation such as in the subform code, but I am having trouble
coming up with a way to pass the variables in a variety of situations,
such
as in the query qryListing as described above.

 

[Klatuu]

Okay. You are pretty close. Assuming the function is a public function in a
standard query, and you are calling it from an event in a subform, it is just
a matter of syntax.

FullNumber:
EquipNumber(Me.Parent!txtDeptCode,Me.Parent!txtSeqNumber,Me.Parent!SeqLetter)

Or something to that effect.
--
Dave Hargis, Microsoft Access MVP

Thanks for the reply. I was overcomplicating it in part, because when I did
as you suggested in a query that combines both tables everything worked as
it should. What I seemed to be stumbling over was how to pass the variables
to the function in the subform's Current event.

tblEquipType
EquipID (PK)
DeptCode
SeqNumber

tblEquipItem
EquipItemID (PK)
EquipID (FK)
SeqLetter

In my function I have something like this:

Public Function EquipNumber (strDeptCode as String, lngSeqNumber as Long,
lngSeqLetter as Long) as
String

EquipNumber = strDeptCode & "-" & lngSeqNumber & "-" Chr(lngSeqLetter +
64)

End Function

In the query I can have:
FullNumber: EquipNumber([DeptCode],[SeqNumber],[SeqLetter])

However, in the subform the variables for strDeptCode and lngSeqNumber are
in the main form, so I suppose I just need to grab those values from the
main form and pass them to the function as either a string or long, as
needed. I think I understand, but my brain must have locked up. I am at
the end of my work day, but I will give it a try tomorrow and let you know
how it goes.

Since you have a function that works for forms and reports, you can use
the
same function in a query. This will give you consistency and make your
maintenance easier. To call the function from a query, create a
calculated
field with the function in the calculated field and pass the fields
necessary
to the function. The function must be a public function in a standard
module:

EquipItem: FullNumber([DeptCode], [SeqNumber], [SeqLetter])

I just left your names, but what you want in each of the arguments is the
name of the field in the table/query that relates to those data items.

--
Dave Hargis, Microsoft Access MVP

I have a main table for EquipmentType, with a child table for
EquipmentItem.
The equipment is identified in EquipmentType by department code and an
incremented number. Equipment for the machining department is numbered
M-0001, M-0002, etc. For welding, W-0001, etc.
The related EquipmentItem table contains a letter. Let's say that M-0001
is
a clamp, and that there are three of them. They are numbered M-0001-A,
M-0001-B, and M-0001-C. When a new clamp is needed, it will be M-0001-D.
In order that the incrementing may be done automatically, the letter is
actually stored as a number, and converted to a letter for display. The
outline of how I go about this is as follows:

varSeqLetter and varSeqCount are declared as variants in the subform's
code
module Declarations. strSeqNumber and strDeptCode are declared as
strings.
In the subform's Current event:

' Find the highest SeqLetter for this equipment
varSeqLetter = DMax("SeqLetter", "tblEquipItem", _
"EI_EquipID = " & Me.Parent.EquipID)
' Determine whether this is the first record using this EquipID
varSeqCount = DCount("*", "tblEquipItem", _
"EI_EquipID = " & Me.Parent.EquipID)

strSeqNumber = Format(Me.Parent.SeqNumber, "0000")
strDeptCode = Me.Parent.T_DeptCode

' User-defined function in the subform's module
SequenceLetter

Public Function SequenceLetter()

' Chr(65) = "A", so 1 is rendered as "A", 2 as "B", etc.
SequenceLetter = Chr(varSeqLetter + 64)

' Place the full number into an unbound text box on the main form
Me.Parent.txtEquipNumber = "Equipment ID #" & _
strDeptCode & "-" & strSeqNumber & "-" & _
SequenceLetter

End Function

Perhaps I should explain that the actual code is more involved. For
instance, SequenceLetter contains provisions to go from "Z" to "AA", then
all the way to "ZZ"; otherwise I would just use the expression
Chr(varSeqLetter + 64) rather than creating the function.

This works as intended in this situation. However, this number will need
to
appear in many different forms and reports. The form/subform structure
as
described presents the information in this way:
M-0001
A
B
C
M-0002
A
W-0001
A

Another way of presenting the information is to combine the two tables
into
one query (I will call it qryListing) so that there is no distinction
between main records and related records:
M-0001-A
M-0001-B
etc.

I could create an expression in the query to convert M, 1, and 1 to
M-0001-A, etc., but I wonder if it is the best way, or whether instead I
should use a user-defined function to do the combining. If I use a
user-defined function I suppose it would include arguments along the
lines
of:
FullNumber(DeptCode as String, SeqNumber as Long, SeqLetter as Long) as
String
but I am wandering into rather new territory with this approach. I have
been experimenting, and can pass the variables to the function in a
particular situation such as in the subform code, but I am having trouble
coming up with a way to pass the variables in a variety of situations,
such
as in the query qryListing as described above.

 

[BruceM]

Thanks again. In some cases the sub in which the function is called
contains variables I could use for some of the arguments. The only thing I
had to add was to skip running the function if there is a new main form
record, in which case DeptCode and SeqNumber have not yet been established.
SeqLetter comes from the subform, and is assigned automatically when a new
subform record is created, so no problem there.
I don't know why I stumbled over this, as it seems straightforward enough
now. I appreciate your comments.

Okay. You are pretty close. Assuming the function is a public function
in a
standard query, and you are calling it from an event in a subform, it is
just
a matter of syntax.

FullNumber:
EquipNumber(Me.Parent!txtDeptCode,Me.Parent!txtSeqNumber,Me.Parent!SeqLetter)

Or something to that effect.
--
Dave Hargis, Microsoft Access MVP

Thanks for the reply. I was overcomplicating it in part, because when I
did
as you suggested in a query that combines both tables everything worked
as
it should. What I seemed to be stumbling over was how to pass the
variables
to the function in the subform's Current event.

tblEquipType
EquipID (PK)
DeptCode
SeqNumber

tblEquipItem
EquipItemID (PK)
EquipID (FK)
SeqLetter

In my function I have something like this:

Public Function EquipNumber (strDeptCode as String, lngSeqNumber as Long,
lngSeqLetter as Long) as
String

EquipNumber = strDeptCode & "-" & lngSeqNumber & "-" Chr(lngSeqLetter
+
64)

End Function

In the query I can have:
FullNumber: EquipNumber([DeptCode],[SeqNumber],[SeqLetter])

However, in the subform the variables for strDeptCode and lngSeqNumber
are
in the main form, so I suppose I just need to grab those values from the
main form and pass them to the function as either a string or long, as
needed. I think I understand, but my brain must have locked up. I am at
the end of my work day, but I will give it a try tomorrow and let you
know
how it goes.

Since you have a function that works for forms and reports, you can use
the
same function in a query. This will give you consistency and make your
maintenance easier. To call the function from a query, create a
calculated
field with the function in the calculated field and pass the fields
necessary
to the function. The function must be a public function in a standard
module:

EquipItem: FullNumber([DeptCode], [SeqNumber], [SeqLetter])

I just left your names, but what you want in each of the arguments is
the
name of the field in the table/query that relates to those data items.

--
Dave Hargis, Microsoft Access MVP


:

I have a main table for EquipmentType, with a child table for
EquipmentItem.
The equipment is identified in EquipmentType by department code and an
incremented number. Equipment for the machining department is
numbered
M-0001, M-0002, etc. For welding, W-0001, etc.
The related EquipmentItem table contains a letter. Let's say that
M-0001
is
a clamp, and that there are three of them. They are numbered
M-0001-A,
M-0001-B, and M-0001-C. When a new clamp is needed, it will be
M-0001-D.
In order that the incrementing may be done automatically, the letter
is
actually stored as a number, and converted to a letter for display.
The
outline of how I go about this is as follows:

varSeqLetter and varSeqCount are declared as variants in the subform's
code
module Declarations. strSeqNumber and strDeptCode are declared as
strings.
In the subform's Current event:

' Find the highest SeqLetter for this equipment
varSeqLetter = DMax("SeqLetter", "tblEquipItem", _
"EI_EquipID = " & Me.Parent.EquipID)
' Determine whether this is the first record using this EquipID
varSeqCount = DCount("*", "tblEquipItem", _
"EI_EquipID = " & Me.Parent.EquipID)

strSeqNumber = Format(Me.Parent.SeqNumber, "0000")
strDeptCode = Me.Parent.T_DeptCode

' User-defined function in the subform's module
SequenceLetter

Public Function SequenceLetter()

' Chr(65) = "A", so 1 is rendered as "A", 2 as "B", etc.
SequenceLetter = Chr(varSeqLetter + 64)

' Place the full number into an unbound text box on the main form
Me.Parent.txtEquipNumber = "Equipment ID #" & _
strDeptCode & "-" & strSeqNumber & "-" & _
SequenceLetter

End Function

Perhaps I should explain that the actual code is more involved. For
instance, SequenceLetter contains provisions to go from "Z" to "AA",
then
all the way to "ZZ"; otherwise I would just use the expression
Chr(varSeqLetter + 64) rather than creating the function.

This works as intended in this situation. However, this number will
need
to
appear in many different forms and reports. The form/subform
structure
as
described presents the information in this way:
M-0001
A
B
C
M-0002
A
W-0001
A

Another way of presenting the information is to combine the two tables
into
one query (I will call it qryListing) so that there is no distinction
between main records and related records:
M-0001-A
M-0001-B
etc.

I could create an expression in the query to convert M, 1, and 1 to
M-0001-A, etc., but I wonder if it is the best way, or whether instead
I
should use a user-defined function to do the combining. If I use a
user-defined function I suppose it would include arguments along the
lines
of:
FullNumber(DeptCode as String, SeqNumber as Long, SeqLetter as Long)
as
String
but I am wandering into rather new territory with this approach. I
have
been experimenting, and can pass the variables to the function in a
particular situation such as in the subform code, but I am having
trouble
coming up with a way to pass the variables in a variety of situations,
such
as in the query qryListing as described above.