How to

[mdnuts]

In my Access 2013 Database I have a form for user information "frmUserInformation" (stored in tblusers) normal details are in there, last name, first name, etc. I have a related table for user submissions and other submissions both tied back to the user id in tblUsers.

All normal everyday stuff. However one field on my User information form can contain one string or multiple strings based on however I decide it to be (comma separated, semi-colon, etc). Usually you'd think normalize it and create a new table - however probably 95% of the time it would just contain one string. I'd rather not create a new table and deal with multiple table entries/deletions/joins, etc to accommodate that 5%.

End of the day I need to be able to click on a string and have it launch a form using that string's value. I know of no way to click on individual words in a text box to do this which leaves me to thinking about having VBA create a text box for each string in an array plus one blank one (for new entries).

or maybe a combobox, one line per item.

Then if/when the form is saved have them join back together and stored in the one field.

Can anyone think of any other way?

 

[plog]

day I need to be able to click on a string and have it launch a form using that string's value.

What does that mean exactly? What does this second form do? What table is it based on?

 

[mdnuts]

The textbox on the User Information form that I'm referring to is txtSystems - in it would display the systems the user frequently uses. For example.

Mary, Jane, Jean - use sysInfant
Grace, Chris, Pat use sysToddler
....
Charles being a floating employee could use sysInfant, sysToddler or sysYouth.

What I'm trying to accomplish is me being able to click any system listed and have it bring up the system information form. (frmSystemInformation) (based on tblSystems and tblSystemSubmissions).

 

[gemma-the-husky]

a combo box


have a single column combo box/list box, with source set to value list

try
mycombobox.rowsource =useinformationstring

this should automatically split based on commas or semi colons, I think. Don't use other separators.

see if that works.

 

[mdnuts]

That worked out really well.

In the event someone else stumbles here - i used the following on my onLoad event for the form.

Dim strInformationString As String
If Not IsNull([fldSystems]) Then
   strInformationString = Trim([fldSystems])
   Me.txtSystems.RowSource = strInformationString
   Me.txtSystems.SetFocus
   Me.txtSystems.ListIndex = 0
End If

Once the combobox is expanded - you have the option to edit the list. Property Sheet for the combobox is attached.