Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Access 2007 VBA Programmer's Reference ISBN: 978-0-470-04703-3
This is the forum to discuss the Wrox book Access 2007 VBA Programmer's Reference by Teresa Hennig, Rob Cooper, Geoffrey L. Griffith, Armen Stein; ISBN: 9780470047033
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Access 2007 VBA Programmer's Reference ISBN: 978-0-470-04703-3 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 21st, 2011, 08:46 AM
Registered User
Points: 19, Level: 1
Points: 19, Level: 1 Points: 19, Level: 1 Points: 19, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using ReplaceWhereClause with Dates

Hi, I have been trying to use the RepalceWhereClause function with a date. My Union query that selects the dates is as follows:

SELECT DISTINCT tblWorkPlans.Target_Go_Live, tblWorkPlans.Target_Go_Live
FROM tblWorkPlans
UNION SELECT #1/1/1900# , "<all>"
FROM tblWorkPlans
ORDER BY tblWorkPlans.Target_Go_Live;

The Combo box has been set up to bind column 1, have 2 columns, widths 0;1 to show only the right column.

When I choose a date or if I choose <all>, I get an error message:
25880: The record source ' WHERE tblWorkPlans.Target_Go_Live = #5/28/2012# ' specified on this form or report does not exist.

If I choose <all> I get the same message except there is nothing between the quotes. 25880: The record source ' ' specified on this form or report does not exist.

Aside from my IF statements, the code comes direct from the SelectRecords code made available for download.

Public Sub SelectRecords()
On Error GoTo Error_Handler

Dim varWhereClause As Variant
Dim strAND As String

varWhereClause = Null
strAND = " AND "

If cobFilGoLive <> #1/1/1900# Then
varWhereClause = (varWhereClause + strAND) & _
"tblWorkPlans.Target_Go_Live = #" & cobFilGoLive & "#"
End If

varWhereClause = " WHERE " + varWhereClause

Me.RecordSource = ReplaceWhereClause(Me.RecordSource, varWhereClause)
Me.Requery

'EnableDisableControls

Exit_Procedure:
On Error Resume Next
Exit Sub
Error_Handler:
'DisplayUnexpectedError Err.Number, Err.Description
MsgBox (Err.Number & ": " & Err.Description)
' simpler version without centralized error text
Resume Exit_Procedure
Resume

End Sub

Any thoughts on what could be wrong here?
Reply With Quote
  #2 (permalink)  
Old September 21st, 2011, 02:47 PM
gjgriffith's Avatar
Wrox Author
Points: 517, Level: 8
Points: 517, Level: 8 Points: 517, Level: 8 Points: 517, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2009
Location: Boulder, CO
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default Did you check the Record Source?

Hello dpm1057,

Thank you so much for posting your question in the "Access 2007 VBA Programmer's Reference" book forum. You asked:

Quote:
When I choose a date or if I choose <all>, I get an error message:
25880: The record source ' WHERE tblWorkPlans.Target_Go_Live = #5/28/2012# ' specified on this form or report does not exist.
...
...
...
Any thoughts on what could be wrong here?
So, I think I see the answer to your problem. You set the value for the where clause that you are going to replace to NULL in your code that you've provided here:

Code:
...
varWhereClause = Null
strAND = " AND "

If cobFilGoLive <> #1/1/1900# Then
    varWhereClause = (varWhereClause + strAND) & _
    "tblWorkPlans.Target_Go_Live = #" & cobFilGoLive & "#"
End If
...
In the above code, first you set the "varWhereClause" variable to Null and then you concatenate the Null to the " AND " clause, in the line of code that says:

Code:
    varWhereClause = (varWhereClause + strAND) & _
    "tblWorkPlans.Target_Go_Live = #" & cobFilGoLive & "#"
which would be invalid for the WHERE clause. Does that make sense? Hopefully you can understand the problem here, because it looks like you've created an invalid WHERE clause.

Also, my question to you is: Does this error appear in the Access 2007 VBA Programmer's Reference? If so, could you let me know the location in the book (i.e. the page number)? And thanks again for posting your questions!

Sincerely,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->
Reply With Quote
  #3 (permalink)  
Old September 21st, 2011, 08:35 PM
Registered User
Points: 19, Level: 1
Points: 19, Level: 1 Points: 19, Level: 1 Points: 19, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Ooops

Apologies... I fat fingered the error code in my post... it's 2580 not 25880. I am using an e-book version (Kindle) and a search for the error code in the book yields no results.
Reply With Quote
  #4 (permalink)  
Old September 22nd, 2011, 07:56 AM
Registered User
Points: 19, Level: 1
Points: 19, Level: 1 Points: 19, Level: 1 Points: 19, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Doesn't make sense

Hi Geoffrey,

Thanks for the response, but it does not make sense. Mostly because this is how the example provided in the sample database "Chamber Application" was written.

All I have done is change the 2 conditional IF statements to reflect the objects and tables I am filtering upon, and deleted the EnableDisableControls command since I am not using that.

This code works in the sample database, but it is not working when I try it with a date or text value from my database.

Public Sub SelectRecords()
On Error GoTo Error_Handler

Dim varWhereClause As Variant
Dim strAND As String

varWhereClause = Null
strAND = " AND "

If cboMemberStatusKey & "" <> "<all>" Then
varWhereClause = (varWhereClause + strAND) & _
"tblBusiness.MemberStatusKey = """ & _
cboMemberStatusKey & """"
End If

If Not IsNull(txtPaymentAmt) Then
varWhereClause = (varWhereClause + strAND) & _
"tblBusiness.BusinessKey IN (" & _
"Select BusinessKey From tblPayment Where" & _
" PaymentAmount = " & Me!txtPaymentAmt & ")"
End If

varWhereClause = " WHERE " + varWhereClause

Me.RecordSource = ReplaceWhereClause(Me.RecordSource, varWhereClause)
Me.Requery

EnableDisableControls

Exit_Procedure:
On Error Resume Next
Exit Sub
Error_Handler:
DisplayUnexpectedError Err.Number, Err.Description
'MsgBox (Err.Number & ": " & Err.Description)
' simpler version without centralized error text
Resume Exit_Procedure
Resume


End Sub
Reply With Quote
  #5 (permalink)  
Old September 22nd, 2011, 09:19 PM
gjgriffith's Avatar
Wrox Author
Points: 517, Level: 8
Points: 517, Level: 8 Points: 517, Level: 8 Points: 517, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2009
Location: Boulder, CO
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default

Hello dpm1057,

Ah, I think I understand now. You said:

Quote:
This code works in the sample database, but it is not working when I try it with a date or text value from my database.
Hmm, this comment makes me think there is a problem with how you are providing the WHERE clause statements. It is important to remember, in a WHERE clause:

1. Number type fields are normal. Example: [NumberField]=3
2. Text fields should be surrounded by single or double quotes. Example: [TextField]="Some Text"
3. Date fields should be surrounded by pound signs. Example: [DateField]=#09/22/2011#

Does this make sense? I'm guessing that the where clause itself is not being setup properly for each different field type.

So, hopefully that helps, but please let me know if you have any other questions. And thanks for reading the Access 2007 VBA Programmer's Reference - I really appreciate it!

Thanks,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->

Last edited by gjgriffith; September 22nd, 2011 at 09:22 PM..
Reply With Quote
  #6 (permalink)  
Old September 23rd, 2011, 08:09 AM
Registered User
Points: 19, Level: 1
Points: 19, Level: 1 Points: 19, Level: 1 Points: 19, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default WHERE clauses are a correct

Hi Geoffrey,

Thanks for trying to help with this, but I am starting to think that the issue is with one of the other pieces of code. This morning I took a copy of the working code from the example database. The example had 2 IF statements, on that looked at a text based combo box and the other that looked for a price. I deleted the price statements. Then I edited the text process by copying the object names from the forms property window and pasting them over the corresponding names from the sample code.

I even toyed with using the brackets around my table and field names. I still get the Error 2580, but then is shows me a valid WHERE clause with the message.

I am starting to wonder if it is my field names and how the ParseSQL and RepalceWhereClause procedures work. Both my field names have under scores in them. INVISION_CUSTOMER_NAME or Target_Go_Live. Could those underscores be throwing the process off?
Reply With Quote
  #7 (permalink)  
Old September 23rd, 2011, 01:02 PM
gjgriffith's Avatar
Wrox Author
Points: 517, Level: 8
Points: 517, Level: 8 Points: 517, Level: 8 Points: 517, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2009
Location: Boulder, CO
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default Underscores are allowed in Field Names

Hello dpm1057,

Thanks for the follow up. You said:

Quote:
...but I am starting to think that the issue is with one of the other pieces of code.
...
...
Both my field names have under scores in them. INVISION_CUSTOMER_NAME or Target_Go_Live. Could those underscores be throwing the process off?
Hmm, I'm not sure what to say. Underscores are allowed in field names, so that should not be the issue.

And at this point, it sounds like the code you provided above is NOT the exact code that you are having a problem with. For example, neither of the variables "Target_Go_Live" or "INVISION_CUSTOMER_NAME" that you mentioned in your previous note are listed in the previous code block you provided. It's going to be extremely difficult to help you solve your problem with the information that you've provided, when that information isn't the actual code that you are using. Does this makes sense?

So, if you want further help on this issue, please provide the actual code that you are having a problem with.

Thanks,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->
Reply With Quote
  #8 (permalink)  
Old September 23rd, 2011, 01:22 PM
Registered User
Points: 19, Level: 1
Points: 19, Level: 1 Points: 19, Level: 1 Points: 19, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Got it to work...

On a whim I opened the record source on the form and added my combo boxes to the criteria for their corresponding fields. As soon as I did that, it worked.
Reply With Quote
  #9 (permalink)  
Old September 23rd, 2011, 03:38 PM
gjgriffith's Avatar
Wrox Author
Points: 517, Level: 8
Points: 517, Level: 8 Points: 517, Level: 8 Points: 517, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2009
Location: Boulder, CO
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Talking Glad to hear it works!

Hello dpm1057,

Thanks for the follow up note. I'm Extremely glad to hear that you got it working . If there is anything else we can do to help please let us know!

And thank you again for reading the "Microsoft Access 2007 VBA Programmer's Reference", we truly appreciate your patronage!

Sincerely,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
dates again dhoward VB.NET 2002/2003 Basics 12 August 22nd, 2007 09:48 AM
dates DARSIN General .NET 4 January 14th, 2005 09:09 AM
ReplaceWhereClause briandugas BOOK: Access 2003 VBA Programmer's Reference 9 July 29th, 2004 01:33 PM
between dates capitala Access VBA 1 May 30th, 2004 05:20 PM
Dates treadmill SQL Language 3 July 3rd, 2003 02:32 PM



All times are GMT -4. The time now is 08:34 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.