Append a Table

[dr223]

Hi,



I am working with Access 2003.

I have a form called 'frmMain' on this form I have 4 questions..

Each question has a combo box next to it where the user should select from the list i.e., cmbcri1, cmbcri2, cmbcri3, cmbcri4.
These combo boxes are populated each with the data from tblCriteria1, tblCriteria2, tblCriteria3, tblCriteria4 respectively.

tblCriteria1 has 3 fields (and so are the rest of the tables)
ID
Preference
Weight

The preference value is displayed on the combo box for the user view and selection.

E.g

ID Preference Weight
1 Not Well 0.20

Now, what I want to do is when the user selects all the values from all the 4 combo boxes, and Click 'Submit' values of the Weight (for the above example 0.20) to be saved in table called TblDetails.

Any help how I can achieve this ?

Thank you

[Robeen]

Your Combo Box displays "1 Not Well 0.20"?
And you want to get the "0.20" out of that string and insert it into TblDetails?

Is that correct?

Or does the Combo Box only display "0.20"?

[dr223]

The combo box displays "Not well" ONLY , but i want to save 0.20 to the TblDetails

Thanks

[Robeen]

I'm not sure if you specifically NEED to have your code for inserting the weight into TblDetails behind a Command Button.

I have the following code behind the On Change event of the combo box.
You can just paste the code into the On Click Event of your button if that is what you need.

This code inserts the Weight that corresponds to the Preference you select -> into TblDetails.

 
Private Sub Combo0_Change()
 
Dim strPreference, strSQL As String
Dim Weight As Double
 
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("tblCourses")
 
Weight = Nz(DLookup("[Weight]", "[ID_Preference_Weight]", "[ID] = Forms![ID_Weight_Preference]![Combo0]"), 0#)
 
DoCmd.SetWarnings False 'Turns off warning messages [if you want].
 
strSQL = "INSERT INTO TblDetails (Weight) "
strSQL = strSQL & "VALUES (" & Weight & "); "
 
DoCmd.RunSQL strSQL
 
DoCmd.SetWarnings True 'Turn Warning messages back on.
 
End Sub

Let me know if this helps.