problem with checkboxes in form

[manasm]

I have a form in MS Access and it has 4 checkboxes. Based on the chekcbox selected the code inserts new row into the table. The code will first check if the record corresponding to the checkbox exists or not and if it doesn't then will insert in the table.

I want to display the message on the message box for the particular record that exists. Right now I have that message on the temporary table but I can't combine 4 temporary tables if all the 4 records exist.

My code looks like this:

Option Compare Database
Private Sub Command11_Click()
Dim Msg As String
Dim Msg1 As String
Dim Value As Integer
Dim CheckBox As String
Dim Count As Integer
Dim InCount As Integer

If Check75.Value = True Then
Dim strSql As String
Dim selSql As String
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim dbs As Database
Dim qdf As QueryDef
Dim ControlVal As Integer
Set dbs = CurrentDb()
ControlVal = Forms!tbl_Study_form!Participant_ID
selSql = "SELECT * FROM tbl_RiskItem WHERE RiskItem_Name='Post Menopausal' AND Participant_ID= " & ControlVal
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdText
.CommandText = selSql
End With

Set rs = cmd.Execute
If rs.EOF Then
InCount = InCount + 1
Msg = "Do you want to insert?"
MsgBox Msg
strSql = "INSERT INTO tbl_RiskItem ( RiskCategory_ID, RiskItem_Name, Participant_ID )VALUES ('4', 'Post Menopausal', Forms.tbl_Study_form.Participant_ID);"
DoCmd.RunSQL strSql
Else
With dbs
Set qdf = .CreateQueryDef("tmpProductInfo", selSql)
DoCmd.OpenQuery "tmpProductInfo"
.QueryDefs.Delete "tmpProductInfo"
End With
dbs.Close
qdf.Close
Count = Count + 1
End If
End If

If Check77.Value = True Then
Dim selSql1 As String
Dim strSql1 As String
Dim rs1 As New ADODB.Recordset
Dim cmd1 As New ADODB.Command
Dim ControlVal1 As Integer
ControlVal1 = Forms!tbl_Study_form!Participant_ID
selSql1 = "SELECT * FROM tbl_RiskItem WHERE RiskItem_Name='Uterus Removed' AND Participant_ID= " & ControlVal1
With cmd1
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdText
.CommandText = selSql1
End With
Set rs1 = cmd1.Execute
If rs1.EOF Then
InCount = InCount + 1
Msg = "Do you want to insert"
MsgBox Msg
'strSql1 = "UPDATE tbl_RiskItem SET RiskCategory_ID='4', RiskItem_Name='Uterus Removed', Participant_ID= Forms.tbl_Study_form.Participant_ID;"
strSql1 = "INSERT INTO tbl_RiskItem ( RiskCategory_ID, RiskItem_Name, Participant_ID )VALUES ('4', 'Uterus Removed', Forms.tbl_Study_form.Participant_ID);"
DoCmd.RunSQL strSql1
Else
With dbs
Set qdf = .CreateQueryDef("tmpProductInfo", selSql1)
DoCmd.OpenQuery "tmpProductInfo"
.QueryDefs.Delete "tmpProductInfo"
End With
dbs.Close
qdf.Close
Count = Count + 1
End If
End If

If Check79.Value = True Then
Dim selSql2 As String
Dim strSql2 As String
Dim rs2 As New ADODB.Recordset
Dim cmd2 As New ADODB.Command
Dim ControlVal2 As Integer
Dim dbs2 As Database
Dim qdf2 As QueryDef
Set dbs2 = CurrentDb()
ControlVal2 = Forms!tbl_Study_form!Participant_ID
selSql2 = "SELECT * FROM tbl_RiskItem WHERE RiskItem_Name='Ovaries Removed' AND Participant_ID= " & ControlVal2
With cmd2
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdText
.CommandText = selSql2
End With
Set rs2 = cmd2.Execute
If rs2.EOF Then
InCount = InCount + 1
Msg = "Do you want to insert?"
MsgBox Msg
strSql2 = "INSERT INTO tbl_RiskItem ( RiskCategory_ID, RiskItem_Name, Participant_ID )VALUES ('4', 'Ovaries Removed', Forms.tbl_Study_form.Participant_ID);"
DoCmd.RunSQL strSql2
Else
With dbs2
Set qdf2 = .CreateQueryDef("tmpProductInfo2", selSql2)
DoCmd.OpenQuery "tmpProductInfo2"
.QueryDefs.Delete "tmpProductInfo2"
End With
dbs2.Close
qdf2.Close
Count = Count + 1
End If
End If

If Check81.Value = True Then
Dim selSql3 As String
Dim strSql3 As String
Dim rs3 As New ADODB.Recordset
Dim cmd3 As New ADODB.Command
Dim ControlVal3 As Integer
ControlVal3 = Forms!tbl_Study_form!Participant_ID
selSql3 = "SELECT * FROM tbl_RiskItem WHERE RiskItem_Name='Ovaries Removed' AND Participant_ID= " & ControlVal3
With cmd3
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdText
.CommandText = selSql3
End With
Set rs3 = cmd3.Execute
If rs3.EOF Then
InCount = InCount + 1
Msg = "Do you want to insert?"
MsgBox Msg
strSql3 = "INSERT INTO tbl_RiskItem ( RiskCategory_ID, RiskItem_Name, Participant_ID )VALUES ('4', 'Harmone Replacement', Forms.tbl_Study_form.Participant_ID);"
DoCmd.RunSQL strSql3
Else
With dbs
Set qdf = .CreateQueryDef("tmpProductInfo", selSql3)
DoCmd.OpenQuery "tmpProductInfo"
.QueryDefs.Delete "tmpProductInfo"
End With
dbs.Close
qdf.Close
Count = Count + 1
End If
End If

If Count >= 1 Then
Msg = "Record exists"
MsgBox Msg
End If

End Sub


Your help is appreciated.
Thanks in anticipation.
Manas

[OBP]

I know this is Not answering your question, but can I ask why you are going to so much trouble to add a record to a table?
When you say that you can't combine 4 temporary tables, I am not sure what you mean. You can combine the data from 4 temorary tables using string variables for display purposes.
Is that what you want to do?

[manasm]

Hi OBP,
Thanks for your reply.

[manasm]

I know this is Not answering your question, but can I ask why you are going to so much trouble to add a record to a table?
When you say that you can't combine 4 temporary tables, I am not sure what you mean. You can combine the data from 4 temorary tables using string variables for display purposes.
Is that what you want to do?
Hi OBP,
Thanks for your reply. Yeah that is what I want to do. I want to combine the results in 4 temporary tables for display purpose. You said I can use string variables to do that. Can you please give me an example.

I have been scratching my head to get the solution to this problem.

Thanks for all your help.

[OBP]

Well if you want to combine the data from 4 tables you can use a String variable and just use
Dim data as string
data = tabelone data
data = data & tabeltwo data
data = data & tabelthree data
data = data & tabelfour data

Where tableone data etc is the contents of the Fields that you wish to combine