How do I make a Lookup table containing paragraphs

[Guest]

I'm new to Access so thanks in advance.
I'm trying to create a text field in a form, where the user can select the
text they want to insert from a dropdown box. I've worked out that I can
create a "look up" table to store the options that will be displayed and then
in the main table and associated form, create a text field and a combo box
that will refer to this lookup table for the various options. So far so good.
But the length of the text that can be selected like this is limited to 255
characters, the max size of a text field. I want the user to be able to
select from several longer paragraphs. How can I do this?

 

[Douglas J. Steele]

Use the Memo data type rather than Text.

 

[Larry Daugherty]

I don't know all that you're doing but you might consider something
like the following:

re-work your lookup table to have a primary key, a description field
and a memo field. Let the result of the selection by the description
be that the contents of the memo field are what's displayed in the
textbox control on your form. Make sure that the textbox itself is
not limited in the number of characters it can display.

HTH

 

[Guest]

Douglas,

Thanks for the reply. If I try to use a "memo" as the data type, then when
when I go to the properties for that field, it doesn't give me the option to
use a "combo box" (which it does for a text field). Could you describe in
more detail how your suggestion would work?

 

[Guest]

Larry,

Thanks for taking the time to reply. Your suggestion sounds like it should
do what I need. I know how to set up the lookup table as you describe but I
don't know Access well enough to make the displayed memo be dependant on what
is chosen in the description field. Presumably I need to configure the data
properties of the memo field appropriately. Could you describe the detail of
what I need to do?

 

[Larry Daugherty]

I can't see what you have before you so, "No, I can't give you a
detailed solution".

I can sketch one in general and then you'll have to dig out the
details from your resources to get the job done.

In the query for your text selecting combobox be sure that all fields
or the selectable text chunks are included - but, in the display,
enter a value of "0" for the width of the memo field. In the
AfterUpdate event of the combobox put in a line something like:
Me!MyMemoText=Me!myCombobox.column(2) assuming that your query returns
3 fields then the 3rd field is column 2.

In a quick sketch, that will get the job done.

HTH

 

[Douglas J. Steele]

Aah. Sounds as though you're trying to use the misguided "Lookup Field"
feature. (see http://www.mvps.org/access/lookupfields.htm at "The Access
Web" for some of the reasons why you shouldn't)

Simply use a combo box on your form, with the paragraph table as its
RowSource.

 

[Guest]

I should explain more clearly what I'm trying to do. I'm creating a database
in my hospital to record procedures that have been performed on patients. I'm
trying to keep the design as simple as possible. The idea is that each
procedure will be represented by one record. So, starting with a blank Access
file I've created a MAIN TABLE with the fields "Patient name" and "Procedure
performed". For the "Procedure performed" field I want the database secretary
to be able to select the procedure that the patient has had done from a drop
down list. So, still in the MAIN TABLE I've selected the "Procedure
performed" field and for its field properties I have selected the following
DISPLAY CONTROL = combo box, ROW SOURCE TYPE = table/query and ROW SOURCE =
the name of a second table I have created with one field listing all the
different types of procedures performed. I've then created a form and
inserted the two fields ("Patient Name" and "Procedure performed") and this
seems to work well.

Depending on the type of procedure performed, patients will need a different
set of instructions to help them recover. So, if patient A has had an
angioplasty, they will need the post angioplasty instructions and if patient
B has had a liver biopsy, they will need the post liver biopsy instructions.
I would like to add these instructions onto the simple form, so far only
containing the two fields, so that they can be printed out. So I tried to
simply use another combobox and lookup table which works fine except for the
fact that it is limited to 255 characters. I don't necessarily need the data
entry into the "instructions" field" to be automatically linked to what has
been entered in the "procedure performed" field, I just need more space. If I
create the "instructions field" as a memo, that gives me more characters but
I'm not then given the option of using a combo box (the lookup tab on the
field properties is blank)

If you are able to help, I would appreciate it but understand if you haven't
the time or aren't able to help for other reasons.

Cheers