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
CONCAT (LastName, ' ',FirstName) AS FullName
to check if this works execute this select statement:
Your list box should work from this view if properly set up.
On Tue, Jan 1, 2013 at 9:53 AM, Ian Whitfield <email@example.com>wrote:
Thanks for your reply to me - appreciated!!
*On 01/01/2013 10:21 AM, Hank Alper wrote:
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.
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
Something like - MembersID = DatabaseName.TableName.FieldName
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: firstname.lastname@example.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Impressum (Legal Info)
: 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