Parsing variables to functions.

  • Thread starter Thread starter Anthony
  • Start date Start date
A

Anthony

Hi

I have two functions (behind command buttons) called FunctionName1 and
FunctionName2 and I want to parse a value from FunctionName1 and a value
from FunctonName2 to another function called "CombinedFieldFunction". The
code below only passes the last value selected but never both at the same
time. How can I amend the following code so that the third functions accepts
both variables from the other two functions.

***************************************
Public Function FunctionName1()
Dim debstr As String
Forms![FormName]![FieldA] = "ContentsFieldA"

debstr = "ANewValue"
CombinedFieldFunction (debstr)
End Function
**********************************
Public Function FunctionName2()
partstr As String
Forms![FormName]![FieldB] = "ContentsFieldB"

partstr = "AnotherValue"
CombinedFieldFunction (partstr)
End Function
************************************
Public Function CombinedFieldFunction(Optional debstr As String, _
Optional partstr As String)

Dim total As String
If debstr <> "" Then
total = debstr & " " & partstr

Else
total = partstr

End If
Forms![FormName]![FieldE] = total
End Function

*********************************
Some more info about what I'm trying to do...

I have a form which contains a number of fields (ie A, B, C and D) which are
populated by clicking on buttons. If values are entered in one or more of
these fields, I want another field (ie E) to automatically contain
'combined' values. These values are not a simple concatenation of the values
entered in fields A, B, C or D, but they are related. For example, I want
the value in field E to be "ANewValue" if the value selected in field A is
"ContentsFieldA" as shown in "FunctionName1". If "ContentsFieldA" is
selected in field A and "ContentsFieldB" is selected in field B, I want the
value in field E to be "ANewValue AnotherValue".

Please tell me where the problem is
Thanks
Anthony
 
Anthony

If I understand what you are trying to do, you don't need to use functions
at all. It looks like you want to show a concatenated value in a control
named [FieldE] (by the way, you don't need to save this to a table if you
already have values for [FieldA] and [FieldB] - just use an expression or a
query to return the concatenated values).

You can add something like the following expression to the ControlSource
property of [FieldE] (your syntax may vary):

= IIF(Nz([FieldA],"")="",Nz([FieldB],""),[FieldA] & " " & [FieldB])

Another approach uses the fact that the "&" and "+" operators handle nulls
differently (but then, you didn't indicate if you are using zero-length
strings or nulls or ...?
 
Hi Jeff

Thanks for your reply. I added your suggested expression into the Control
Source property of Field E and it successfully concatenates the required
fields. However, the value in FieldE is not actually a simple concatenation
of values entered into Fields A, B, C and D. Field E should contain a
concatenation of values based on the values entered in the other fields, but
not the values themselves. To illustrate, say the value selected in Field A
is "ValueA" and the value selected in Field B is "Value B". The
corresponding value is Field E would be "ItemA" and "ItemB" rather than
"ValueA" and "ValueB".

So, assuming there is some expression I can use to substitute "ItemA" for
"ValueA" and "ItemB" for "ValueB" and then concatenate "ItemA" and "ItemB"
in FieldE, I guess it is no big deal to also include this expression in my
query which pulls all the data together to be displayed in a report.

Any suggestions for displaying the 'substituted' values in FieldE?

Thanks again
Anthony

PS: Just out of curiosity, how do I pass two variables to a function where
these variables are passed from two separate functions.
 
Anthony

I'm a little confused. You have controls on your form and you want to have
another control "say" which controls had values in them? Where did ItemA
and ItemB come from? How do you decide what shows when FieldA has a value?

Can you describe WHAT you are trying to do, rather than HOW? By this I
mean, why do you want to do this at all -- for what business purpose?
 
To simplify data entry, I have created a form in which values are entered
into text boxes (ie Fields A, B, C and D) by selecting options on menubars.
These menubars appear when toggle switches are pressed. I have used these
menubars where there are several options to choose from and each of these
options has two or more suboptions.

Each sub-option on the menubar has an underlying function which, when
selected, places a value in the associated Field and I have different
menubars for Fields A, B, C and D. Fields A, B, C and D are used to show
symptoms. Field E shows the general name of the procedure used to rectify
these symptoms.

So my initial idea was to use the existing functions for Fields A, B, C and
D to pass 'symptom' values onto a function which automatically enters these
(concatenated) values into FieldE.

I hope this helps put everything I've said so far into perspective and
thanks in advance for any suggestions.
Anthony
 
Hi Anthony,

I'm not sure I'm following what you're trying to do at all - it sounds
pretty complicated - but it sounds like what you're trying to do is
really more of a lookup than a function, per se. You might consider
creating a table to include both sets of information, and then do your
field fills from that table. For example:

tblValueItems
Field 1: Value
Field 2: Item

which would then be filled as follows:

Value Item
--------- ---------
ValueA ItemA
ValueB ItemB
ValueC ItemC

Then you could use a combination of lookups and Jeff's concatenation
suggestion to get the information you wanted.

Does that make sense? (Again, I'm not sure I understand what you're
trying to do, so it may not...)

grep
 
As a few others have mentioned, you should try and put the logic of this
into another table..and pluck out values from that table.

The end result will be MUCH less code..and often modifications, or adding to
the rules will requite no code changes (a good design will take this into
account. For example, as a developer I would consider it dishonest to code
an application where every time the options in a combo box needed to be
changed, or added to...you then had to bring in the developer). So, I would
not feel comfortable hard coding this stuff into a menu (and, to fair..you
might not be...but you example does seem to hint at this).

What I would do have the button code call ONE routine that figures this out.

However, you could also modify your code, and simply have it look at other
values on the form...:

(I am going to assume the code module is in the forms module...so I can use
the short form addressing).

Public Function FunctionName1()
Dim debstr As String
me!FieldA = "ContentsFieldA"

me!FieldE = me!FieldA & me!FieldB

End Function
**********************************
Click to expand...
Public Function FunctionName2()
partstr As String
me!FieldA = "ContentsFieldB"

me!FieldE = me!FieldA & me!FieldB

End Function
************************************
Click to expand...

So, really, you don't need the "combine" function. And, as per your other
question as to how to pass two values...the problem is WHEN do you pass two
values...since you are only calling one function at a time. However, as you
can see above...it is a simple matter to references the values CURRENTLY in
the form...and not worry about the variables....
 
Hi Guys

Thanks for your help. I think that I'm going to have to rethink the
structure of my data and break it down into smaller parts as one should
anyway. I will then be able to fill my FieldE as suggested by Jeff

Thanks again
Anthony
 
Hi Guys

Thanks for your help. I think that I'm going to have to rethink the
structure of my data and break it down into smaller parts as one should
anyway. I will then be able to fill my FieldE as suggested by Jeff

Thanks again
Anthony
Click to expand...

Note that you can also address a field contorl on a screen by a string


strField = "LastName"

msgbox "the last name is " & me(strField)

or, you can go:

me(strField) = "Smith"

I don't know if the above helps.

Also, don't feel too taken back by some of the people hinting at different
ideas here. Most of the time, we are all under the "gun", and have to get
things done. At the end of the day..whatever works for you is going to be
best for now......

Take every suggestion with a grain of salt. Often, the solution that YOU can
make is the best....

(this stuff does take time...and we generally don't have enough of it!!).
 
Hi Albert, Jeff and Grep

I have split the data in my fields A, B, C and D into smaller chunks and
concatenated the data entered into the new fields:

That is, instead of ProblemA: SolutionA being contained within one field. I
have created separate fields for both as shown below:

FieldA = "ProblemA"
ItemA = "SolutionA"

FieldB = "PromblemB"
ItemB = "SolutionB"

and used the expression suggested by Jeff as control source in FieldE to
concatenate the data in Fields A, B, C and D. But....how do I handle
situations where ItemA and ItemB contain the same value (eg ItemA =
SolutionA and ItemB = SolutionA. I don't want SolutionA to be repeated in
FieldE.

Any suggestions?
Thanks again
Anthony
 
Ok, first question:

When SoltuonA and SolutionB are the same....does this imply that ProblemA
and ProblemB are the same? (if yes, then, I would simply code the
ProblmeB to NOT allow duplicate problems in the fields.

However, it is likely that ProblmeA, and ProblmeB can be different...but
have
same solution?

Depending on the answer to the above will much dictate the approach here.

Further, are ProblemA and ProblemB independent of each other? (or, can a
user select/enter the same problem for "A", and also the same problem for
"B" (and, further...do you want to prevent this?).

Depending on how the above is answered will dictate the best approach
here...
 
Hi Albert

SolutionA and SolutionB can be the same if ProblemA and ProblemB are
different, in that there are different grades associated with the general
category called "Problem" (ie ProblemA is at the lower end of the scale
where ProblemD is at the higher end of the scale). Furthermore, the same
problem can occur in several different parts of the area being examined. So
I have several fields on my form (relating to different parts of the area
being examined) in which the general category of "Problem" is the same
(therefore, duplicate problems must be allowed) but the grade of problem may
be different.

The above may sound confusing, but the important thing is that in the fields
in which the general category of "Problem" is the same, the solution will be
the same no matter what the grade of Problem. I have no difficulty in
writing code to place values in the "Solution" fields. My problem is trying
to get the "Solution" fields to be displayed in another field (ie. FieldE)
without duplicates. FieldE should not look like this: "SolutionA SolutionA
SolutionA SolutionA". It should look like this: "SolutionA".

I hope all of that makes sense, Albert.
Anthony
 
Hi

Below is the code I was hoping to use to display only one value
(ie."SolutionA") where this value was contained in two or more fields (ie.,
Fields A, B, C and D.
*************************************
Private Sub FieldE_BeforeUpdate(Cancel As Integer)
Dim debstr As String
debstr = "SolutionA"

If (Me![FieldA] Or Me![FieldB] Or Me![FieldC] Or Me![FieldD]= debstr)
Then
Me![FieldE] = debstr
End If

End Sub
***********************************************
But I keep getting a type mismatch error with the If..Then line highlighted.
I don't understand why this error would be occurring when the correct values
are being passed to each of the fields in this expression (as indicated in
the messages which appear when I hold the mouse over each element of the
expression).

Any suggestions?
Thanks
Anthony
 
Ok, the problem is that the "before" update event of the FieldE does NOT get
updated when you use code to set the values.

Further, while the suggestion to use a "expression" for the control FieldE
means that the final resulting string will NOT be saved in the database.

Of course, I think you realize that a control on a form can be bound to the
underlying table/query, or the control can be bound to an expression. If as
have the control bound to a expression...then it is NOT saved when you close
the form.

(and, you wonder why we sometimes ask many questions....the reason is that
as more of the "problem" is reveled, then what is a reasonable solution also
begins to change!).

Given what we know now, and the fact that you need some Pre-processing on
the data that goes into the FieldE, I am now going to suggest that you built
a function that all of the fields call (a to d) in the after update event.
This code will thus run, and can setup FeildE correctly.

And, just a quick note:
(while the before update event does NOT run when you modify a field via
code, so your code would not get executed, *if* the code was to run, the
correct syntax would have to be

If ( Me![FieldA] = debstr) Or (Me![FieldB] = debstr) Or (Me![FieldC]
= debstr) Or (Me![FieldD]= debstr) then
Click to expand...

So, since we actually want the value(s) to be saved in FieldE (not just a
concat expression that does NOT get saved), *AND* we also need to
pre-process the data before hand also (we can't as we just learned simply
concat the values..since we need to check for duplicates etc).

So, we need a routine to concat the 4 values together...but also remove
duplicate values also. Hence, the solution is to call the following routine
in the after update event of our 4 fields (these are the ones that the user
changes). The code would look like:

Sub MyConcat()

Dim colResults As New Collection
Dim FieldList As Variant
Dim intInList As Integer
Dim i As Integer
Dim strTemp As String

FieldList = Array("FieldA", "FieldB", "FieldC", "FieldD")
intInList = UBound(FieldList, 1)

On Error Resume Next

' remove any duplicates...
For i = 0 To intInList
strTemp = Nz(Me(FieldList(i)), "")
If Len(strTemp) > 0 Then
colResults.Add strTemp, strTemp
End If
Next i
On Error GoTo 0
' now concat the results for FieldE

strTemp = ""
For i = 1 To colResults.Count
If strTemp <> "" Then
strTemp = strTemp + " "
End If
strTemp = strTemp & colResults(i)
Next i

Me!FieldE = strTemp

End Sub

So, just call the above code in the after update event of the 4 fields that
get modified....
 
Hi Albert

That function works great! Thanks heaps for all of your time and effort.

Anthony

Albert D. Kallal said:
Ok, the problem is that the "before" update event of the FieldE does NOT get
updated when you use code to set the values.

Further, while the suggestion to use a "expression" for the control FieldE
means that the final resulting string will NOT be saved in the database.

Of course, I think you realize that a control on a form can be bound to the
underlying table/query, or the control can be bound to an expression. If as
have the control bound to a expression...then it is NOT saved when you close
the form.

(and, you wonder why we sometimes ask many questions....the reason is that
as more of the "problem" is reveled, then what is a reasonable solution also
begins to change!).

Given what we know now, and the fact that you need some Pre-processing on
the data that goes into the FieldE, I am now going to suggest that you built
a function that all of the fields call (a to d) in the after update event.
This code will thus run, and can setup FeildE correctly.

And, just a quick note:
(while the before update event does NOT run when you modify a field via
code, so your code would not get executed, *if* the code was to run, the
correct syntax would have to be

If ( Me![FieldA] = debstr) Or (Me![FieldB] = debstr) Or (Me![FieldC]
= debstr) Or (Me![FieldD]= debstr) then
Click to expand...

So, since we actually want the value(s) to be saved in FieldE (not just a
concat expression that does NOT get saved), *AND* we also need to
pre-process the data before hand also (we can't as we just learned simply
concat the values..since we need to check for duplicates etc).

So, we need a routine to concat the 4 values together...but also remove
duplicate values also. Hence, the solution is to call the following routine
in the after update event of our 4 fields (these are the ones that the user
changes). The code would look like:

Sub MyConcat()

Dim colResults As New Collection
Dim FieldList As Variant
Dim intInList As Integer
Dim i As Integer
Dim strTemp As String

FieldList = Array("FieldA", "FieldB", "FieldC", "FieldD")
intInList = UBound(FieldList, 1)

On Error Resume Next

' remove any duplicates...
For i = 0 To intInList
strTemp = Nz(Me(FieldList(i)), "")
If Len(strTemp) > 0 Then
colResults.Add strTemp, strTemp
End If
Next i
On Error GoTo 0
' now concat the results for FieldE

strTemp = ""
For i = 1 To colResults.Count
If strTemp <> "" Then
strTemp = strTemp + " "
End If
strTemp = strTemp & colResults(i)
Next i

Me!FieldE = strTemp

End Sub

So, just call the above code in the after update event of the 4 fields that
get modified....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn
Click to expand...
 
Back
Top