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

acs

New member
Local time
Tomorrow, 07:44
Joined
May 18, 2020
Messages
4
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.
 
Last edited:

arnelgp

error reading drive A:
Local time
Tomorrow, 07:44
Joined
May 7, 2009
Messages
10,834
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

New member
Local time
Tomorrow, 07:44
Joined
May 18, 2020
Messages
4
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

error reading drive A:
Local time
Tomorrow, 07:44
Joined
May 7, 2009
Messages
10,834
that is a Query my friend, nothing is duplicated data.
if you like use your original query instead.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:44
Joined
Oct 29, 2018
Messages
13,132
Hi @acs. Welcome to AWF!

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

acs

New member
Local time
Tomorrow, 07:44
Joined
May 18, 2020
Messages
4
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

New member
Local time
Tomorrow, 07:44
Joined
May 18, 2020
Messages
4
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

Member
Local time
Tomorrow, 00:44
Joined
Mar 3, 2013
Messages
46
Is the form in your subForm, Bound or Unbound?

try:
Code:
Me!subfrm.Form.RecordSource = sql
Me!subfrm.Form.Requery

In my apps i usually do something like this ("Uren_perWeek" is a bound Form)
Code:
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
 

Users who are viewing this thread

Top Bottom