Solved

constructiing criteria for opening task form

Posted on 2011-03-20
8
253 Views
Last Modified: 2012-05-11
Table for recording Tasks to be completed includes the following fields:

      Reaff_Reference, text field
      bln_Task_Complete,boolean

I have constructed the following to count the number of tasks for a particular Reaff_Reference and this works accurately:

count_Pending = DCount("Reaff_Reference", "tbl_Tasks", "Reaff_Reference='" & Me.Reaff_Creditor_ID & "'" & " And " & "bln_Task_Complete=" & False)

-----------------------
I now want to open a form which would permit me to enter completion dates for each task applicable to a particular Reaff_Creditor_ID.

However, I am having difficulty in authoring the docmd for the form.

DoCmd.OpenForm "frm_Pending_Tasks", acNormal, , "Reaff_Reference='" & Me.Reaff_Creditor_ID & "'" & " AND " & "bln_Task_Complete=" & "False" & "'"

I get the following message which is attached.




vba-error.docx
0
Comment
Question by:James_Moroney
8 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35177759
try this

DoCmd.OpenForm "frm_Pending_Tasks", acNormal, , "Reaff_Reference='" & Me.Reaff_Creditor_ID & "' AND bln_Task_Complete=False"
0
 
LVL 4

Expert Comment

by:thewild
ID: 35179060
You have a quote in excess at the end of your filter :
"Reaff_Reference='" & Me.Reaff_Creditor_ID & "'" & " AND " & "bln_Task_Complete=" & "False" & "'"
should be :
"Reaff_Reference='" & Me.Reaff_Creditor_ID & "'" & " AND " & "bln_Task_Complete=False"
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 500 total points
ID: 35184366
I have tortured myself too many times to try "" & me.SomeControlWithAString & ""
The quotes are a pain to debug.
String Variables need to be surrounded by quotes
Chr(34) is a quote mark

Try this
DoCmd.OpenForm "frm_Pending_Tasks", acNormal, , "Reaff_Reference= " & Chr(34)  & Me.Reaff_Creditor_ID & Chr(34) & " AND bln_Task_Complete  = False"
You can see immediately that your string variable has its need quotes much easier.
Both of the previous posters corrections will work too.

I just despise a gazillion quotes because they are so hard to debug.
Imagine if your filter had three or four string variables!
This hangs together as an easily identifable unit
Chr(34)  & Me.Reaff_Creditor_ID & Chr(34)
 
This, to me, not so much
'" & Me.Reaff_Creditor_ID & "'

Did you get them in the right order '" or wrong "'
And they switch '" at the beginning "' at the end
I'll pass on that showcase, Bob
I'll take, did I have & Chr(34) & both before and after, for $100, Alex

Let's not even talk about the possibility your string may contain apostrophes or quotes
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Accepted Solution

by:
James_Moroney earned 0 total points
ID: 35186082
I attempted to use the  suggested solutions from  capricorn1 and thewild  but would not process. I have concern that I did not copy the suggestions properly because i am having one hell of a time recognizing quotes, etc. on the screen.

I tried the suggested solution from Nick67 and also ran into problems. Using Snagit, I have copied the command I used and the resulting error message. I agree with Nick67 that using the qoutes is a nightmare. Thanks to each of you for helping me. I would like to award 500 points to each of you. Can I do this?
need-help.docx
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35186222
Just what is 'bln_Task_Complete'?
Given that it doesn't have ME in front of it, we all assumed it was a field from the query/table underlying your form.
Dumb question...does the form have bln_Task_Complete in its data source?
Even dumber question...what does ME refer to in  Me.Reaff_Creditor_ID?
It can't be "frm_Pending_Tasks" since you're opening that.
Is it frm_Reaff_Creditor?

If your record source for frm_Pending_Tasks is correct, you should be able to add 'bln_Task_Complete' as a control
Can you?
0
 

Author Comment

by:James_Moroney
ID: 35188743
Nick,

Thank you so much. I have spent hours and hours trying different combinations. I did not include the bln_Task_Compete filed on the form to be opened. I read your comment and immediately realized the mistakei had made. Thanks again for your assistance. Your're the greatest!
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35191285
Not a problem.
Quotes drive me mad!
You should accept a comment that has the actual code you used as the solution.
And the original two posters suggestions would have worked too, so spliting the points is courteous.

Thanks

Nick67
0
 

Author Closing Comment

by:James_Moroney
ID: 35221356
I need help to finish up but the suggestions thus far have been very helpful. If Nick67 will send follow up to complete the answer I would be most grateful.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question