Solved

constructiing criteria for opening task form

Posted on 2011-03-20
8
250 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 119

Expert Comment

by:Rey Obrero
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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now