Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-14-2011, 11:02 AM   #1
forms_are_nightmares
Newly Registered User
 
Join Date: Apr 2010
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
forms_are_nightmares is on a distinguished road
You Can't Assign A Value To This Object

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

forms_are_nightmares is offline   Reply With Quote
Old 10-14-2011, 12:23 PM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,127
Thanks: 81
Thanked 2,009 Times in 1,957 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: You Can't Assign A Value To This Object

Quote:
IsDate(Me.txtStartDate)
You should probably convert the String to Date using
IsDate(CDate(Me.txtStartDate))
and see if that changes anything.
jdraw is offline   Reply With Quote
Old 10-17-2011, 03:50 AM   #3
forms_are_nightmares
Newly Registered User
 
Join Date: Apr 2010
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
forms_are_nightmares is on a distinguished road
Re: You Can't Assign A Value To This Object

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.

forms_are_nightmares is offline   Reply With Quote
Old 10-17-2011, 03:54 AM   #4
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: You Can't Assign A Value To This Object

Are any of your controls called Filter?
vbaInet is offline   Reply With Quote
Old 10-17-2011, 04:06 AM   #5
forms_are_nightmares
Newly Registered User
 
Join Date: Apr 2010
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
forms_are_nightmares is on a distinguished road
Re: You Can't Assign A Value To This Object

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.
forms_are_nightmares is offline   Reply With Quote
Old 10-17-2011, 04:11 AM   #6
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,769
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: You Can't Assign A Value To This Object

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?
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 10-17-2011, 04:11 AM   #7
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: You Can't Assign A Value To This Object

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

Before Me.Filter = strWhere, put this:

Me.Filter = vbNullString

vbaInet is offline   Reply With Quote
Old 10-17-2011, 04:20 AM   #8
forms_are_nightmares
Newly Registered User
 
Join Date: Apr 2010
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
forms_are_nightmares is on a distinguished road
Re: You Can't Assign A Value To This Object

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 is offline   Reply With Quote
Old 10-17-2011, 04:31 AM   #9
forms_are_nightmares
Newly Registered User
 
Join Date: Apr 2010
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
forms_are_nightmares is on a distinguished road
Re: You Can't Assign A Value To This Object

When I move my cursor over the yellow highlighted Me.filter = strWhere, this is what it reads...see attached
Attached Images
File Type: bmp code_error.bmp (37.2 KB, 144 views)
forms_are_nightmares is offline   Reply With Quote
Old 10-17-2011, 04:35 AM   #10
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: You Can't Assign A Value To This Object

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
vbaInet is offline   Reply With Quote
Old 10-17-2011, 04:42 AM   #11
forms_are_nightmares
Newly Registered User
 
Join Date: Apr 2010
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
forms_are_nightmares is on a distinguished road
Re: You Can't Assign A Value To This Object

Thank YOU very very much. That did the trick. I really appreciate your help and patience.
forms_are_nightmares is offline   Reply With Quote
Old 10-17-2011, 04:44 AM   #12
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: You Can't Assign A Value To This Object

You're welcome!
vbaInet is offline   Reply With Quote
Old 10-17-2011, 04:52 AM   #13
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: You Can't Assign A Value To This Object

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.
vbaInet is offline   Reply With Quote
Old 10-17-2011, 05:00 AM   #14
forms_are_nightmares
Newly Registered User
 
Join Date: Apr 2010
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
forms_are_nightmares is on a distinguished road
Re: You Can't Assign A Value To This Object

Will do. Thanks again.
forms_are_nightmares is offline   Reply With Quote
Old 10-17-2011, 05:02 AM   #15
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: You Can't Assign A Value To This Object

No problemo!

vbaInet is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
You can't assign a value to this object iglobalusa Forms 10 05-02-2010 08:59 PM
You can't assign a value to this object livvie Forms 2 09-07-2004 03:29 AM
You cant assign a value to this object?? desibasha General 0 04-08-2004 02:02 PM
You can't assign a value to this object gilberto Forms 2 03-13-2003 09:55 AM
You can't assign a value to this object kholm Forms 1 10-30-2002 12:31 PM




All times are GMT -8. The time now is 07:29 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World