"Stuart McCall" <sm*****@myunrealbox.comwrote in message
news:fi*******************@news.demon.co.uk...
>Thanks very much for your help. Being a total beginner I don't clearly
understand how I should create a module or how to write the SELECT
query. I have copied and pasted your code into a module page and saved it
named as "AddrLines".
I'd be most obliged if you could give a worked example using the field
names below:
Address1
Address2
Village
Town
County
Country
Postcode
from a table named: Sheet1
Ok, first of all you should rename that module, or sooner or later Access
will give you an 'ambiguous name detected' error. Call it say
modAddrLines. Anything so long as it differs from the name of the
function.
I'll assume you want this combined address to go into a Form's control
(let's call it txtAddrLines). The form needs to be bound to table Sheet1
(it should have Sheet1 in it's RecordSource property).
Here's how it's done:
Me.txtAddrLines = AddrLines(Address1, Address2, Village)
You fill in the rest of the fields the same way I've just shown. Open the
form and check it out.
I hope that helps.
Well, to be honest - mostly no.
It might help if I explain that this DB to hold membership information of a
charitable small integrated abilities sailing club that wishes to expand.
The information will be used, amongst many other things, to create:-
- printed directories containing all contact information in an easily
readable format
- mailings, directed at specific geographic areas, vis: county, country
I previously created a form called: frm_members, which uses the table:
Sheet1, which I assume is what you mean by "bound to". The form is used to
enter data.
I don't understand what the Me. stands for in the code - nor was I sure
where to paste the code example. So, I opened the properties for the form:
frm_members but I can't see where I should paste this code. But then again
I'm not sure it would do what I intended.
My original idea was to create an update query contain a code such as you
wrote that would combine the address data for each record and update that
into a single field in an update table called: mktbl-table.
The mktbl-table table would be the source table for forms and reports, in
fact everything.
However, having written this, it seems I would be doubling (or more) the
information storage - by having 2 tables, the original and the updated
table.
So I guess I probably need a table into which 'new' or 'edited' information
is entered (called say, tbl-newinfo), upon which an update query uses that
to update the table called: mktbl-table, after which a routine runs to
delete the information in tbl-newinfo.
Does this make any more sense to you?
Regards,
Hal.