Solved

constructiing criteria for opening task form

Posted on 2011-03-20
8
251 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

867 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

21 Experts available now in Live!

Get 1:1 Help Now