Multiple list box

[Atalla]

Hi all,
This question has been addressed before but unfortunately, all the solutions provided apply only to Single user environment. Even the ones at Microsoft.com
Briefly, I have 3 tables:

tblAgent:
AgentID, FirstName, LastName
tblLanguages:
LangID - Data Type is Text
Field Size = 2
LangDesc - Data Type is Text
tblAgentLang:
AgentID - Data Type is Number
LangID - Data Type is Text

The same scenario, 2 list boxes on a form: lstSource with tblLanguages as Row Source. lstDestination is unbound where will contain the selected records from lstSource. This will be done via a command button or double click event. AgentID exists on the form so when choosing an employee, his/her relevant information appears (i.e., FirstName, LastName). My challenge is inserting AgentID and LangID selected in the lstDestination into tblAgentLang respectively. Correct me if I am wrong, every field should contain a single record, i.e., I don't want to insert multiple-selection list box to a single field by storing a semicolon-delimited list of values as it would be very difficult to query on that field.
So,in an ideal world, could have the same AgentID more than once as well as LangID. In other words, an Agent can speak many languages and a language can be spoken by many Agents.

Note: For technical and access reasons, tables cannot be altered in any ways, cannot use the Database Splitter.

I hope my question is clear, if not please let me know.

Appreciate very much your help in advance.
Thanks.

 

[FoxProProgrammer]

Hi Atalla,

Let me rephrase your question so you can let me know if I understand it.

Your form has two list boxes: lstSource and lstDestination. The user double clicks (or clicks a button) to move an item from lstSource to lstDestination. Once they have moved all the items that they are interested in, you want to save the data that is in lstDestination in tblAgentLang.

If this is the correct scenario, to do this you will need to iterate through the rows in lstDestination and save the data in each row.

Your other question: "Correct me if I am wrong, every field should contain a single record.."

More correctly, every record can contain one or more fields. In this case, each record in tblAgentLang will contain two fields: AgentID and LangID. I am assuming that the data in these two fields has been moved to lstDestination.

Just curious...instead of using two list boxes, why don't you use a single multiselect list box? The user would click the items that they are interested in, and then you could iterate through the Selected items and store the data in tblAgentLang dz
dzaccess@yahoo.com

 

[Atalla]

Thank you very much for your reply. You clearly understood my question. There is no problem to iterate through the rows but the problem is AgentID field is not part of the list boxes, but rather a text box on the main form and thus saving the AgentID field in the table is my opstacle.

In addition, I am using 2 list boxes as a mean to show to the users the choices the've made, i.e., languages that are spoken by an agent.

If my comments are not clear and if you don't mind, I could e-mail you a sample of the database to have an idea.

Thanks again for your help.

 

[FoxProProgrammer]

Atalla,

The easiest way to do what you want is to put AgentID in lstSource and move it to lstDestination with the LangID. You can hide that column by setting the column width to 0 if you don't want the user to see it. Alternatively you could look up the AgentID for each item in the list box, but I think that would be more trouble. Instead of setting the Row Source of lstSource to tblLanguages, you would set the Row Source to a query that includes both LangID and AgentID. If you have trouble implementing this, please send me your database. I haven't had as much time to spend at this website lately, but I'll do my best to look at your code.

Best regards, dz
dzaccess@yahoo.com