Parsing words in a field to use in subsequent forms

[blwilsey]

A multiselect list box is used to populate a text box with items
separated by one space (eg., oxycontin methadone oxycodone
hydrocodone)

The control source for the text box is a field,
[tblEncounters]![MedsUsed], that stores these items for later use

I would like to take each of the items in turn from the field and have
them appear in a text box in several forms. The number of forms would
be equal to the number of words stored in the field. For the example
given above, there are four words so the user would be presented with
four forms to answer the same information about each of the items

thanks in advance, Barth

 

[Mike Painter]

A multiselect list box is used to populate a text box with items
separated by one space (eg., oxycontin methadone oxycodone
hydrocodone)

The control source for the text box is a field,
[tblEncounters]![MedsUsed], that stores these items for later use

I would like to take each of the items in turn from the field and have
them appear in a text box in several forms. The number of forms would
be equal to the number of words stored in the field. For the example
given above, there are four words so the user would be presented with
four forms to answer the same information about each of the items

Bad design and you will have problems with it, even if everybody always
spell oxycotton (sic) the same way.
Each item should be a separate record in MedsUsed table related to Your
encounters table.
There is no need to open four forms as this could be done in a continuous
form if need be or just a form with a record for each name.

No code would be needed. For your way the Split function is a place to
start.

 

[Barth Wilsey]

thanks Mike

I followed your instructions and am using a continuous form that uses a
query to list the medications
There is one problem. The continuous form has one blank record at the bottom
I assume that the one extra space is because the table has a autonumber key
field as the extra record has a * notation in the record number. Is there
anyway to delete this extra record?

thanks in advance, Barth

A multiselect list box is used to populate a text box with items
separated by one space (eg., oxycontin methadone oxycodone
hydrocodone)

The control source for the text box is a field,
[tblEncounters]![MedsUsed], that stores these items for later use

I would like to take each of the items in turn from the field and have
them appear in a text box in several forms. The number of forms would
be equal to the number of words stored in the field. For the example
given above, there are four words so the user would be presented with
four forms to answer the same information about each of the items

Bad design and you will have problems with it, even if everybody always
spell oxycotton (sic) the same way.
Each item should be a separate record in MedsUsed table related to Your
encounters table.
There is no need to open four forms as this could be done in a continuous
form if need be or just a form with a record for each name.

No code would be needed. For your way the Split function is a place to
start.

 

[Mike Painter]

thanks Mike

I followed your instructions and am using a continuous form that uses a
query to list the medications
There is one problem. The continuous form has one blank record at the bottom
I assume that the one extra space is because the table has a autonumber key
field as the extra record has a * notation in the record number. Is there
anyway to delete this extra record?

thanks in advance, Barth

That's the space for the next record, which you would not use. I'm not
aware of a way to hide it completely. I suppose code could be used to shrink
form if the number of records was to few but I'd leave it.

 

[Guest]

Hi Barth,
You can get rid of the empty row at the end of your subform by setting the subform's AddNew property to No. The empty row will disappear. It also means that as it is you will not be allowed to add a new record to this subform.

HTH,
Anand