Concatening fields 3

[SidLancing]

I have a combobox which list contact names (ie. Title,First,Middle,Last). How can I get the row selected to be saved as a concatenated version of Title & First & Midldle & last. I'm not sure where to put the code... should this go to ON Click and the correct syntax here would it be possibly something like...

CName (i.e the combobox field name) = Title & " " & First & " " & Middle & " " & Last


The Rowsource for the combobox is:

SELECT [locationID], [Suffix],[FirstName],[Mddle],[LastName] FROM Contact WHERE LocationID=[FORMS]![Order]![ShipingLocationID] ORDER BY [LastName];

Suggestions anyone?

 

[scriverb]

If you want the cancatenation to take place in the query so that the user sees the final product in the combobox use the following updated SQL:

SELECT [locationID], [Suffix] & " " & Trim([FirstName]) & " " & Trim([Mddle]) & " " & Trim([LastName]) as Name FROM Contact WHERE LocationID=[FORMS]![Order]![ShipingLocationID] ORDER BY [LastName];

Let me know if this is what you were looking for.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SidLancing]

Is Name in your select a variable that will actually be the Rowsource value or the combobox name or just a variable to hold the value of the concatenation?... because it still shows the LastName which is what I had before...I want the result to be the value saved in the combobox field ... right now it's just a LastName.

 

[scriverb]

What I provided was a SQL string to be used in your RowSource property of the combobox. Name is an Alias that will be the column name of the cancatenation of the fields.

Now in your combobox you can change the name of the combobox itself to whatever you want it to be. The value to be stored in the combobox is determined by the Bound Column property. In this instance if you want the column Name to be the value of the combobox after the pick by the user, then set the Bound Column property to 2. The columns to be displayed in the dropdown of the combobox is determined in the ColumnWidths property. Put a 0 for those columns that should not show and an appropriate width in inches of the columns to show. (i.e 0;3")

Now the Order of the rows in the combobox is set right now to just the LastName field. If you want something different than that just change that.

If you have more questions please post back.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[smokyjoker]

I have a slightly more complex concatenated query that I need to put together. The above example has put me on the right track, but I have several fields that I need to specify criteria for. Here's my code based on your example:

=(SELECT [LitGroup], [LitGroupType], [LitGroupSubType], [LitGroupStyle], [StartYear] & "-" & Trim([EndYear]) & "-" & Trim([VehicleType]) & "-" & Trim([Model]) & "-" & Trim([Edition]) & "-" & Trim([Cond_Vend]) & "-" & Trim([Binding]) & "-" & Trim([Country]) & "-" & as PartNumber FROM PartNumberLookup WHERE LitGroupID=[FORMS]!PartNumberLookup!LitGroup AND LitGroupTypeID=[FORMS]!PartNumberLookup!LitGroupType AND LitGroupSubTypeID=[FORMS]!PartNumberLookup!LitGroupSubType AND LitGroupStyleID=[FORMS]!PartNumberLookup!LitGroupStyle)

What I thought I might have to do was nest the subqueries like this:

"...[Edition] & "" & (SELECT LitGroup.Value From LitGroup WHERE (((LitGroup.LitGroupID)=forms!PartNumberLookup!LitGroup))) & "" & (SELECT LitGroupType.Value From LitGroupType WHERE (((LitGroupType.LitGroupTypeID)=forms!PartNumberLookup!LitGroupType)))..."

but that doesn't work either. I am putting this in a combo box rowsource.

 

[scriverb]

smokeyjoker: This is a little confusing so why don't you describe the tables and their fields and the relationships and I will see what can be done to help you with your query. Describe what it is that you want the rows to look like also.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[PHV]

Why not JOINing the tables ?
Create a similar query in the Query Design Window and grab the generated SQL code as a starting point.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[smokyjoker]

Sorry scriverb--I'll start from the beginning. I have thirteen tables that are lookup tables for a master table. Each table represents options for a position in a part number. Each table has three columns: tablenameID, Value, and Description. The 'tablenameID' is an autonumber field and the primary key. The 'value' field is an arbitrary 1 or 2 digit code that we devised to represent an attribute of an item. The description is a descriptive phrase that will explain what the code means (i.e. "A" is a shop manual and "B" is a Parts Book).

Four of the tables are related in one-to-many connections with referential integrity enforced in this order: LitGroup (top level) 1---->many LitGroupType (2nd level) 1---->many LitGroupSubType (3rd level) 1---->many LitGroupStyle (bottom Level). Each of these fields has an additional field bound to the primary key of its parent table. That way, I can restrict the values that appear in the child tables based on the option chosen in the parent table.

I have created a form based on a query of the master table. I want to concatenate the thirteen data entry options into one string. Each of the options is represented by a combo box bound to the 'value' field in the underlying query. The exception to this is that the four connected tables are bound parent to child by the primary key colum. The problem is that if I simply reference one of the four connected tables' combo box by name, I get passed the autonumber ID of that field instead of the 'value' field which is what I really need. But I cannot simply bind to the value field, because it contains duplicate values. So when I am concatenating, I think I need to include subqueries that take the ID number of a row and return the 'value' field of that row.

 

[PHV]

Have you tried to play with the Column property of the combo ?
Something like:
Forms!PartNumberLookup!LitGroupType.Column(1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[scriverb]

As PHV suggested, if all you want to do is create an unbound calculated control that represents a cancatenation of all of these combobox values then use something like this:

=Me.[StartYearCombo].column(1) & "-" & Trim(Me.[EndYearCombo].column(1)) & "-" & Trim(Me.[VehicleTypeCombo].column(1)) & "-" & Trim(Me.[ModelCombo].column(1)) & "-" & Trim(Me.[EditionCombo].column(1)) & "-" & Trim(Me.[Cond_VendCombo].column(1)) & "-" & Trim([BindingCombo]) & "-" & Trim(Me.[CountryCombo].column(1))

Now keep in mind that this is an example. The combobox names and the correct column needs to be referenced. The columns are numbered starting at 0 rather than 1. Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[smokyjoker]

Thanks very much to both of you. I tried putting it on the row source of a combo box and it tells me that it doesn't exist. Also, why does the "Trim" not go on the first field also?

 

[SidLancing]

Scriverb,
I've had the following code in the Rowsource ...

SELECT [locationID], [Suffix] & " " & Trim([FirstName]) & " " & Trim([MiddleName]) & " " & Trim([LastName]) AS Name FROM Contact WHERE LocationID=[FORMS]![Order]![ShipingLocationID] ORDER BY [LastName];

Column count=2
Column width=0";3"
Bound column=2
List width=3"

but it still shows just the LastName field which is what I previosusly had ... I realized that this is due to the After_update event procedure of another combobox that is updating this one ...

How do I specify this in the After_Update. I get a compile error.. I have a feeling that the quotes are the problem.
Here is the code

Recipient.RowSource = "SELECT locID,[Suffix] & " " & Trim([FirstName]) & " " & Trim([MiddleName]) & " " & Trim([LastName]) AS Name FROM Contact WHERE LocID=" & ShipingLocationID & " ORDER BY 1"

thanks

 

[PHV]

HAve you tried this ?
Recipient.RowSource = "SELECT locID,[Suffix] & "" "" & Trim([FirstName]) & "" "" & Trim([MiddleName]) & "" "" & Trim([LastName]) AS Name FROM Contact WHERE LocID=" & ShipingLocationID & " ORDER BY 1"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SidLancing]

Ooops I meant to put LocID instead of LocationID in the code for the rowsource...

The compile error still shows up highlighting the entire select statement.

P.S>...As if things weren't strange enough...now the concatenated value show up in the combo box but I get thrown into the VB editor with the compile error

Help!

 

[SidLancing]

NEVER MIND MY LAST POST!...

Thanks PHV... I just tried the "" "" and it worked...

 

[PHV]

And this ?
Recipient.RowSource = "SELECT locID,Suffix & ' ' & Trim(FirstName) & ' ' & Trim(MiddleName) & ' ' & Trim(LastName) AS Name FROM Contact WHERE LocID=" & ShipingLocationID & " ORDER BY 1"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SidLancing]

I just tried that ... and it also worked...so you know I have to ask Is one method better than the other?

Thanks

 

[SidLancing]

I have another question concerning combo boxes and forms.
In my form I have 6 combo boxes. (2groups of 3)
Group 1
Shipping Company
Ship Co location
Ship CO Contact

Group2
Billing Company
Bill Co Location
Bill CO Contact.

Those combo boxes work as I'd like them ... (ie. Ship Co location displays locations only for the Shipping Co and Ship CO Contact shows contacts only for those locations.)
I say that because the Billing group works in the same maner...(hence my comment above of 2 groups of 3)

The problem is that when I make a selection for all of them and close the form then when I reopen the form the location field comes out blank. sometimes... I haven't yet found a pattern.
Any idea what might be causing this?... Is it possibly because I don't have yet a button or any code to save the record . I usually just CLick it close(as with any window screen)