Ways to ENTER DATA using seperate DATA SHEET? - PLEASE HELP save my sanity! -

[floyd]

Two days I have been working on this and I have to admit defeat.
PLEASE forgive me if I don't speak in the exact terms you are used to... I am still learning and don't mean to offend anyone.

I have a relational database and because of the amount of non-changing (re-usable data), I have multiple list boxes tied to various data sheets (all listed as tblDa...t).
These house the followings: Audience / Delivery Type / Item Type / Topics

I have junction fields built which tie these data sheets to the actual records (all listed as tblJnc...).

My MAIN tables are for Courses and Files. (Files may or may NOT be assigned to various courses) (All listed as tblMain...)

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

I have sub forms to help populate the Data Entry forms (all listed as frm

My "Active forms" to Enter Data are (frmInputCourse and frmInputFiles)
THESE are the two where I would like to have multiselect (multi select) listboxes. (**frmInputFiles** gives the best visual representation of what I am looking to do)


The frmInputCourse should have multislect listboxes tied to tblDatAud / tblDatdlvry / tblDatTopic (each contains a junction table already created)
the frmInputFiles should have multiselect listboxes tied to tblDatAud / tblDatdlvry / tblDatTopic AND tblDatItemTyp

The listbox should allow you to select MULTIPLE for each box and then place that information in the appropriate junction box.
(BTW - the frmInputFiles Course listbox is pulling the primary key instead of the Course name at the moment)



If there is an easier way to do this, I am open to suggestions.

I will offer any information you need (including giving you the full database I am working on) Enclsoed in the following zip file.

Please ask... I will answer anything and be eternally grateful.

JoeTeamToolbox.zip

[floyd]

I have created a data entry form:

The multi select list box is giving me FITS!!! (Item Type / Audience / Delivery Type / Topics)
Is there an easier way than using a muti select list box to allow a user to enter data where multiple pieces of information come from a separate table and need to go into a junction table????

PLEASE HELP!

[ssanfu]

An alternative would be to use sub-forms.

Muti-select list boxs can take a lot of code (at times) to use them effectively.



PS - just found your post from 3:59AM. Reading it now...... (Mod edit: threads merged)

[June7]

Related thread https://www.accessforums.net/databas...elp-37424.html

[floyd]

An alternative would be to use sub-forms.

Muti-select list boxs can take a lot of code (at times) to use them effectively.

PS - just found your post from 3:59AM. Reading it now...... (Mod edit: threads merged)

Wow... merging these threads confuses even me.
I think they are separate topics.

1. How do I?
2. Is there a different way?

[June7]

Multi-select listboxes (or comboboxes) are often used to select multiple values into a single multi-value field. I NEVER use multi-value field. Don't think I've ever used a listbox for data entry. I will use unbound listbox to select parameter for a procedure.

VBA code could cycle through listbox items and save record for each selected item.

The VBA itself is not difficult. I don't understand what you want to save where - having trouble reconciling the form image with the tables/fields from the last ERD you posted in other thread. I suspect you need a subform in place of each of the listboxes, as already suggested by Steve. So the stumbling block is understanding db structure and therefore threads are related.

Hopefully someone smarter than me about db design will respond on your ERD question in other thread.

[floyd]

I have the Database attached in the first message in the thread.

I have scrapped the multi-select listbox idea as three days of frustration is too much time to spend on anything.
I will look at the subform idea though I have been focusing so hard on the listbox idea that I am having a hard time coming to terms with how I can pull off the subform.

If anyone can help me understand how this might look / work... I would appreciate it.

[ssanfu]

I spent about 2 hrs looking at your dB last night. And I am really confused with your structure. Obviously, I don't know your specifications, but I think you should work on the table structure before creating forms.

FYI, "File" (jnct_Type_File) is a reserved word and shouldn't be used as object names.
See http://allenbrowne.com/AppIssueBadWord.html

I would remove the spaces in the field names in the table "Courses"... but that is me being picky.

There are 7 tables with a field named "ID". I would take the time to give meaningful names... maybe "TopicCourse_ID" for the table "jnct_topic_Course".
I use "ID" or "_PK" suffixes for the primary key field. And I use "_FK" for the foreign key fields. Easier for me to see what is what.

I have scrapped the multi-select listbox idea

I can kinda see what you are trying to do with the list boxes, but it will take a lot of code. If you use multi-valued fields, you don't have to write code to add/delete entries, but you will have a lot more problems viewing/printing the data. (I never use multi-valued fields.)
Using normal fields, you have to have code to add/delete entries, but viewing/reporting is lots easier.

As I said, I don't understand your structure. You have 14 tables; 7 are junction tables. That is 50% of the tables in your db. Seems excessive to me. I have 26 tables with 2 junction tables. I'm just saying.....

For instance, looking at tables "dat_Item_Type" and "Files".
One "FileType" can be assigned to many files
Can one file have several types??


Tables "Files" and "Courses":
One course can use many files
One file can be used in many courses
If both statements are true, then many-to-many and need a junction table.


I can see where a "Course" has an audience, but does a "File" have an audience???


Can you describe what you are trying to do from a course perspective? Forget the table structure...
What is a course name?
What is (are) the course file(s)?
What is (are) the course audience(s)?
What is (are) the course Delivery(ies)?
What is (are) the course Topic(s)?

Now describe what you are trying to do from a file perspective...
What is a file name?
What is (are) the course(s)?
What is (are) the file audience(s)?
What is (are) the file Delivery(ies)?
What is (are) the file Topic(s)?


I'm struggling trying to understand the process... just haven't been able to wrap my head around it.