You Can't Assign A Value To This Object (1 Viewer)

forms_are_nightmares · Oct 14, 2011

Hello All,

I know exactly where the code is failing but for some reason, I can't figure out the code.

Scenario:
I have filters on a form that will allow a user to find specific records. All filters work with the exception of the combo boxes that specify a date range. All combo boxes are unbound. The code is below. If someone can help me, I'd appreciate it. The bold code is what is failing.

Private Sub Filter1_Click()
Dim sWhere As String
Dim strDateField As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"
Me.Filter = ""
Me.FilterOn = False
If Not IsNull(Me.ACmbo) Then
sWhere = sWhere & "[AName]='" & Me.ACmbo & "' And "
End If

If Not IsNull(Me.StoreCmbo) Then
sWhere = sWhere & "[Store]='" & Me.StoreCmbo & "' And "
End If
If Not IsNull(Me.StateCmbo) Then
sWhere = sWhere & "[State]='" & Me.StateCmbo & "' And "
End If
If Not IsNull(Me.SCmbo) Then
sWhere = sWhere & "[Pspecialist]='" & Me.SCmbo & "' And "
End If

If Not IsNull(Me.CMCmbo) Then
sWhere = sWhere & "[cmgr]='" & Me.CMCmbo & "' And "
End If

If Not IsNull(Me.CityCmbo) Then
sWhere = sWhere & "[City]='" & Me.CityCmbo & "' And "
End If
If IsDate(Me.txtStartDate) Then
sWhere = sWhere & "[Adate] = '" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & " And "
End If

If IsDate(Me.txtEndDate) Then
If sWhere <> vbNullString Then
sWhere = sWhere
End If
sWhere = sWhere & "[Adate] = (" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ") And "
End If

If Right(sWhere, 4) = "And " Then
sWhere = Left(sWhere, Len(sWhere) - 5)
End If

Me.Filter = sWhere
Me.FilterOn = True

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No Matches Found Based on Criteria Entered!!"
Me.FilterOn = False
End If

If sWhere = "" Then
MsgBox "No Criteria Has Been Entered!!"

End If
End Sub

jdraw · Oct 14, 2011

IsDate(Me.txtStartDate)

You should probably convert the String to Date using
IsDate(CDate(Me.txtStartDate))
and see if that changes anything.

forms_are_nightmares · Oct 17, 2011

Thanks for the reply. However it didn't work. I still get the error. When I debug, the code fails on the Me.Filter = sWhere part of the code.

vbaInet · Oct 17, 2011

Are any of your controls called Filter?

forms_are_nightmares · Oct 17, 2011

No, I just verified. Somehow the code is trying to assign a value rather than filter the value. I keep playing with the code but for some reason can't get it right.

gemma-the-husky · Oct 17, 2011

i suspect that the problem might be the construction of the "swhere" string.

with dates, you need to wrap the data value in # characters, not " characters

out of interest, how do you fill the date comob boxes, and what datatype is ADATE?

vbaInet · Oct 17, 2011

Are you sure that's where the debug button highlighted?

Before Me.Filter = strWhere, put this:

Me.Filter = vbNullString

forms_are_nightmares · Oct 17, 2011

vbaInet: Yes, when I go to the code when the error occurs, the Me.filter = strWhere is highlighted. I inserted the vbNullString, nothing happens with the filter.

gemma: Adate is date/time, short format. Users enter the date via a calendar box that they click and select the date. I will work on the # vs. "

forms_are_nightmares · Oct 17, 2011

When I move my cursor over the yellow highlighted Me.filter = strWhere, this is what it reads...see attached

vbaInet · Oct 17, 2011

You are wrongly concatenating the date parts. I've rewritten your entire routine:

Code:

Private Sub Filter1_Click()
    Dim sWhere As String
    Const strcJetDate = "\#mm\/dd\/yyyy\#"
    
    Me.Filter = ""
    Me.FilterOn = False
    
    If Len(Nz(Me.ACmbo, vbNullString)) Then
        sWhere = "[AName] = '" & Me.ACmbo & "'"
    End If
    
    If Len(Nz(Me.StoreCmbo, vbNullString)) Then
        sWhere = IIf(Len(sWhere) <> 0, sWhere & " AND ", vbNullString) & "[Store] = '" & Me.StoreCmbo & "'"
    End If
    
    If Len(Nz(Me.StateCmbo, vbNullString)) Then
        sWhere = IIf(Len(sWhere) <> 0, sWhere & " AND ", vbNullString) & "[State] = '" & Me.StateCmbo & "'"
    End If
    
    If Len(Nz(Me.SCmbo, vbNullString)) Then
        sWhere = IIf(Len(sWhere) <> 0, sWhere & " AND ", vbNullString) & "[Pspecialist] = '" & Me.SCmbo & "'"
    End If
    
    If Len(Nz(Me.CMCmbo, vbNullString)) Then
        sWhere = IIf(Len(sWhere) <> 0, sWhere & " AND ", vbNullString) & "[cmgr] = '" & Me.CMCmbo & "'"
    End If
    
    If Len(Nz(Me.CityCmbo, vbNullString)) Then
        sWhere = IIf(Len(sWhere) <> 0, sWhere & " AND ", vbNullString) & "[City] = '" & Me.CityCmbo & "'"
    End If
    
    If IsDate(Me.txtStartDate) Then
        sWhere = IIf(Len(sWhere) <> 0, sWhere & " AND ", vbNullString) & "[Adate] >= " & Format(Me.txtStartDate, strcJetDate)
    End If
    
    If IsDate(Me.txtEndDate) Then
        sWhere = IIf(Len(sWhere) <> 0, sWhere & " AND ", vbNullString) & "[Adate] < " & Format(Me.txtEndDate + 1, strcJetDate)
    End If
    
    Me.Filter = sWhere
    Me.FilterOn = True
    
    If Me.RecordsetClone.RecordCount = 0 Then
        MsgBox "No Matches Found Based on Criteria Entered!!"
        Me.FilterOn = False
    End If
    
    If Len(sWhere) = 0 Then
        MsgBox "No Criteria Has Been Entered!!"
    End If
    
End Sub

forms_are_nightmares · Oct 17, 2011

Thank YOU very very much. That did the trick. I really appreciate your help and patience.

vbaInet · Oct 17, 2011

You're welcome!

vbaInet · Oct 17, 2011

Oops... small problem with that code. In all the If Len(Nz()) lines, I forgot to include something. They should all be:

If Len(Nz()) <> 0 Then

Add the red bit.

forms_are_nightmares · Oct 17, 2011

Will do. Thanks again.

vbaInet · Oct 17, 2011

No problemo!

KiEESH · Feb 14, 2013

Hi!

Trying to do the same thing.

I tried this and got "You Can't Assign A Value To This Object."

My public function works fine. The form displays the username.

I added the priavate sub on my form's BeforeUpdate event, and that's where the issue lies.

Table Field name: LAST_EDITED_BY
Form Text Box Control name: LAST EDITED BY (no underscore)

Please help!

Code:

Option Compare Database
 
Public Function GetUserName() As String
 
GetUserName = Environ("UserName")
 
End Function
 
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
   
   Me.LAST_EDITED_BY = GetUserName()
 
End Sub

JHB · Feb 14, 2013

Me.LAST_EDITED_BY = GetUserName()

Are you are trying to set the value for the text control in the form or for in the table?
If table try:

Code:

Me!LAST_EDITED_BY = GetUserName()

KiEESH · Feb 14, 2013

Both.
I need the username to be auto-generated on the form and also added to the table.

Played around with it a bit and found that this syntax worked:

Code:

Me![LAST_EDITED_BY] = GetUserName()

But, I have another issue now...

I need to do exactly the same thing in a different form for a different field name [ADDED_BY].

I repeated the same exact steps, only replacing the field name, and get #Name? error.

Any ideas why?

KiEESH · Feb 14, 2013

I set the textbox control equal to:

= GetUserName ()

Code:

Public Function GetUserName() As String
 
GetUserName = Environ("UserName")
 
End Function
 
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
 
Me![ADDED_BY] = GetUserName()
 
End Sub

JHB · Feb 14, 2013

KiEESH said:
Both.

I repeated the same exact steps, only replacing the field name, and get #Name? error.

Any ideas why?

The control- or field-name is unknown.

You Can't Assign A Value To This Object (1 Viewer)

Registered User.

Super Moderator

Registered User.

AWF VIP

Registered User.

Super Moderator

AWF VIP

Registered User.

Registered User.

Attachments

AWF VIP

Registered User.

AWF VIP

AWF VIP

Registered User.

AWF VIP

Registered User.

Have been here a while

Registered User.

Registered User.

Have been here a while

Similar threads

Users who are viewing this thread