Hi,
I'm a new Access VBA programmer and also a new member to this forum, hoping you can help me resolve a similar issue in this thread.
I have a main form (frmAll) that have a combo box where I select a value and I want the subform (frmSummary) to display the recordset that meet the criteria of the value that I selected in the combo box.
The MsgBox shows I have records in my RecordSource but the form doesn't show any records after I select the value from the combox (cmbId).
Please let me know what I'm missing in my codes and thank you so much for your help!
My code is below:
Public Sub cmbId_AfterUpdate()
On Error GoTo Error:
Dim tmpId As String
Dim strSql_Sum As String
Dim lngBlack As Long
lngBlack = RGB(0, 0, 0)
Me.cmbId.ForeColor = lngBlack
tmpId = [Forms]![frmAll]![cmbId].Column(2)
Me.txtId = [Forms]![frmAll]![cmbId].Column(2)
Me.txtLOB = [Forms]![frmAll]![cmbId].Column(1)
If tmpId = "ALL" Then
strSql_Sum = "SELECT tbl_Incentive_summary.* " & _
"FROM tbl_Incentive_summary INNER JOIN tbl_Incentive_product ON tbl_Incentive_summary.Incentive_id = tbl_Incentive_product.Incentive_id " & _
"WHERE (((tbl_Incentive_product.Incentive_sort) Is Not Null)) " & _
"ORDER BY tbl_Incentive_product.Incentive_sort;"
ElseIf tmpId = "COMM" Or tmpId = "MCAL" Or tmpId = "MDCR" Then
strSql_Sum = "SELECT tbl_Incentive_summary.* " & _
"FROM tbl_Incentive_summary INNER JOIN tbl_Incentive_product ON tbl_Incentive_summary.Incentive_id = tbl_Incentive_product.Incentive_id " & _
"WHERE (((tbl_Incentive_product.Incentive_sort) Is Not Null) AND ((tbl_Incentive_product.Incentive_LOB)= '" & tmpId & "')) " & _
"ORDER BY tbl_Incentive_product.Incentive_sort;"
Else
strSql_Sum = "SELECT * FROM tbl_Incentive_summary " & _
"WHERE ((Incentive_id)= '" & tmpId & "');"
End If
Forms!frmAll!frmSummary.Form.RecordSource = strSql_Sum
Me.frmSummary.Requery
Me.Requery
Me.Refresh
MsgBox "This form gets its records from " & Forms!frmAll!frmSummary.Form.RecordSource & "."
Exit_cmbId_AfterUpdate:
Exit Sub
Error:
MsgBox Err.Number & " - " & Err.Description, vbCritical, "Error!"
Resume Exit_cmbId_AfterUpdate
End Sub
I'm a new Access VBA programmer and also a new member to this forum, hoping you can help me resolve a similar issue in this thread.
I have a main form (frmAll) that have a combo box where I select a value and I want the subform (frmSummary) to display the recordset that meet the criteria of the value that I selected in the combo box.
The MsgBox shows I have records in my RecordSource but the form doesn't show any records after I select the value from the combox (cmbId).
Please let me know what I'm missing in my codes and thank you so much for your help!
My code is below:
Public Sub cmbId_AfterUpdate()
On Error GoTo Error:
Dim tmpId As String
Dim strSql_Sum As String
Dim lngBlack As Long
lngBlack = RGB(0, 0, 0)
Me.cmbId.ForeColor = lngBlack
tmpId = [Forms]![frmAll]![cmbId].Column(2)
Me.txtId = [Forms]![frmAll]![cmbId].Column(2)
Me.txtLOB = [Forms]![frmAll]![cmbId].Column(1)
If tmpId = "ALL" Then
strSql_Sum = "SELECT tbl_Incentive_summary.* " & _
"FROM tbl_Incentive_summary INNER JOIN tbl_Incentive_product ON tbl_Incentive_summary.Incentive_id = tbl_Incentive_product.Incentive_id " & _
"WHERE (((tbl_Incentive_product.Incentive_sort) Is Not Null)) " & _
"ORDER BY tbl_Incentive_product.Incentive_sort;"
ElseIf tmpId = "COMM" Or tmpId = "MCAL" Or tmpId = "MDCR" Then
strSql_Sum = "SELECT tbl_Incentive_summary.* " & _
"FROM tbl_Incentive_summary INNER JOIN tbl_Incentive_product ON tbl_Incentive_summary.Incentive_id = tbl_Incentive_product.Incentive_id " & _
"WHERE (((tbl_Incentive_product.Incentive_sort) Is Not Null) AND ((tbl_Incentive_product.Incentive_LOB)= '" & tmpId & "')) " & _
"ORDER BY tbl_Incentive_product.Incentive_sort;"
Else
strSql_Sum = "SELECT * FROM tbl_Incentive_summary " & _
"WHERE ((Incentive_id)= '" & tmpId & "');"
End If
Forms!frmAll!frmSummary.Form.RecordSource = strSql_Sum
Me.frmSummary.Requery
Me.Requery
Me.Refresh
MsgBox "This form gets its records from " & Forms!frmAll!frmSummary.Form.RecordSource & "."
Exit_cmbId_AfterUpdate:
Exit Sub
Error:
MsgBox Err.Number & " - " & Err.Description, vbCritical, "Error!"
Resume Exit_cmbId_AfterUpdate
End Sub