Line breaks in concatenated fields & auto-entries from related tab

[reluctantAccessor]

I’m a FileMaker Pro fanatic who’s now pretty much compelled, against his will
and all reason, to try to make his databases in Access, and I can’t recreate
several elements of functionality I want in Access. I’ve searched this
user’s group, but can’t find the magic words to describe what I’m trying to
accomplish.

First, I want to copy data from a group of selected fields in a given record
onto the clipboard, complete with line breaks that “format†the plain text in
a primitive way.

This is so simple in FM Pro you can practically sneeze and make it happen.
You merely create a calculated field, concatenate the desired data/text
fields, and insert paragraph marks into the calculation where you want line
breaks. Then all you do is select the contents of the calculation field,
CTRL C, and presto!

So how does one accomplish this seemingly straightforward task in Access?

Second, I have a basic contacts database of names and addresses. When
filling in the state for a record, I have a list box (probably more accurate
to call it a lookup – the jargon in Access is completely different from
FileMaker) that references a related two-column table. One column has the
two-letter postal abbreviations, the other has the state names spelled out.

Is there a means to have the state name automatically entered into a
separate field once one has selected the postal abbreviation for that record
in another field?

I’ve been chasing my tail in circles on these two basic tasks using Access
helps and can’t get anywhere. And I dread the thought of tackling the more
complex functions I’ve programmed into my FM Pro databases when I see how
cumbersome these things are in Access.

Thanks in advance for answers and advice.

 

[John Spencer]

You can create a calculated item in Access. You don't do that at the table
level, you do it in a form or in a query that is bound to the form

Short paradigm to help you in using Access.
--Tables are places to store data and you should not store two copies of the
same data in a record.
--Queries are used to select and manipulate data (sort it, do calculations,
join data from various tables, etc.)
--Forms are places to display and enter (modify) data and create user
interfaces
--Reports are places to display/print data


FIRST:
In a query:
Field: Field1 & Chr(13) & Chr(10) & Field2 & " - " & Field3

That combines field 1, inserts a new line, puts Field2 and Field3 after the
new line with a dash between the two. On a form you can enter the
expression as the source for a control.


SECOND:
Since you are storing the data already in a table, don't store it a second
time. When you need it, you just add the table of state codes and names
into your query and display the name from the related table.

On a form, there are several ways to display the related state name. If you
have the abbreviaion and the name in the listbox or combobox, you can use a
formula in a control to display the full state name. That could look
something like the following for a control's source:
= lstBoxName.Column(1)
Column number is the column that displays the data in the list box or combo
box. Column is zero-based, so this would display the data in the second
column


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[reluctantAccessor]

John,

I'm just now getting around to trying out your recommendations. I've been
successful so far with the first one. (I'd love to know how you knew the
necessary character codes. I searched for some clue before I posted my
original question, to no avail.)

The second item will require more effort to comprehend and implement. But
in anticipation of a reprise of the first "fix," I'll extend my heartfelt
thanks for your time and effort assisting me.

Regards,

Andrew
ps--I see you're an Access expert of sorts. I envy you. The forum censors
will no doubt slam me for saying it, but I think Access is part of a
conspiracy by Microsoft to clinch job security for programmers
worldwide---hence, why 49 of every 50 office workers limp along through life
using Excel thinking it's a database application.