Unanswered: Getting data from a list box to a table
I'm in the process of building a database and have created a form with 2 multiselect (simple) list boxes but I can't get the selected data to save from my list boxes to the table, even if only 1 item in the list box is selected. A simple solution to this would be appreciated as I have limited VB and SQL knowledge. I've included a copy of the database (it is password protected so use the password add to access the form).
I couldn't find in which form you put list box, anyway get the number of items in your list box and make a loop from 0 to your list count-1 to put each item in the related table.
For example if your list box name is List1 then
for i=0 to counter-1
data of each item can be verified as: me.List1.ItemData(i)
'Code to put data in the table'
In addition your password security is better to be dynamic and let user to change the password from time to time instead of put password in code; you can find good examples in Code Bank for security
Thanks Aran1. I'm not sure where that loop code is supposed to go.
The form which contains the 2 list boxes is called frm_RM_Main and the related table (the one that stores the info from the input form) is called tbl_RM_Main. The tables where the data which populates the 2 fields on the form which users can then select from are called tbl_function and tbl_controlled_copy_distribution.
The database is only a prototype (for a pilot) so the level of security you have recommended is not required for the purpose of this build.
Forgot to say that the password I provided to access the input form needs to be used in conjunction with the middle selection on the login form (Add / Modify Records).
The advice Aran1 gave you will only work once your design supports the use of list boxes.
You have a table tbl_RM_Main (whatever that means), but to store items from a listbox the way you want to do, you would need to have a related table for each listbox. So for example, you have a listbox for RM_Function, but you have no table to store data relating to both tbl_Function and tbl_RM_Main (a junction table). Therefore you have nowhere to store the data.
Not to mention the fact that there are no relationships defined at all, which is just setting a time-bomb for disaster...
IMO, you need to get your table design under control before doing anything else.
And tbh, designing a database such as you are, you should learn more about VBA and SQL while you are at it! It will save you a lot of stress if you understand them well