Sub Form with Multiple Memo Fields

[Campbell C via AccessMonster.com]

Hi and TIA,

I am hoping someone can help me out by at least pointing me in the right
direction.

I have a work order table and form for entering data into it. I now need to
have a popup form that will allow the user to enter a checklist for the work
order. This will contain some header information and rows of details for the
checklist.

So in the db I have set up 2 tables, one for the header record which contains
the FK to the work order. The other table has a FK back to the header record
and a number of fields.

So I created a checklist form and added a sub-form which displays in
datagrid form each of the rows of detail.

I added code into a button on the work order form that opens the checklist
form as a popup and filters the header record to show only the detail items
that have a matching foreign key (1: 0 or 1). The popup window has a record
in the OnCurrent event set to filter the subreport of items using the
header_id.

The issue that I am struggling with is that each detail item record needs 3
large text fields. Since Access only allows one memo field per record I have
added a long_text table. Each detail item record now contains 3 id's
pointing to the long_text fields. This is because I need to have each record
appear in a specific place in the forms and some reports which are going to
be made later. (In other words, I need an Action_text field, an
Item_description field, and a Comment field for each detail item record).

I also need to be able to allow the user to enter more detail records in the
same form.


I apologize for any rambling or incoherence. I'm running on little sleep and
much stress. Any help would be much appreciated.

 

[Campbell C via AccessMonster.com]

I feel like Homer Simpson... I finally figured out that a table can have
multiple memo fields. and it can be used that way in the datasheet view.
which solves the problems.

Sleep would be good...

 

[Marshall Barton]

I have a work order table and form for entering data into it. I now need to
have a popup form that will allow the user to enter a checklist for the work
order. This will contain some header information and rows of details for the
checklist.

So in the db I have set up 2 tables, one for the header record which contains
the FK to the work order. The other table has a FK back to the header record
and a number of fields.

So I created a checklist form and added a sub-form which displays in
datagrid form each of the rows of detail.

I added code into a button on the work order form that opens the checklist
form as a popup and filters the header record to show only the detail items
that have a matching foreign key (1: 0 or 1). The popup window has a record
in the OnCurrent event set to filter the subreport of items using the
header_id.

The issue that I am struggling with is that each detail item record needs 3
large text fields. Since Access only allows one memo field per record I have
added a long_text table. Each detail item record now contains 3 id's
pointing to the long_text fields. This is because I need to have each record
appear in a specific place in the forms and some reports which are going to
be made later. (In other words, I need an Action_text field, an
Item_description field, and a Comment field for each detail item record).

I also need to be able to allow the user to enter more detail records in the
same form.

Access has no restriction on the number of memo fields per
record, so maybe you can just do what you want without
getting any more complicated than it already is.

On the other hand, having a separate table for memo fields
is not a bad idea. What I do in this kind of situation is
display the memo fields in a separate subform. Whit proper
Link Master/Child setting, you will only be able to see the
memos for the current record, but usually, that's all
that's needed.