You Can't Assign A Value To This Object

[forms_are_nightmares]

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]

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]

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]

Are any of your controls called Filter?

 

[forms_are_nightmares]

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]

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]

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

Before Me.Filter = strWhere, put this:

Me.Filter = vbNullString

 

[forms_are_nightmares]

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]

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

 

[vbaInet]

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

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]

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

 

[vbaInet]

You're welcome!

 

[vbaInet]

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]

Will do. Thanks again.

 

[vbaInet]

No problemo!

 

[KiEESH]

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!

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]

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:

Me!LAST_EDITED_BY = GetUserName()

 

[KiEESH]

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:

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]

I set the textbox control equal to:

= GetUserName ()

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

 

[JHB]

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.