Error 2115 when using combobox to filter subform (1 Viewer)

[acs]

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.

 

[arnelgp]

Is Null can only be used in Query, use IsNull() function.

on your Main Form, add an Unbound textbox (txtUnbound).
set the visible property to No.

add code to cmb_ClientName AfterUpdate Event:

private sub cmb_ClientName_AfterUpdate()
If Trim(Me.cmb_ClientName & "") <> "" Then
Me.txtUnbound = Me.cmb_ClientName.Column(1)
Else
Me.txtUnbound = Null
End If
Forms!main.subfrm.Form.RecordSource = "NewQueryName"
end sub

create a New query against your table.

on [Client Name] field, add Criteria:

=NZ([Forms]![main]![txtUndbound], [Client Name])

 

[acs]

Why need to create a unbound textbox with the same values that will appear in the combobox?
what is the recordsource for?
is it to create another query (qry 2) with the same records as the one i have in the original query (qry 1), just that now there is a criteria in qry 2 with =NZ([Forms]![main]![txtUndbound], [Client Name]) in criteria row?
if so, wouldnt i have extra duplicating data in the same file?

 

[arnelgp]

that is a Query my friend, nothing is duplicated data.
if you like use your original query instead.

 

[theDBguy]

Hi @acs. Welcome to AWF!

Just curious, what is error 2115 and which line is highlighted when you get it?

 

[acs]

i dont understand, pls explain in more detail ur reasoning behind these codes. actually why did the error comes up? i'm using afterupdate not beforeupdate. then should i change the subform source to qry 2 also?

 

[acs]

Hi @acs. Welcome to AWF!

Just curious, what is error 2115 and which line is highlighted when you get it?

hi!
this is the error
"The macro or function set to the beforeupdate or ValidationRule property for this field is preventing the DB from saving the data in the field. "
it highlights this line
Forms!main.subfrm.Form.RecordSource = sql

 

[Rene vK]

Is the form in your subForm, Bound or Unbound?

try:

Me!subfrm.Form.RecordSource = sql
Me!subfrm.Form.Requery

In my apps i usually do something like this ("Uren_perWeek" is a bound Form)

Sub kzeProjecten_AfterUpdate()
Dim SQLweek As String
Dim SQLstring As String

SQLstring = "ProjectNr = '" & kzeProjecten.value & "'"

With Me![SubUurInfo]
  If .SourceObject <> "Uren_perWeek" Then
    .Visible = False
    .SourceObject = "Uren_perWeek"
    .Form.Filter = SQLstring
    .Form.FilterOn = True
    .Visible = True
  Else
    .Form.Filter = SQLstring
    .Form.Requery
    .Visible = True
  End If
End With
End Sub