ListBox Nightmare!

[djreyrey]

Hello all!

I have not been able to find the answer to my question. Everything I find is opposite of what I want to do.

Any who...I have a listbox on my form that will be MultiSelect. Let's say the listbox contains the names of fruits: Apples, Oranges, Grapes, Mango. The user should select which one he/she likes. So let's say he/she picks Apples and Oranges. Both of these entries should be stored somehow in the table for the same person.

Every thread or example I find on the internet has a listbox where they can select more than one item but the items on the listbox don’t necessarily affect the same person. For example, I saw a database example that had a list of employees. The user could select multiple employees for which you could set a meeting date. Then, when the command button was pressed, the meeting date would be entered in the table for each employee. Thus, each item is stored in different parts of the table.

The thing with my listbox is that the items being selected would be for the same person. How then, if not more than one item can be stored in one field, could I store the Apple and Orange in the table?

I appreciate your time and assistance. Only with your guidance can I make sence of this. Thank you!

 

[pbaldy]

The normalized solution would be to store the selections in a related table:

http://www.baldyweb.com/MultiselectAppend.htm

If you reaaaallly want to store them in one field:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210203

But note that even MS recommends against it.

 

[djreyrey]

pbaldy,

I appreciate your response. Thank you for the examples. After reviewing the code and samples you provided, I'm not sure how to put it to use for what I want to do. I'm putting together a database for a government agency so without giving too much information, here is what I need:

I have a database that will be used to track field incidents. These incidents can be anything from traffic stops to drug busts. The form will require the agent to fill out the information regarding the particular incident. On this form, there's a listbox from which the agent should pick if certain types of chemicals were detected.

Let’s say I'm the agent and I'm filling out the form regarding this incident. If I had a drug bust and noticed certain toxic chemicals (myth, liquid crystal, chlorine) at this house, then I would pick all three of these chemicals from the list. The sample you attached was a good example. However, I need to be able to add the chemicals on the listbox to the incident (or record) that is being created.

The sample assumes that you will be entering something in a text box and then storing that data in the table. In my case, there will be no text box (at least I don’t think so). All I need is the listbox and store the selected items in a table. I should NOT store the data in a single field because we will need to run reports on a regular basis.

I think I “kind of” understand what needs to be done but I can quit wrap my head around it.

Does any of this make sense? Hope it does. And again, I thank you for your time and assistance.

 

[pbaldy]

I would assume your form would be based on an incident table of some sort, with a key field along the lines of "IncidentID". It would be that field that I would expect to be appended along with the selected items from the listbox. Given your example, that would give you 3 records in the related table, each with the same IncidentID and myth, liquid crystal and chlorine respectively.

 

[djreyrey]

Going with your example database Baldy, how would you modify the code assigned to the command button so that it takes the data from the listbox instead of the text box?

 

[pbaldy]

Not sure what you mean, as the current code already gets values from the listbox. It takes a value from the textbox ("Other value") plus the selected values from the listbox ("EmpID"). I would simply get a value from whatever your existing IncidentID field is instead of the textbox. So instead of

rs!OtherValue = Me.txtOtherValue

it would be

rs!IncidentIDField = Me.YourIncidentIDControl

Post a sample of your db if you're still confused.

 

[gemma-the-husky]

pbaldy is right - hopefully this will make it clearer

lets say your suspect had heroin, meth, and pcp (see - i watch the wire!)

then you are tring to store a table that says

suspect id, suspectname, articles found (several of)

what you need is TWO tables

ONE table that stores the cases

[B]
(so you actually store)
suspect id, suspectname[/B]
1               Jim Smith

and ANOTHER TABLE that stores the details

[B]
(so you actually store)
suspect id, substance found[/B]
1               heroin
1               meth
1               pcp

-------------
note that in practice, you might take it a step further, and store a suspect table, and a case table linked to the suspect, with the substances table linked to the case - as a suspect may have multiple busts.

--------
but as far as handling the list box goes, you have to use code somewhere that basically says

for each selected item in the list
add the item to the subsidiary table (which would normally be done in code by forming and executing a SQL insert statement)

 

[djreyrey]

Thank you all for your help so far.

If you don't mind, please see the attached PDF for more details of where I am and where I would like to go with this database. I included some screenshots.

 

[pbaldy]

Well, you say that the table doesn't include a field to tie it to the main record. It needs one. At a guess, it appears EventID should also be in the table the listbox is appending to.

 

[djreyrey]

However, the EventID "is" already in the table I am appending to. When the ListBox AfterUpdate event triggers, how does the event add the data from the ListBox and also tie it to the main record table? This is where I am stuck. I can't get my brain to think outside this part.

In looking at your database example, the OtherTable has the EmpID column which is also in the tblEmployees (the main table). I noticed you don't have any relationships or any other way of tieing the tables together. How is it that, when you click the command button, the code knows how to associate the ListBox data to the EmpID from the tblEmployees table? It seems like everything in the code refers to the OtherTable and nothing in regards to the tblEmployees. And yet, the EmpID from the tblEmployees column is somehow added.

I would really appreciate a detailed explination.

* I'd like to also ask if you could explain what this part of the code does. I'm not a VBA expert. Thank you very much.

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblOtherTable", dbOpenDynaset, dbAppendOnly)


and


Set ctl = Me.lstEmployees
For Each varItem In ctl.ItemsSelected
rs.AddNew
rs!EmpID = ctl.ItemData(varItem)
rs!OtherValue = Me.txtOtherValue


What does db.OpenRecordset and ctl.ItemData(varItem) do?

 

[pbaldy]

The EventID in that table is an autonumber in that table. It is not the EventID from the other table, which is what it needs to be. It should be coming from the EventID on that form. Otherwise, how can the tables relate to each other?

In my sample, the employee ID's come from the listbox, which has as its rowsource the employee table. While I didn't bother setting up relationships in the sample, there is obviously one on EmpID. The code does exactly what you need it to do. It takes a value from a textbox on a form along with selected values in a listbox and appends them to another table. My "other value" is your event ID, my EmpID is your IsotopeID.

 

[djreyrey]

Ok, I did what you suggested but the EventID is not appending to the IsotopeFound table.

FYI - I changed the name EventID to PRDID so that the column names match in the main table and the IsotopeFound table. Please see attached file.

 

[pbaldy]

The line

rs!PRDID = Me.IsotoopeList

should be

rs!PRDID = Me.PRDID

or whatever the name of the control containing that value is. This would be much easier with a copy of the db, by the way.

 

[djreyrey]

Thank you! I will try this out tonight and let you know if it works.

I can't post the DB because it already has sensitive information stored in it. The government wouldn’t appreciate that very much.

Thank you though for being patient with my screenshots. If the database didn’t have records in it already, I would have uploaded it.

 

[djreyrey]

OMG!! Pbaldy, it worked! It's inserting the PRDID number. It does not make any sense to me logically but it's working none the less. Yeeeha!

Thank you so very much. The only problem now is that when I move on to the next record, the items are still selected. And if the items are still selected, it wants to add those same selected items to the next record and so on.

I checked out a few samples but was not able to find code that will clear the list when I move to another record.

In addition, if I go back to a record that has items selected in the ListBox, I'll need those items re-populated so the user can see what items were selected for that incident and without adding the items to the table again since they were already added once before.

Again, thank you!

 

[pbaldy]

This is one way to clear the selections:

  For i = 0 To ctl.ListCount - 1
    ctl.Selected(i) = False
  Next i

I generally let users add records with the listbox, but manage the selections later with a subform. You can re-select the listbox items, but since it's hard to tell what the user has done with them, you'd need to delete/re-insert the items in the table when they're done.

 

[djreyrey]

Pbaldy,

Thank you for that example. I was able to implement it in my code correctly.

This leads to my next question.

How can I append a field other than the bound column?

Please see attached file.

 

[pbaldy]

I'm not sure what you're after. You can certainly refer to a different column in the listbox, but given your example, good design would be to store the ID in the related table, not the text description. You include both tables in queries to get the description associated with the ID.

 

[djreyrey]

Pbaldy,

That's a good point. I didn't think about the query. I was just thinking of the table itself. I'll create a query in the morning to make sure it all works.

Thank you greatly!

 

[djreyrey]

Pbaldy,

Well, everything is working great! Thanks to you, everything is flowing as it should. We're down to the last bit of business for this listbox:

If the user needs to unselect an item on the list, what method would you use that would allow the user to remove a certain item from the listbox for a certain record?