Hi
I have a combobox that takes data from table 1 and using select where function filters the subform that takes data from table 2.
when i use this code in
Private Sub cmb_ClientName_AfterUpdate()
Dim sql As String
sql = "SELECT * FROM qry " _
& "WHERE qry.[CLIENT NAME] = '" & Forms!main!cmb_ClientName.Column(1) & "' " _
& "OR Forms!main!cmb_ClientName IS NULL " _
& "ORDER BY qry.[INVOICE DATE]asc"
Forms!main.subfrm.Form.RecordSource = sql
Forms!main.subfrm.Form.Requery
Sometimes the code works. sometimes, especially when the client names in table 1 cannot be found in table 2, error 2115 appears. sometimes when the combobox is cleared. ?name error appears in the subform and error 2115 occurs.
what i wanted is the subform to show no records.
so i thought to try this
Private Sub cmb_ClientName_AfterUpdate()
On Error GoTo Errbtn_Edit_Click
Dim sqlAll As String
Dim sql As String
Dim sqlClient As Integer
sqlClient = DCount("[CLIENT NAME]", "[qry]", "[CLIENT NAME] = '" & Forms!main!cmb_ClientName.Value & "'")
If Me.cmb_ClientName Is Null And sqlClient = 0 Then
'all records
sqlAll = "SELECT * FROM qry " _
& "ORDER BY qry.INVOICE DATE]asc"
Forms!main.subfrm.Form.RecordSource = sqlAll
Forms!main.subfrm.Form.Requery
ElseIf Me.cmb_ClientName Is Not Null And sqlClient = 0 Then
'no records
MsgBox "no records", vbOKOnly, "Msg"
ElseIf sqlClient > 0 Then
'filter records
sql = "SELECT * FROM qry " _
& "WHERE qry.[CLIENT NAME] = '" & Forms!main!cmb_ClientName.Column(1) & "' " _
& "OR Forms!main!cmb_ClientName IS NULL " _
& "ORDER BY qry.[INVOICE DATE]asc"
Forms!main.subfrm.Form.RecordSource = sql
Forms!main.subfrm.Form.Requery
End If
HandleExit:
Exit Sub
Errbtn_Edit_Click:
MsgBox Err.DESCRIPTION
Resume HandleExit
End Sub
"object required" error appears.
Where went wrong?
Please explain in more detail as im a begineer.
I have a combobox that takes data from table 1 and using select where function filters the subform that takes data from table 2.
when i use this code in
Private Sub cmb_ClientName_AfterUpdate()
Dim sql As String
sql = "SELECT * FROM qry " _
& "WHERE qry.[CLIENT NAME] = '" & Forms!main!cmb_ClientName.Column(1) & "' " _
& "OR Forms!main!cmb_ClientName IS NULL " _
& "ORDER BY qry.[INVOICE DATE]asc"
Forms!main.subfrm.Form.RecordSource = sql
Forms!main.subfrm.Form.Requery
Sometimes the code works. sometimes, especially when the client names in table 1 cannot be found in table 2, error 2115 appears. sometimes when the combobox is cleared. ?name error appears in the subform and error 2115 occurs.
what i wanted is the subform to show no records.
so i thought to try this
Private Sub cmb_ClientName_AfterUpdate()
On Error GoTo Errbtn_Edit_Click
Dim sqlAll As String
Dim sql As String
Dim sqlClient As Integer
sqlClient = DCount("[CLIENT NAME]", "[qry]", "[CLIENT NAME] = '" & Forms!main!cmb_ClientName.Value & "'")
If Me.cmb_ClientName Is Null And sqlClient = 0 Then
'all records
sqlAll = "SELECT * FROM qry " _
& "ORDER BY qry.INVOICE DATE]asc"
Forms!main.subfrm.Form.RecordSource = sqlAll
Forms!main.subfrm.Form.Requery
ElseIf Me.cmb_ClientName Is Not Null And sqlClient = 0 Then
'no records
MsgBox "no records", vbOKOnly, "Msg"
ElseIf sqlClient > 0 Then
'filter records
sql = "SELECT * FROM qry " _
& "WHERE qry.[CLIENT NAME] = '" & Forms!main!cmb_ClientName.Column(1) & "' " _
& "OR Forms!main!cmb_ClientName IS NULL " _
& "ORDER BY qry.[INVOICE DATE]asc"
Forms!main.subfrm.Form.RecordSource = sql
Forms!main.subfrm.Form.Requery
End If
HandleExit:
Exit Sub
Errbtn_Edit_Click:
MsgBox Err.DESCRIPTION
Resume HandleExit
End Sub
"object required" error appears.
Where went wrong?
Please explain in more detail as im a begineer.
Last edited: