Re: Expression in SQL or user-defined function?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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.

"Klatuu" <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:1E5C98C8-34D8-43CD-9A7B-FF100BEBDEF0@xxxxxxxxxxxxxxxx
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


"BruceM" wrote:

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.



.

Relevant Pages

  • Re: Expression in SQL or user-defined function?
    ... SeqLetter comes from the subform, and is assigned automatically when a new subform record is created, so no problem there. ... standard query, and you are calling it from an event in a subform, it is just ... Public Function EquipNumber (strDeptCode as String, lngSeqNumber as Long, ... Equipment for the machining department is>> numbered ...
    (microsoft.public.access.formscoding)
  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Need help with Code Please!!!
    ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)
  • Re: Need help with Code Please!!!
    ... putting the query SQL itself in here maybe it will help you understand what I ... the actual string that gets built at the end of the SQ1 build process. ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ... Dim qd As QueryDef 'object ref to query item ...
    (microsoft.public.access.formscoding)
  • Re: Need help with Code Please!!!
    ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)