combing several fields into one

[Meg Brady]

I've been looking for an answer to this for a while now, and cannot find
anything. Was wondering if someone here had any ideas?

I would like VBA code for generating a text string made of multiple combo
box selections. This would probably have to happen on FieldX GotFocus

If FieldA=ceramic, then FieldX=FieldB FieldD FieldE
If FieldA=glass, then FieldX=FieldC FieldE FieldF

I have this as multiple queries in a macro, and it works but it's very slow.
I've seen it done in VBA and it is much much faster. Any ideas?

 

[Douglas J. Steele]

Private Sub FieldX_GotFocus()

Select Case Me.FieldA
Case "Ceramic"
Me.FieldX=Me.FieldB & ", " & Me.FieldD & ", " & Me.FieldE
Case "Glass"
Me.FieldX=Me.FieldC & ", " & Me.FieldE & ", " & Me.FieldF
End Select

End Sub

 

[Ken Sheridan]

It depends on whether FieldX is a column in the table to which you want to
assign the concatenated string, or a computed control on the form. If Fields
A to F are all columns in the table then there is no need to have FieldX in
the table as it can be computed on the fly; if any of them are unbound
controls on the form, i.e. FieldX's value can't be computed from the other
columns in the table, then you would need to assign the value. In either
case the simplest thing to do is to add a little function to the form's
module.

For the first scenario, where FieldX is an unbound computed control the
function would be like this:

Private Function GetX() As String

Select Case FieldA
Case "ceramic"
GetX = FieldB & " " & FieldD & " " & FieldE
Case "glass"
GetX = FieldC & " " & FieldE & " " & FieldF
End Select

End Function

You'd then call the function as the ControlSource property of the FieldX
control:

=GetX()

For the second scenario the function would be:

Private Function AssignX()

Select Case FieldA
Case "ceramic"
FieldX = FieldB & " " & FieldD & " " & FieldE
Case "glass"
FieldX = FieldC & " " & FieldE & " " & FieldF
End Select

End Function

This function assigns the value to FieldX, for which you'd have a bound
control on the form this time. As it depends on all of Fields A to F, so a
change to any of them would affect the value assigned, you'd call it as the
AfterUpdate event property of each of these controls with:

=AssignX()

Its not possible to say which of these is the appropriate solution on the
basis of the information you've given, but if you are unsure which way to go
post back with more details of what you are trying to do in terms of the real
world attributes which these fields represent.

Ken Sheridan
Stafford, England

 

[Meg Brady]

Basically, I'm trying to generate an artifact description based on different
attributes and different materials the user can chose from combo boxes. For
instance, if an artifact is made of glass (material), the description needs
to have the color, manufacture method, portion, and object form. If ceramic,
then it should be ware type, decoration, portion, and object form. I also
have things based on object form, so if material=metal and object form=nail,
then the description should be manufacture method, object form, object form
subtype.

Right now, all these fields (including the field my description goes in) are
in the same table, and need to be able to go in the same report.

I can type this out in SQL all day long, but I think part of my problem is
my field names - some of them have spaces (like the field "Manufacture
Method"), and I'm unsure of how VBA and spaces get along. I've tried the
code you posted, and the one Doug posted, but I still can't make this work.