Solved combo box after update event

[quest]

Hi,
I have form with few combo boxes. afterupdate event were with macros so i decided to change them with code and add message if record is not found. i fix all except the one for filtering records by year. macro was like this ="[Godina] = " & "'" & [Screen]. [ActiveControl] & "'" and worked. i added this code but now dont work show only current year (2025) not other years
Private Sub cboYear_AfterUpdate()
Dim yearVal As Variant
yearVal = Nz(Me.cboYear.Value, "")

If yearVal = "" Then
Me.FilterOn = False
Exit Sub
End If

' Use Year() directly on the real field, not on alias
Me.Filter = "Year([DateOfReceiving]) = " & yearVal
Me.FilterOn = True

' Wait 100 ms to let filter apply before checking record count
DoEvents

If Me.Recordset.RecordCount = 0 Then
MsgBox "No records found for year " & yearVal, vbInformation, "Filter"
Me.FilterOn = False
Me.cboYear = Null
DoCmd.GoToControl "cboYear"
End If
End Sub

 

[CJ_London]

Step through your code to check what you actually have

Small issue- the year function returns a number but you are treating yearval as text. Doubt it makes a difference but you should keep to the appropriate data types

And try setting filter=“” to clear it

 

[Ken Sheridan]

Rather than examining the form's Recordset property you could examine any NOT NULL column for NULL:

    Dim yearVal AS Variant
    yearVal = Me.cboYear

    If IsNull(yearVal) Then
        MeFilterOn = False
    Else
        Me.Filter = "Year(DateOfReceiving) = " & yearVal
        Me.FilterOn = True

        If IsNull(NameOfAnyNotNullColumn GoesHere) Then
            MsgBox "No records found for year " & yearVal, vbInformation, "Filter"
            Me.FilterOn = False
            Me.cboYear = Null
            Me.cboYear.SetFocus
        End If
    End If

 

[quest]

Step through your code to check what you actually have

Small issue- the year function returns a number but you are treating yearval as text. Doubt it makes a difference but you should keep to the appropriate data types

And try setting filter=“” to clear it

i tried treating it as number, text, date same result. not working

 

[quest]

Rather than examining the form's Recordset property you could examine any NOT NULL column for NULL:

    Dim yearVal AS Variant
    yearVal = Me.cboYear

    If IsNull(yearVal) Then
        MeFilterOn = False
    Else
        Me.Filter = "Year(DateOfReceiving) = " & yearVal
        Me.FilterOn = True

        If IsNull(NameOfAnyNotNullColumn GoesHere) Then
            MsgBox "No records found for year " & yearVal, vbInformation, "Filter"
            Me.FilterOn = False
            Me.cboYear = Null
            Me.cboYear.SetFocus
        End If
    End If

there is no null value all date fields are populated

 

[Ken Sheridan]

there is no null value all date fields are populated

You miss the point. When the form is filtered so that no rows are returned all fields will be Null in the empty form provided it has no DefaultValue. Therefore if a field has been constrained as NOT NULL, i.e. its Required property is True (Yes), the field's being Null will mean no row has been found as a result of the search.

 

[The_Doc_Man]

This is a side note. When dealing with numbers and text in expressions, VBA has this thing called "LET coercion" that allows you to assign a text-string to a number value as long as the text string is a digit string and the number so represented wouldn't overflow the variable being assigned.

In case any of the new folks is wondering, "LET coercion" = coercing the data type of an expression in a "LET" context. It stems from the 1960s version of BASIC in which assigning a value to a variable involved a LET statement, as "LET A=10"

 

[quest]

You miss the point. When the form is filtered so that no rows are returned all fields will be Null in the empty form provided it has no DefaultValue. Therefore if a field has been constrained as NOT NULL, i.e. its Required property is True (Yes), the field's being Null will mean no row has been found as a result of the search.

so to try your code what should i put in NameOfAnyNotNullColumn not sure which name is this

 

[quest]

here what i found. i deleted default value in data tab on the combo box and now code works. filtering is ok. will try to put default value on form loading. i guess this can be considered solved.

 

[June7]

Tried to replicate issue.
My code works just fine with DefaultValue set in design.
Could provide your db for analysis.

 

[quest]

Tried to replicate issue.
My code works just fine with DefaultValue set in design.
Could provide your db for analysis.

But i don't have that version anymore i rewrite it and defaultvalue on the combo box worked when was with macro when i added code then all mixed.

 

[quest]

But i don't have that version anymore i rewrite it and defaultvalue on the combo box worked when was with macro when i added code then all mixed.

here i recreated it with macro.

 

[June7]

Would want version with VBA. I could set DefaultValue for testing.
However, language barrier makes review difficult.

 

[quest]

Would want version with VBA. I could set DefaultValue for testing.
However, language barrier makes review difficult.

here is with code and i know if i start from beginning I would do it different.

 

[June7]

I tried but language is an issue. Errors just trying to open form.
Here's what line of code looks like for me which is definitely not what you posted:
Me.Filter = "Year([ÄàòóìÍàÏðèåìîò]) = " & yearVal

 

[quest]

I tried but language is an issue. Errors just trying to open form.
Here's what line of code looks like for me which is definitely not what you posted:
Me.Filter = "Year([ÄàòóìÍàÏðèåìîò]) = " & yearVal

ok i will try tomorrow to translate it to english only for that field to make it work

 

[quest]

here it was faster than i thought

 

[June7]

Opening form (or query object even when form is open) triggers popup input prompt for each combobox.
Selecting in year combobox triggers same popups first time, next time triggers "problem communicating with OLE server or ActiveX control" error.
Probably all related to language issue. Sorry, cannot pursue.

I don't use dynamic parameterized queries. I prefer VBA to build filter criteria and apply to form (or report). Review http://allenbrowne.com/ser-62.html