Run-time error '2448': You can't assign a value to this object.

[keirnus]

I actually researched this in this forum and found lots of them
but their cases are kinda complicated. So, I decided to post this.

Here's my case:

Private Sub cmdSortStartDate_Click()
 
    Dim frm As Access.Form
 
    Dim sSortOrder As String
 
    If cboSortBy.Value = "Descending" Then _
        sSortOrder = "DESC"
 
    Set frm = subfrm_L5OrigPlan.Form
    With frm
        .OrderBy = cboDateToSort.Value & " " & sSortOrder '<<< ERROR
        .OrderByOn = True
    End With
 
End Sub

You may check an image of it here:

The cboDateToSort is a combobox in a SubForm.
It has a ControlSource based from the RecordSource of the SubForm.
The DB data are connected via ODBC-DAO.
The data are retrieved from DB and properly displayed in a Datasheet view.
But when I'm about to sort the displayed data, this error 2448 occurred.

The OrderBy and OrderByOn properties of the Datasheet SubForm supposed to be related only to the data displayed/viewed, right?
Can anyone tell me the reason why I am having this error?

 

[ajetrumpet]

is the object in use when the code reaches that section? don't see anything wrong with the assignment or dec.

 

[keirnus]

is the object in use when the code reaches that section? don't see anything wrong with the assignment or dec.

Thanks for your reply ajetrumpet.

I would like to clarify what you mean about "the object is in use."

At 1st, the Date field is blank.
A button is used to select a date and set it to Date field.
After setting, the Date data is displayed in the Form (Datasheet view).
In the Datasheet view, there are lots of data.
Let's say there are Item01 with Date01, Item02 with Date02, etc.
The Date data here (Date01, Date02, ...,DateXX) are to be sorted using the above code.
When the cmdSortStartDate Combobox is clicked, it should sort the Date data
(Date01, Date02, ...,DateXX) displayed in Datasheet-view Form.
But got this error instead.

When can the object is still in use?

 

[speakers_86]

I suspect this is not the problem, but there is no End If.

 

[keirnus]

I suspect this is not the problem, but there is no End If.

I am using a single-line syntax for the If Statement.

If cboSortBy.Value = "Descending" Then _
        sSortOrder = "DESC"

The underscore ( _ ) is a line continuation.
So, that line can be done like this:

If cboSortBy.Value = "Descending" Then sSortOrder = "DESC"

Both are the same.
Another syntax (which you are most familiar of) is this:

If cboSortBy.Value = "Descending" Then 
        sSortOrder = "DESC"
End If

which eats up 1-line of code.

So, this line has no problem.

 

[Galaxiom]

I am using a single-line syntax for the If Statement.

Don't think so. Single line syntax has a colon after the Then.

If cboSortBy.Value = "Descending" Then: _
sSortOrder = "DESC"

Without the colon, all the following lines are part of the true until you hit an End If.

Edit: Oh well perhaps there goes another long held myth.

However I would recommend not doing one line If statements with continuation as it could be easily missed as Speakers86 did.
You should consider that others may need to follow your code.

Besides the saving of one line is a trival gain since lines are not actually "eaten up" anyway.

 

[DCrake]

I think the problem is that by using the With ... End With syntax you need to change the way in which you are addressing the sort order of the form. Also you do not need the .Value property.

If you think about it what you are saying is

With Frm
   .OrderBy = DateField DESC
   .OrderByOn = True
End With

Access is not recognising that CboDateToSort is the actual name of a field in the RecordSource.

If You were hard coding it it would look like

With Frm
    .OrderBy = .DateField DESC
    .OrderOnBy = True
End With

That's how I see it anyway.

David

 

[keirnus]

With Frm
    .OrderBy = .DateField DESC
    .OrderOnBy = True
End With

David

Thanks for replying DCrake.

I did what you said and got a different error.

Run-tim error '2465'
Application-defined or object-defined error

I tried the following and still got the Run-time error '2448':
[1]

    Set frm = Me.Form![subfrm_L5OrigPlan].Form
 
    With frm
        .OrderBy = cboDateToSort.Value & " " & sSortOrder '<<< ERROR
        .OrderByOn = True
    End With

[2]

    subfrm_L5OrigPlan.Form.OrderBy = cboDateToSort.Value & " " & sSortOrder '<<< ERROR    
    subfrm_L5OrigPlan.Form.OrderByOn = True

[3]

Me.Form![subfrm_L5OrigPlan].Form.OrderBy = cboDateToSort.Value & " " & sSortOrder '<<< ERROR

I don't understand why it worked before I implemented the ODBC.
I just did the same. This SubForm got the same RecordSource query.

Are there any reasons why this error occurred?
(Run-time error '2448': You can't assign a value to this object.)

What is the counter measure on this error?

 

[Galaxiom]

Private Sub cmdSortStartDate_Click()
 
   [B] Dim frm As Access.Form  [COLOR=blue]'....1[/COLOR][/B]
 
    Dim sSortOrder As String
 
    If cboSortBy.Value = "Descending" Then _
        sSortOrder = "DESC"
 
    [B]Set frm = subfrm_L5OrigPlan.Form   [COLOR=blue]'....1[/COLOR][/B]
    With frm
        .OrderBy = [B]cboDateToSort[/B].Value & " " & sSortOrder [COLOR=blue][B]'.....2, 3[/B][/COLOR]
        .OrderByOn = True
    End With
 
End Sub

The cboDateToSort is a combobox in a SubForm. '.....2
It has a ControlSource based from the RecordSource of the SubForm. ....3

The OrderBy and OrderByOn properties of the Datasheet SubForm supposed to be related only to the data displayed/viewed ....4

I'm assuming the sort button and its code are on the Main form's module. You need to use unambiguous referencing syntax to address objects.

1. You could be (and probably are) referring to the subform object rather than its instance as the source object of a subform control on the main form.
You should be using:
Me!subformcontrolname.Form

IMHO, assigning the form to a variable is pointless obfuscation.

2. You are not fully addressing cboDateToSort:
Use:
Me!subformcontrolname.Form!comboboxname

Without including Me or a reference from the database level Access has to guess what you are referring to.

3. cboDateToSort would need to hold the names of the fields to be sorted but it appears to be a control bound to a field containing actual dates.

4. OrderBy sorts the recordset defined by the form's RecordSource query/table. It must be defined using the field names rather than the control names if they are different.