Hello again,
I could do with a little leg up on this one. I've read a couple of threads but none seems to sort out this specific issue.
I've got 3 choices on the form (all for same field, but trawling through same table up to 3 times). Based on the 3 options (from 1 to 3 choices allowed), I then create the SQL for a query, which I want to then feed into the report. I've got to the stage where the SQL seems to be correct, but how do I now link it to a report?
I'm struggling with DoCmd.OpenReport or RunSQL.... any hints.
Thanks
Train2
So far:
Private Sub OK_Click()
Dim Msg, strSQL, strSelect, strFrom, strWhere As String
Dim Repsonse As Variant
'set variables up for most basic...
strSQL = ""
strSelect = "SELECT testtest.*"
strFrom = " FROM "
strWhere = " WHERE "
'first check to ensure an input
If (IsNull(cboProgress)) Then
Msg = MsgBox("You must enter something in the first box", vbOKOnly)
Response = Msg
strFrom = ""
strWhere = ""
strSelect = ""
Else
'combo box 0
If Not (IsNull(cboProgress)) Then
If Len(strSelect) > 0 Then
strSelect = strSelect & ", T_Progress.Grade"
Else
strSelect = strSelect & ""
End If
If Len(strFrom) > 0 Then
strFrom = strFrom & "testtest INNER JOIN T_Progress ON (testtest.C_Id = T_Progress.C_Id) AND (testtest.P_Id = T_Progress.P_Id) "
Else
strFrom = strFrom & ""
End If
If Len(strWhere) > 0 Then
strWhere = strWhere & " (T_Progress.R_No = me![cboProgress]) "
Else
strWhere = strWhere & ""
End If
End If
'combo box 1
If Not (IsNull(cboProgress1)) Then
If Len(strSelect) > 0 Then
strSelect = strSelect & ", T_Progress_1.Grade"
Else
strSelect = strSelect & ""
End If
If Len(strFrom) > 0 Then
strFrom = strFrom & " INNER JOIN T_Progress_1 ON (testtest.C_Id = T_Progress_1.C_Id) AND (testtest.P_Id = T_Progress_1.P_Id) "
Else
strFrom = strFrom & ""
End If
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND (T_Progress_1.R_No = me![cboProgress1]) "
Else
strWhere = strWhere & ""
End If
End If
'combo box 2
If Not (IsNull(cboProgress2)) Then
If Len(strSelect) > 0 Then
strSelect = strSelect & ", T_Progress_2.Grade"
Else
strSelect = strSelect & ""
End If
If Len(strFrom) > 0 Then
strFrom = strFrom & " INNER JOIN T_Progress_2 ON (testtest.C_Id = T_Progress_2.C_Id) AND (testtest.P_Id = T_Progress_2.P_Id)"
Else
strFrom = strFrom & ""
End If
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND (T_Progress_2.R_No = me![cboProgress2]) "
Else
strWhere = strWhere & ""
End If
End If
strSQL = strSelect & strFrom & strWhere
Response = MsgBox(strSQL, vbOKOnly)
End If
End Sub
I could do with a little leg up on this one. I've read a couple of threads but none seems to sort out this specific issue.
I've got 3 choices on the form (all for same field, but trawling through same table up to 3 times). Based on the 3 options (from 1 to 3 choices allowed), I then create the SQL for a query, which I want to then feed into the report. I've got to the stage where the SQL seems to be correct, but how do I now link it to a report?
I'm struggling with DoCmd.OpenReport or RunSQL.... any hints.
Thanks
Train2
So far:
Private Sub OK_Click()
Dim Msg, strSQL, strSelect, strFrom, strWhere As String
Dim Repsonse As Variant
'set variables up for most basic...
strSQL = ""
strSelect = "SELECT testtest.*"
strFrom = " FROM "
strWhere = " WHERE "
'first check to ensure an input
If (IsNull(cboProgress)) Then
Msg = MsgBox("You must enter something in the first box", vbOKOnly)
Response = Msg
strFrom = ""
strWhere = ""
strSelect = ""
Else
'combo box 0
If Not (IsNull(cboProgress)) Then
If Len(strSelect) > 0 Then
strSelect = strSelect & ", T_Progress.Grade"
Else
strSelect = strSelect & ""
End If
If Len(strFrom) > 0 Then
strFrom = strFrom & "testtest INNER JOIN T_Progress ON (testtest.C_Id = T_Progress.C_Id) AND (testtest.P_Id = T_Progress.P_Id) "
Else
strFrom = strFrom & ""
End If
If Len(strWhere) > 0 Then
strWhere = strWhere & " (T_Progress.R_No = me![cboProgress]) "
Else
strWhere = strWhere & ""
End If
End If
'combo box 1
If Not (IsNull(cboProgress1)) Then
If Len(strSelect) > 0 Then
strSelect = strSelect & ", T_Progress_1.Grade"
Else
strSelect = strSelect & ""
End If
If Len(strFrom) > 0 Then
strFrom = strFrom & " INNER JOIN T_Progress_1 ON (testtest.C_Id = T_Progress_1.C_Id) AND (testtest.P_Id = T_Progress_1.P_Id) "
Else
strFrom = strFrom & ""
End If
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND (T_Progress_1.R_No = me![cboProgress1]) "
Else
strWhere = strWhere & ""
End If
End If
'combo box 2
If Not (IsNull(cboProgress2)) Then
If Len(strSelect) > 0 Then
strSelect = strSelect & ", T_Progress_2.Grade"
Else
strSelect = strSelect & ""
End If
If Len(strFrom) > 0 Then
strFrom = strFrom & " INNER JOIN T_Progress_2 ON (testtest.C_Id = T_Progress_2.C_Id) AND (testtest.P_Id = T_Progress_2.P_Id)"
Else
strFrom = strFrom & ""
End If
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND (T_Progress_2.R_No = me![cboProgress2]) "
Else
strWhere = strWhere & ""
End If
End If
strSQL = strSelect & strFrom & strWhere
Response = MsgBox(strSQL, vbOKOnly)
End If
End Sub