 |
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

September 21st, 2011, 08:46 AM
|
Registered User
|
|
Join Date: Sep 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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?
|

September 21st, 2011, 02:47 PM
|
 |
Wrox Author
|
|
Join Date: Jul 2009
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
|
|
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,
|

September 21st, 2011, 08:35 PM
|
Registered User
|
|
Join Date: Sep 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|

September 22nd, 2011, 07:56 AM
|
Registered User
|
|
Join Date: Sep 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

September 22nd, 2011, 09:19 PM
|
 |
Wrox Author
|
|
Join Date: Jul 2009
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
|
|
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,
Last edited by gjgriffith; September 22nd, 2011 at 09:22 PM..
|

September 23rd, 2011, 08:09 AM
|
Registered User
|
|
Join Date: Sep 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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?
|

September 23rd, 2011, 01:02 PM
|
 |
Wrox Author
|
|
Join Date: Jul 2009
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
|
|
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,
|

September 23rd, 2011, 01:22 PM
|
Registered User
|
|
Join Date: Sep 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|

September 23rd, 2011, 03:38 PM
|
 |
Wrox Author
|
|
Join Date: Jul 2009
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
|
|
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,
|
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 |
|
 |