Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index

Hi Ian,
First things first. I'm assuming that if you were to manually insert the
"Member ID" in the appropriate text box on the subform, then the emails
you're searching for will appear. That is the impression I received from
reading your last post on this topic. Is this the case? If so, the list box
should be able to solve the problem. If your form doesn't behave that way,
I don't understand your problem.

As for concatenating First and Last Names, I have been working with the
embedded HSQL in LO and my MYSQL is a bit rusty. However I think using the
UPDATE key word is incorrect as you've applied it. You are , I think,
trying to add a new Column, ie. "FullName" to your member table, and
populate it with the full names of your members. . UPDATE  is used to
change  values. The keywords to add columns to a table is ALTER TABLE
tablename ADD [COLUMN] columname datatype. Check my syntax it's rusty! Then
you'll have to insert the full names into the new column in the altered
table. Why not create a view instead, using the CONCAT function.

Concatenating last name and first name in MYSQL and creating a view with
the concat function:
CREATE VIEW viewname
Member ID,
CONCAT (LastName, '  ',FirstName) AS FullName
FROM TableName;

to check if this works execute this select statement:
FROM viewname

Your list box should work from this view if properly set up.

On Tue, Jan 1, 2013 at 9:53 AM, Ian Whitfield <>wrote:

 *Hi Hank*

Thanks for your reply to me - appreciated!!

*On 01/01/2013 10:21 AM, Hank Alper wrote:

*Hi Ian,
I'm not sure what you mean by automatic.Would you consider a list box with
a drop down list of all the member names with the equivalent member ID
being the input value  to the subform automatic enough? I've used this
approach many times on the project I'm working on.
In your form edit mode in the design view, click on the list box icon and
place a list box on your subform and follow the directions. Your source
table for the list box display will be your Member table and
the data to appear in the list box will be your "member name"  attribute
of that table. Finally, you will select the matching attributes from the
two tables which links them. The Member ID number In the e-mail table and
the Member ID from your member table (I'm guessing) will be the attributes
you'll link. In this way, when you click on a name in the list box, you'll
be inserting " member ID" into that form.
Hank  *

This sounds interesting and I can live with this answer although I must
say I find it strange that My SQL can not automatically pick up a value
like this!!

Something like - MembersID = DatabaseName.TableName.FieldName

or maybe

MembersID = FieldName IN Parent Form

Anyway - as I said I can use your idea. SO - I have set-up the List Box as
you suggested and it brings-up all the names but does NOT put in the
MembersID but rather just the Name??

Secondly - at the moment I have First Names and Surnames in separate
fields so I decided to create a new field called "FullName" and now I'm
trying to automate this. Googling for info on this I found the perfect
answer to what I'm doing here .....

But all it gives me is a Syntax error. I have looked and looked and found
lots of other answers but they ALL give me syntax errors.

I'm using MySQL 5.1.55 with Base as the front-end and putting in

update table set FullName = concat(PrefFirstName, ' ', LastName);

If tried everything I can think of, CONCAT_WS and looked at GROUP_CONCAT
but no luck!!

Can you see my error??

Last question - Will I have to add a "Find" button to my subForm to go and
get all the respective eMails for each member or will a filter do the trick?

Thanks for your help - appreciated.


For unsubscribe instructions e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.