Listbox value not save

[darrenislearning]

Hello, I am very new to Access. I created a simple form to explore and learn.

I have this table, tblstudent and it contains these fields (ID, StudentID, FName, Interest). Interest, is a multi-select listbox.

I created a button to save the record but it is not saving my multi-select items. Or I need to use VBA to save? Any expert can guide me. Thanks

 

[June7]

Assume you want to use a multi-value field. This must be defined in the table design.

What code is in the button?

 

[darrenislearning]

Must use VBA to save items selected with multi-select listbox. Do you want a separate record for each selection or are you trying to save to a multi-value field?

All multi select in one field (interest). I dont need to have comma, just new line in that field

 

[darrenislearning]

I did not set write any code. All was based on macro button to save record

 

[theDBguy]

Hi. If the form is bound, there's usually no need to create a save button. Access will automatically save the record for you.

With regards to the multi select listbox for interests, most developers will tell you to avoid using a multi value field (MVF) to do that. Instead, you should consider using a child table and a subform instead.

 

[darrenislearning]

Is there any simple sample on the subform for my reference? I saw the subform but not sure how to use it. And in the future, i want to expand on the form where user have to login to save the record

 

[arnelgp]

sample with Student and subform of interests.

 

[darrenislearning]

sample with Student and subform of interests.

Thks. Will try to explore on it.

 

[darrenislearning]

Hi, I'm back again and I'm still struggling with the work. May I know can I don't do with a sub form as my form will be expanding and I am considering to have navigation control in the future. And the listbox value is not referencing to table. The purpose of the navigation form is to split the form into parts and the each tab will save the data into different tables.

 

[June7]

What do you mean by "form will be expanding" - expanding how?

What other tables?

You should probably provide your db for analysis.

 

[darrenislearning]

Expanding with more fields and placed into tab control.
For now, I have tables for student particulars, courses taken, student nok details. All will linked from studentID

I have messed up my db and currently it is not working so I am unable to share my db

 

[Pat Hartman]

A subform with Interests is the way to go. Do NOT mush multiple values in a single field. It may look simpler but it is not. This violates first normal form and will make it difficult for you to change the Interests list as well as to use it for analysis. All columns must be atomic and indivisible. This is the same reason that you don't put all parts of a name or an address in a single field. It is just too hard to split everything back out again when you need the individual parts.

 

[darrenislearning]

A subform with Interests is the way to go. Do NOT mush multiple values in a single field. It may look simpler but it is not. This violates first normal form and will make it difficult for you to change the Interests list as well as to use it for analysis. All columns must be atomic and indivisible. This is the same reason that you don't put all parts of a name or an address in a single field. It is just too hard to split everything back out again when you need the individual parts.

Thanks for advise.

 

[June7]

Even a 'non-working' db could be provided for analysis.