Adding and displaying records with Multi Select List Box

[abbyanu]

Hi

I have three related tables:
GL: gl_id; gl_name
GL_Acct: gl_id; acct_id
Account: account_id; account_name

There exists a 1:many relationship between GL and GL_Account, and between Account and GL_Acct.

I wish to populate GL_Acct using a multi-select list box in the GL form. For example one gl_id can be associated with many acct_id. This would be displayed in a list box and the user selects whichever apply. I wish the selected records to be stored in the GL_Acct table. When a user accesses the GL form, I would like the associated Account records in the GL_Acct (where the GL.gl_id = GL_Acct.gl_id) to be highlighted on the list box. I wish to use the list box to populate the GK_Acct table as well as display records from it.

I have seen a couple of examples, but still I'm not sure if the above is possible. I prefer the list box because in some instance there will be many records to be selected. Any ideas?

Many thanks for your help.

 

[willir]

Yes, this is possible, but may be problematic, especially if your accounts are spread out. ...And if the end user miss clicks and unselects, they may have to start over.

Another approach would be to use a subform with a combo box to select the accounts. Since you are adding records to the table, might as well use the table for the subform and have the end user enter data that on the subform instead of using code to transfer the list box entry to the table.

 

[abbyanu]

Thanks, Willir

I thought a listbox would be more user friendly, but i guess I would have to settle for the subform.

 

[willir]

List boxes will/may work, but I assume you will have numerous accounts. If you have to scroll up and down more than three pages in a list box, it becomes problematic.

Also, there is a limit to the number of items that will be displayed in a list box. See Tools -> Options -> Edit/Find

If the number in your list box is short, then your idea has real merit.

Richard