Saving Multiple selection values from a form to a table

[haritbhasin]

Hi,
I am fairly new to access. Below is the link for the database(I AM ATTACHING THE FILE AS A ZIP FILE AND I CHANGED THE EXT: TO JPEG. PLEASE CHANGE IT TO ZIP) i am specifing my problem and if you can solve the issue that would be great.
In the database, there are 3 tables :-
Categories,Experts and Region.ExpertManagementDatabase_Updated_dropdown.zip
I created 3 queries by which i am able to select the options in the form and on the click event of each level i did re-query to get the values in the list (If you will check the database once you will get the whole idea). Now once the options are selected i want to save these values in a fourth table and i think that will be a join table but i am not able to figure out how to save the values and i need to create a search page also where user can select from existing users using the search criteria which will be dependent on the drop downs that are in the table and random words.
Is there any way that i can and a multifield drop box by which i can select the options in the first drop down and once i select those i can get a second option where i can select multiple options corresponding to that one.
Please help me with this, i am really stuck with this and want to solve the issue asap.

[June7]

Review
http://office.microsoft.com/en-us/ac...010098674.aspx
http://datapigtechnologies.com/flash...combobox2.html
http://datapigtechnologies.com/flash...combobox3.html
http://datapigtechnologies.com/flash...tomfilter.html

[haritbhasin]

Hi,

Thank you for your reply. But as you can check in the attachment i am able to populate the values in the combo boxs. But i want to save these values in a different table that should contain the user id from the expert table as i already defined the userid as a primary key and the then it should contain fields to save the values for itype, solution and industry and once these values are saved i want those values to be get populated in a sub-table so that a user can review the form before closing the for or before entering the data for another user.

[June7]

What is purpose of this form - to create/edit Expert records? Each record is unique for expert name?

What is the Category table for - as source for category options to associate with an expert? Can each expert have only one category?

Use a combobox bound to CategoriesID field of Experts table to choose a category. Suggest not using this form as vehicle to create new Categories records.

[haritbhasin]

Hi,

The purpose of the form is to add and edit the experts. Earlier we were using an excel sheet with drop down options to filter out the experts belonging to different categories.
Each expert can have more then one categories ID assigned to him.There are 3 different type in the categories table (itype,solution,industry). So each expert should have atleast 1 itype, 1 solution and 1 industry assigned to his profile. But an expert can have more than one itype,solution and industry assigned to hipror example am.So i need to save this information in another table with bet selecting the drop down options.So for example expert one with the userid:1 can have itype with ID 30, solution with ID 40 and industry with ID 50 ASSIGNED TO HIS PROFILE.

So please tell me how to save these information from the categories table to another table with expert table userID as the primary key.


so the table for saving the values will something look like this:-
UserID iTYPE Solution Industry

Please help me to save the values from the form to this table and how to create the search form for this.

[June7]

I have provided you links to tutorials that can guide you in this effort.

You need a child table to save the multiple Category selections for each expert. Consider this simple structure:

UserID (foreign key)
CatID (foreign key)

Use a form/subform arrangement for data entry. See the first tutorial link I reference.

[haritbhasin]

Man you are proving to be a life saver. I created the subform already. The thing is i am able to select the values using the combo boxs now i want to know how to save the values that i selected using the combo boxes. The values in the combo box are related to each other and more over i am using only one table to selct the values in the drop down.but the values for itype solution and industry needs to be independent of each other as i did in the form that is in the attachment in my first post. So when i selct the level 1 value for the itype i selct the level 2 value dependent on it then for that itype i have a unique id and the same way i hqve unique value for solution and industry.but i have all these ids as the first row in the categories table. I want to know how to save those values and how to distingush which id is for itype solution or industry. I know it can be done by selction query but i dnt knw how to write it for this pqtricular case as i am using one table.

[June7]

What do you need to save - the Category ID value? This will identify the Category record associated with the expert.

The second link in post #2 is a tutorial for dependent comboboxes.

[haritbhasin]

Yes i want to save categoryid

[June7]

Then attempt the dependent combobox coding by the example provided. Or use multi-column combobox to select the appropriate Category record. When you encounter specific issue, post question.

[haritbhasin]

Hi,

I tried all the things that are mentioned in the four links provided by you. I created table and sub table architecture. Now i want to save the values that i selected from the drop down list (that are in the frmMain ). So once i click on save i want the corresponding ID values from the iType, solution and industry table to be saved in the rltnship table corresponding to the userid that is the primary key in the experts table. Once these values are saved i want to display these values in the expertexpertise table.


Please Help me to do this as it is becoming critical and i am not able to figure out how to do so.

[June7]

I see you have changed the data structure by having separate tables for I-Type, Industry, Solution. The original setup could have worked but this is fine.

You have the rltnship table set up to save number values for Itype, Industry, Solution. The expertexpertise form does not have comboboxes, they are unbound textboxes. Make comboboxes multi-column (review the tutorial), something like.

RowSource: SELECT DISTINCT ID, Level1 & " / " & Level2 As Levels FROM [I-Type];
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";2"
ControlSource: Itype

Also, set expertexpertise RecordSource property to rltnship. Set the expertexpertise subform container control Master/Child links properties to the UserID pk/fk fields (again, review tutorial article).

Binding means data entered into controls goes directly into fields of table. These entry/edits are committed when the form closes, move to another record, or run 'save record' code.

This is all basic Access functionality. If my instructions do not help, then you need to get an introductory book and do exercises until the concepts are understood.

Once you solve saving data, then deal with how to search/filter data. Review referenced tutorial again.

[haritbhasin]

Hi,


Thank you for your support man. I am able to save the data. I created the combo boxes as suggested by you. But the thing i was looking for was different.

I want the data to be populated using the form that comes up using the Assign expertise button. I want the user to select from the drop down and once the user selected the level 2, the id corresponding to that level 2 needs to be saved into the table. I think i can do this using the same procedure. But my concern is that i want those values that i selected in the frmMain form to be shown in the expertexpertise form, using the relationship table and form that only i was using the text boxes so that i can populate the values that i selected from the frmMain. So please tell me how to acheive that (how to reflect the values from rltnship table in the expertepertise form (Level1 / level2 on the ids))

[June7]

Sorry, not clear what you want to do.

You have the form/subform correctly saving data? Why would frmMain be involved in saving values?

I already described how the comboboxes of expertexpertise can be set up to show the levels data.

[haritbhasin]

I want to select the itype solution and industry to be selected from frmmain coz I don't want to add new line every time I try to add something. This is what happening in expert expertise and more over I can update the already selected values in want those values to be un editable once I selected the values if I want to use expertexpertise form to enter the data.