Join multiple fields in a form 6

[withoutwax]

Does anybody know how to join multiple fields in a form? Basically I want to add a field on my form which will contain the data from several other fields on the form

 

[Remou]

You can use & to set the control source of a control to include several fields from your table (forms have controls, tables have fields):

=[Surname] & " " & [Forename]

 

[withoutwax]

Thank you Remou. I've done it yey!

 

[dhookom]

One tip is to make sure the name of the text box is not also the name of a field from your record source. For instance the following will produce an error:
[blue][tt]
Text box properties
Name: Surname
Control Source: =[Surname] & " " & [Forename]
[/tt][/blue]

Duane
Hook'D on Access
MS Access MVP

 

[withoutwax]

Thank you Duane. Useful tip.

 

[iamerm]

I'm new to the forums, so I'd like to thank everyone in advance for your useful tips. I have a question regarding the method you just described:

I'm trying to create a form that will generate part numbers based on certain criteria. For example, I'd like a part number comprised of 3 segements in this format: X-Y-Z, whereas X equals a selection from a list of possible options, Y equals a selection from another list of possible options, & Z equals a selection of from a third list of possible options.

So far, I've created three tables: the "X" table, the "Y" table, & the "Z" table. Each table has three fields: "ID" (which is an AutoNumber, primary key field that I don't intend to use), "OptionX" (which is the actual data I want in the part number), & "OptionX Desctiption" (which is just a text description of the listed option).

I then created a fourth "Part Number" table that has four fields: "ID" (an AutoNumber, primary key field), "OptionX", "OptionY", & "OptionZ". The last 3 fields are all Lookup ComboBoxes that allow only selection from a dropdown list - with the data coming from the "OptionX", "OptionY", & "OptionZ" fields of their respective tables.

I then created a form based on the "Part Number" table. On that form the user can select the options as described above.

The problem I run into, though, is this: when I try to create the control that combines all of the options into one part number (using the method posted above by Remou), the results are based on the "ID" field of each table, rather than the "Option" field of each table. I have no idea why that happens, though.

Pardon the ridiculously long post, but can you give any suggestions as to whether or not I'm even on the right track with all of this? Thanks again.

 

[SeeThru]

From the sounds of it you need to think it through...

If Im understanding you right, you only really need a coplue of tables:

1 - Parts table
ref (autonumber)
PartCode
PartType (x,y or z)
PartDescription

Then you have an 'assemblies' table with
ref (autonumber)
Part1
Part2
Part3

You build aform based on assembines. Each on the parts are a dropdown based on the Parts Table, but with different source criteria

Part1: select PartCode, PartDescription from PartType where PartType='x'
Part2: select PartCode, PartDescription from PartType where PartType='y'
Part3: select Partcode, PartDescription from PartType where PartType='z'

Now, when you create a new record, you select the three part types, and that's it. Whenver you need to know the reference for the assembly, you can generate it as you go:

=[Part1] & "-" & [Part2] & "-" & [Part3]

You can use this on forms, reports, queries, etc.


Note: if you think you may have more (or less) than three parts things can get more complex. You are looking at a Many-to-Many relationship, which is perfectly possible, but a bit more complex.



SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[iamerm]

Thanks for your reply, SeeThru.

Is there a way to populate a field in a table in the same manner that you just described in reference to a form? (Using the

=[Part1] & "-" & [Part2] & "-" & [Part3]

method)

For example, if I have a table with the following fields:

ref (autonumber)
PartX
PartY
PartZ
AssemblyXYZ

Could the "AssemblyXYZ" field somehow be populated by a combination of the "PartX", "PartY", & "PartZ" fields (at the table level, rather than on a form)?

 

[PHV]

It's usually a bad idea to store derived/calculated values in the table.
Do the combination in a query instead.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SeeThru]

As PHV says, it is not normally neccesary, as you can calculate it wheever you need it....

However, if I wanted to link on this field, then I might do it.

In the afterupdate of each of the three parts dropdowns add:

me!assemblyxyz=[Part1] & "-" & [Part2] & "-" & [Part3]

then, whever one of these is changed, the assembly number is changed.

However, you MUST make all changes only in this form, or add similar code to any form where changes can be made.



SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[iamerm]

Thank you, SeeThru & PHV. I was trying to go straight from my table to a form, & that was giving me some problems. However, now I'm making the combination in a query (as you suggested), & then I've based my form on that query (instead of the initial table). This works very well for what I need it to do. Thanks again.