We help IT Professionals succeed at work.

Extracting Info from a table to a Combo Box (excluding seperator)

dsteers
dsteers asked
on
Hi,
I was wondering if anyone could help me out with this little problem that I face,thanks.

How do I retreive the info saved in a text or memo field (entered from a form),into a combo box.
E.G - the Combo box has 3 items in it on the form,they are saved in a text or memo field in the table each one seperated by a ","
     i.e. Text or Memo field = Cat,Dog,Parrot

How do i make the form that you are entering the data from allow me to extract that information (to a combo) and to write that information (written into a text box) to the Text or Memo field including the ",".

Some code would be much appreciated.

Thanks
Dave Steers
Comment
Watch Question

Commented:
Basically the text-field you describe is equal to the data you use when defining a combobox based on a "value-list".
The only difference is that you would need surrounding qoutes so: "Cat","Dog","Parrot"
To implement this you can assign the text-field (after using a quote-insertion function in VBA) to the value-list of the combo. To add a field you'll need another piece of VBA code that first asks the user or his entry was no typo, followed by adding it to both the combo value-list and the original text-field.

Personally I would however never store multiple values into one field. You'll find out why, when starting to filter or count the different values...
Storing the DISTINCT-values in a different table and the creation of a relation table is the "universal" solution.

Need more info ?

Nic;o)

Author

Commented:
It's just that I need a way so that the user can add values (that appear in the combo) to a field,rather than having numerious fields,but that'll be ok.

The DB prety much allows the user to categorise software,
Categories such as "Database,Spreadsheet,Graphics" etc.
And the user will be able to select more than one category for the software.
I.e Microsoft Office would contain "Database,Spreadsheet,Word Processor" etc.

A query also needs to be done on whether the particular piece of software can do "Word Processing" so it needs to be able to find it.

-----------------------------------------------------------

So the solution that you see is to create a new table that contains the categories like those mentioned above.
Sorta like a Yes/No thing right?

And link them to the Software tabe.

this right?

Author

Commented:
Any code that you could supply to help the solution that would be very much appreciated :).

Thanks
Dave

Author

Commented:
But for now some code for the first solution (about the seperators) would be much appreciated.

Thanks
Dave Steers

Author

Commented:
I'll gladly increase the points for both this first solution and the second one.

Commented:
I once used such a field and choose for the table solution.
For the table solution you need two tables:
1) tblCategory
with one field: [Category]
2) tblSoftwareCategory
with two fields: [SoftwareID] and [Category]
(I assumed your original table is having [SoftwareID] as unique key!)

Just use the description field [Category] as the unique key and add on the relationship a relation with referential integrety and cascade-update.
In the tblSoftware you add a (redundant) field [Categories].

Now you need to synchronize the contents of the (redundant) field [Categories] with the contents of the rows of the tblSoftwareCategory for the effective [SoftwareID].
For this I used a datasheet subform that's enabling the user as many selections as needed.
Behind the subform the mainforms (redundant) field [Categories] is updated after a change.
On that subform a button is added to enable the user to add new categories to the tblCategory.

Just start yourself with constructing the tables and forms, when stuck drop me the compacted and zipped .mdb at my nico5038 mailbox "at" yahoo.com
The more you do by yourself, the easier the maintenance will be afterwards!

Nic;o)

Author

Commented:
ok great :)
I've made a category table and linked it to the software table,like you said, thanks :)

All I need to know now is :
 *How to create custom fields at runtime & define them.
and
 How to modify existing fields at runtime.

Cause the categories are fields in a table.

Thanks Again
Dave
To add the items to the combo box.  I'm a little unclear about where the string is coming from, or how long it might be, but once you have it, just pass it to this sub.

Private Sub PopulatePetCombo(sPetString as string)
    Dim lngLen as long
    Dim lngCounter as long
    Dim sItem as string
    Dim sChar as string

    lngLen = len(sPetString)
    sItem = ""
    for lngCounter = 1 to lngLen
        sChar = mid(sPetString,lngCounter,1)
        if sChar = "," or lngCounter = lngLen then
           comboPets.additem sItem
           sItem = ""
        else
            sItem = sItem & sChar
        end if        
    next
end sub                  

Author

Commented:
Here are the next promised points Nico.

Thanks
Dave
Commented:
Check your mail ;-)

Nic;o)

Author

Commented:
Thanks for all your Help Nico :-)
I really appreciate it.
You really have earned these points.

Thanks Again
Dave Steers

Commented:
You're welcome Dave, success with the application !

Nic;o)

Author

Commented:
Yes Thanks Nico
Seeya

Dave

Author

Commented:
Have a Merry Christmas & Happy New Year :)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.