OpenRecordset problem when query includes parameters that reference to form controls

[Difei Luo]

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

Set qdf = db.QueryDefs("[qryA]")
For Each prm In qdf.Parameters
        prm.Value = Eval(prm.name)
Next prm
Set rsB = qdf.OpenRecordset

SQL for [qryA] is:

SELECT [Account], ([RTM]-[LoanV]) AS [Mgncall],[RTM], LoanV FROM qry8_ChangeByGtor
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!

[NeoPa]

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.

[Difei Luo]

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.

SELECT [Account], ([RTM]-[LoanV]) AS [Mgncall],[RTM], LoanV FROM qry8_ChangeByGtor
WHERE (([RTM]-[LoanV])>=[forms]![FrmRpt]![txtNHM1]);

gives incorrect result (records don't meet the where condition)
while

SELECT [Account], ([RTM]-[LoanV]) AS [Mgncall],[RTM], LoanV FROM qry8_ChangeByGtor
WHERE (([RTM])>=[forms]![FrmRpt]![txtNHM1].[Value]);

produces correct result

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

Thanks.

[NeoPa]

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.

[Difei Luo]

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.

[ADezii]

Is this what you re looking for?

[NeoPa]

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.

[ADezii]

@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.

[NeoPa]

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.

[Difei Luo]

@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.

[NeoPa]

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.

[Stewart Ross]

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

[NeoPa]

Nice link Stewart. I wasn't aware of any of that. That explains how come Jet SQL can interpret form control links (at all).