By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,180 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

OpenRecordset problem when query includes parameters that reference to form controls

P: 4
Hi,
I got wrong result When I open a query as recordset with code:

Expand|Select|Wrap|Line Numbers
  1. Set qdf = db.QueryDefs("[qryA]")
  2. For Each prm In qdf.Parameters
  3.         prm.Value = Eval(prm.name)
  4. Next prm
  5. Set rsB = qdf.OpenRecordset
SQL for [qryA] is:
Expand|Select|Wrap|Line Numbers
  1. SELECT [Account], ([RTM]-[LoanV]) AS [Mgncall],[RTM], LoanV FROM qry8_ChangeByGtor
  2. WHERE (([RTM]-[LoanV])>=[forms]![FrmRpt]![txtNHM1].[Value]);
What really confuses me is that I only got problem when the where clause includes the expression field ([RTM]-[LoanV]). When I remove the expression or form control, for example, change to "WHERE ([RTM]>=[forms]![FrmRpt]![txtNHM1].[Value]);" or "WHERE (([RTM]-[LoanV])>=500;)", the result will be all right.

Any idea is appreciated. Thanks!
Dec 9 '11 #1
Share this Question
Share on Google+
12 Replies


NeoPa
Expert Mod 15k+
P: 31,709
Are you sure?

Adding .[Value] at the end of [forms]![FrmRpt]![txtNHM1] is both unnecessary and, as far as I've ever seen, incorrect SQL that doesn't run. SQL doesn't manage VBA objects in the way VBA code can. Any dot (.) in a name is treated as an unknown item.

Why don't you say exactly what you try and exactly what occurs when you do.
Dec 9 '11 #2

P: 4
I think SQL only doesn't recognize forms("FrmRpt").txtNHM1.value , but it does recognize [forms]![FrmRpt]![txtNHM1].[Value].

Anyway I tried removing ".[Value]", but still the same. It seems the expression is the real reason causing problem.

Expand|Select|Wrap|Line Numbers
  1. SELECT [Account], ([RTM]-[LoanV]) AS [Mgncall],[RTM], LoanV FROM qry8_ChangeByGtor
  2. WHERE (([RTM]-[LoanV])>=[forms]![FrmRpt]![txtNHM1]);
gives incorrect result (records don't meet the where condition)
while
Expand|Select|Wrap|Line Numbers
  1. SELECT [Account], ([RTM]-[LoanV]) AS [Mgncall],[RTM], LoanV FROM qry8_ChangeByGtor
  2. WHERE (([RTM])>=[forms]![FrmRpt]![txtNHM1].[Value]);
produces correct result

By the way, the problem only happens when I run the query in VBA.

Thanks.
Dec 9 '11 #3

NeoPa
Expert Mod 15k+
P: 31,709
Difei:
gives incorrect result (records don't meet the where condition)
I need more than this. I need to know what are the records which don't show but you think they ought to (values for [RTM] & [LoanV]) and what is the value of [forms]![FrmRpt]![txtNHM1] when you ran the test. I assume there may be a Null value somewhere you're not handling, but until I have some data to work with I cannot be much help.
Dec 9 '11 #4

P: 4
Thanks NeoPa.

I made a sample DB(attachment) to illustrate the problem.

After running the code (Report button), Table2 and Query2should have the same records and so should Table3 and Query3. Apparently they don't. I see some pattern there, but just can not figure out why.
Attached Files
File Type: zip testQueryControl.zip (60.6 KB, 49 views)
Dec 9 '11 #5

ADezii
Expert 5K+
P: 8,679
Is this what you re looking for?
Attached Files
File Type: zip testQueryControl_Revised.zip (48.7 KB, 69 views)
Dec 10 '11 #6

NeoPa
Expert Mod 15k+
P: 31,709
I hope you find what you need in ADezii's attachment, as you haven't answered my questions. I'm afraid you may believe you have done so by posting an attachment, but while many people make that same mistake, it simply isn't answering the questions. We're on a forum. Attachments aren't posts, and answers to questions are not an attachment where I can find answers if I put in the effort that you don't seem prepared to put in. Answers to questions in a post are answers in a post. Attachments are almost entirely useless to the vast majority of people who read this thread, and should be used only when every effort has already been made to put the question or answer into words.

You should appreciate that this site is here for the benefit of many more users than ever become members and ask their own questions. If your thread becomes a private area where all the details are hidden in attachments then it becomes of little value to the site, and many experts may choose to ignore it on that basis, as well as the fact that having to download attachments increases the time and effort they need to spend in order to help you.

Attachments can, and often are, very helpful in padding out an answer or helping to clarify a question. In nearly all such cases they will be requested from you by an expert trying to help. If you ever get to answering questions they can also be helpful as examples to clarify your posts. Attachments on their own, unrequested, are rarely appreciated.

@ADezii
While that is also largely true of posting solutions, the problem that way around is much less of an issue. You are volunteering assistance, which may be limited to the very few (presumably including the OP) who will look at it if it's presented simply as an attachment, but the key word here is volunteering. This post is directed at the OP who decided that posting an attachment would be easier for them than answering the questions asked.
Dec 10 '11 #7

ADezii
Expert 5K+
P: 8,679
@NeoPa:
Understood, and I do apologize for my inappropriate Response. I posted the Revised Attachment because I thought, but was not absolutely sure, that I had solved the problem. If the OP confirmed that the problem was resolved, I would have posted the details, if not, I would have asked for further, explicit information from the OP. In any event, you are correct in that a Revised Attachment, in the majority of cases, is useless and/or meaningless as far as resolving problems encountered by Users, and is also not beneficial to others as far as referencing.
Dec 10 '11 #8

NeoPa
Expert Mod 15k+
P: 31,709
I don't really see it as my responsibility to criticise experts ADezii (not unless they do things which which are blatantly anti-Bytes which this is certainly not). Especially such prolific ones as yourself. However, I won't deny it's an important point that I'd like everyone at least to understand.
Dec 11 '11 #9

P: 4
@NeoPa,
I apologize for the posting lacking of description - This is actually my first time posting a question in a forum. I was thinking a sample DB with all queries, forms and code would be the best to illustrate the problem, but I definitely learned a lesson.

@ADezii,
Your solution is perfect. Really appreciated.

@Anyone who runs into the same problem,
I'd like to summrize the problem and ADezii's solution a little bit to hopefully make it helpful to more people:
Problem:
I have a query containing parameters (numeric value) which reference to text box controls from a form. I got wrong result by running it in VBA (please refer to the code in the original post), although opening the query directly shows correct result.
Solution:
In the query, convert the text box value into numeric. e.g. use ">=Val([forms]![FrmRpt]![txtNHM1].[Value])" or ">=CCur([forms]![FrmRpt]![txtNHM1].[Value])" instead of ">=[forms]![FrmRpt]![txtNHM1].[Value]"

@ADezii,
Could you please add more details if I am missing anything? Thanks again.
Dec 12 '11 #10

NeoPa
Expert Mod 15k+
P: 31,709
Difei Luo:
but I definitely learned a lesson.
And you post illustrates that. I'm very happy you got the point so well :-)

It's also good news that ADezii found a solution for you, and even better news that you understood it, so will know how to avoid it in future.
Dec 13 '11 #11

Expert Mod 2.5K+
P: 2,545
For completeness, the problem here is the well-known one that VBA-executed queries cannot resolve references to form controls directly. ADezii's solution adopts the approach recommended by MS of setting the value of the missing parameters explicitly in code by using a querydef object to do so. This MSDN article provides background on the issue:

Everything About Using Parameters from Code

-Stewart
Dec 13 '11 #12

NeoPa
Expert Mod 15k+
P: 31,709
Nice link Stewart. I wasn't aware of any of that. That explains how come Jet SQL can interpret form control links (at all).
Dec 14 '11 #13

Post your reply

Sign in to post your reply or Sign up for a free account.