Query only working sometimes

By below query is only working sometimes. Is there a better way of stating the "WHERE" line? It usually works once, but when I click through forms it stops working. I can't figure out when it properly reads the where and when it doesn't. All I know is it isn't consistent.

SELECT [Memo List Table].MemoNumber, tblOfficePhones.SalesPhone, OfficeWorkers.First, [Memo List Table].MemoEmail
FROM ([Memo List Table] INNER JOIN tblOfficePhones ON [Memo List Table].Area = tblOfficePhones.Area) INNER JOIN OfficeWorkers ON [Memo List Table].TalkedFirst = OfficeWorkers.Last
WHERE ((([Memo List Table].MemoNumber)=[Forms]![MemoForm949]![MemoNumber]));

Open in new window

cansevinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
When are you running the query?  The Where clause will only see the current record of MemoForm949 and so it never sees more than one value of MemoNumber at each execution.
0
cansevinAuthor Commented:
Ok... it just doesn't seem to always read the form. Could it be because it is a subform? It shouldn't be a subform... but it may be because of past redesigns.
0
cansevinAuthor Commented:
It works properly... then I close it. Go to a new record on a form. And then it pull us no data.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Nick67Commented:
WHERE ((([Memo List Table].MemoNumber)=[Forms]![MemoForm949]![MemoNumber]));
This will be  a static value, read from the form when the query executes.

but when I click through forms it stops working.
That's pretty general :)
I suspect you mean that "when I navigate the form, the data supplied by this query isn't changing, and I want it to."

If this query is powering a subform, the WHERE clause is redundant.  It is the master/child relationship that needs to be set up between whatever field [Forms]![MemoForm949]![MemoNumber] is and its equal on the subform.

If this query is powering a ListBox or ComboBox, then the form's Current() event needs
Me.TheNameOfTheControlPoweredByMyQuery.Requery
in it.
0
PatHartmanCommented:
The reference in your code is to a main form so I hope that is where MemoNumber is.  But I still don't have a clear picture of what you are doing.  Exactly when do you run the query?  Is the query the RecordSource for a subform or report?
0
cansevinAuthor Commented:
Ok.. first I'll explain the issue a little more.

When I open the query, it usually works fine. It reads the MemoNumber of the current MemoForm949. Then if I click to a new form record... which clearly has a different memo number and I try to run the query again. It doesn't read the new form that is open. When I open the query it has no data. It want it to always pull up the data from the MemoForm949 that is currenlty open.

It is used to pull information in an email. Later I have an email written that pulls information with DLookups from that query.

I hope that clears the fog. My guess I am doing something very stupid and wrong.
0
Nick67Commented:
When I open the query it has no data.
The first question to ask is "should it have data with this criteria?"
After all, if the value of [Forms]![MemoForm949]![MemoNumber] is say, -1, and no [Memo List Table].MemoNumber has that value, your query is working properly by not returning any records.

Now, if the value of [Forms]![MemoForm949]![MemoNumber] is something that equals values in [Memo List Table].MemoNumber and you get no records -- then there's some syntax issues.

To troubleshoot, in the form's Current event put in
MsgBox Nz(Me.MemoNumber, "It's Null")
Navigate the form.
Are values you expect being msg'd?
In the query, replace
[Forms]![MemoForm949]![MemoNumber]
with the values shown by the MsgBox, and see what happens
Do you get the data you expect?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cansevinAuthor Commented:
Thanks Nick... you are onto something with the 2nd part. When I type the form memo number in there... it still comes up.

You are a genious. Just figured it out. THanks!
0
Nick67Commented:
:)
Naw, I'm just Sage at the moment.
In another 65K points, well then I will be a certified genius.

Glad you figured it out.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.