Urgent help needed on forms

[syedalisajjad]

Hi,

I need help from you guys about access forms. I am totally new with access and vba, having trouble.

I have a form where I have a combo box which is getting the values from a table.

Than I have a sub-form which gets values from some other table...

what i want to do is, i select a value from a combo box, then i select values from the sub-form and when i click on the submit button, the value of combo box is inserted into the table number of times i selected the values in sub-form.....

i have the following tables.....

tblTest
--tblTestID
--TestName

tblComponent
--ID
--Name
--



tblTestResults
--ID
--tblTestID (FK)
--tblComponentID (FK)
--Result

combo box will be displaying values from tblTest column TestID
sub-form will contain all the components

e.g. i select a test from combo box, then let's suppose i select 10 components from sub-form and when i press save button, the value selected in combo box is saved to the tblTestResults 10 times along with the components i selected in sub-form

please help me out....i shall be very thankful to you.

thanks

[pbaldy]

If you want to use a listbox instead of a subform (how would you "select" items on the subform?), this should work:

http://www.baldyweb.com/MultiselectAppend.htm

[khalid]

Hi, Sajjad!
as Paul suggested, there should be a list box with multi selected option where you can select the desired Items.

The question is, Why you want to insert the Combo Value into the Table with number of time the selected items on the form? Do you just want to insert only the combo value into the table OR you want also to Add the selected items on the form into the table along with the value in the combo box?

[syedalisajjad]

Hi Paul, thanks a lot for your help...i will try this solution....

@ Khalid

Dear Khalid,

I also want to add the values selected in the list box along with the value selected in combo box. I have a table test results where TestID and componentID are foreign key so what i want is user selects a test from combo box then he selects the components he want to perform the test on from the list box. let's suppose if a user selects a test in combo box and then he selects 5 components from the list box and then when he presses the submit button these 5 components are saved into the child table and all these 5 records contain the same testid....so i wanted to store the value of combo box the number of items selected in the list box in table.

Thanks

[khalid]

OK,
This can be achieved through vba code, only one question is left, why you don't want to save all the selected component in one row in tblTestResults in the related fields (you might have to create more columns in tblTestResults)?

Whatever the logic is there that can be achievable, but dear its very late here and I have to goto office, tomorrow from my office I will let you Inshallah and we get the conclusion.

[syedalisajjad]

AOA,

Khalid bhai all the components are unique and all the components which are selected for results may have different results like passed or failed. there is a column in tblTestResults 'Result'. so if 5 components are selected for test and the user presses the button, tblTestResults will contain the same test in testID column 5 times against each component and against each component the result column will contain 'open' which can be changed to passed or failed later on

[khalid]

Dear Sajjad!
AOA

I worked on your request and have done as you requested. The sample db is attached herewith for your reference and guidance. The idea is taken from pbaldy's post.

There are three tables in the db,
tblTest
tblComponent
tblTestResults

A combo box, a list box with multi selected option true. combo box is populated from tblTest, Listbox is populated from tblComponent, and a button on the form which have the following code:

Private Sub cmdEntry_Click()

Dim ctl As Control
Set ctl = Me.LstComp
For Each varItem In ctl.ItemsSelected
CurrentDb.Execute " INSERT INTO tblTestResults " _
                  & "(tblTestID, tblComponentID) Values " _
                  & "('" & Me.cboTest.Value & "'," & "'" & ctl.ItemData(varItem) & "')"
Next varItem
End Sub

Records will be added to Your tblTestResults the number of times the components selected, each componentDI will be added to the table along with the TestID selected in combo box:

Hope this fulfill your requirements.

Good Luck!

[syedalisajjad]

Dear Khalid bhai,

AOA,

Thank you so very much for your help and time. I will look at it. Jazakallah

thanks once again

[khalid]

Dear Khalid bhai,

AOA,

Thank you so very much for your help and time. I will look at it. Jazakallah

thanks once again

You are welcome bro! Glad to hear that it works for you, if you feel that your problem is solved then please make this thread as solved by using the thread tools top right cornner of the post and clicking the scall icon right over there.

Thanks to Paul for his site link and guidance too.
Have a nice day!

[designerx007]

I am stuck with form and subform. when I add a subform to my main form.. it gives an error message "This expression is typed incorrectly......"