query not keeping upto date

[SoxPats83]

i have a query that calculates how many open tasks a person has left in the day and the total value of the remaining tasks. this query functions fine and updates after a user completes a task. problem is that when a user wants to go back and clear all the fields in the form they use to complete the tasks, the query is supposed to recognize this cleared task as open once again, the query is not acknowledging this and keeps the count as if it has been completed.

any thoughts?

 

[boblarson]

What's the SQL for the query? Also, if the person "clears the form" but does not move somehow where the record actually updates, then it is still as if nothing happened. You would need to have a way to save the record after they clear the form so that the new values are reflected.

 

[SoxPats83]

What's the SQL for the query? Also, if the person "clears the form" but does not move somehow where the record actually updates, then it is still as if nothing happened. You would need to have a way to save the record after they clear the form so that the new values are reflected.

my SQL is:


SELECT Tasks.ID, Tasks.Value, Tasks.Completed, Tasks.Number
FROM Tasks
WHERE (((Tasks.Completed) Is Null));

basically, i have a form that has this qiery as a record source. the form is in datasheet view, and what the end user should be seeing is the list of all the ID's, next to them is the amount of tasks remaining open, and next to that is the total value of the remaining open tasks.

Thanks!

 

[boblarson]

So, can you provide a screenshot of the form the user is using when you say they "clear all the fields" so I can understand what might be happening. I still think it is a case of a record not updating yet which causes the count to be off.

 

[SoxPats83]

So, can you provide a screenshot of the form the user is using when you say they "clear all the fields" so I can understand what might be happening. I still think it is a case of a record not updating yet which causes the count to be off.

unfortunately i am unable to provide a screenshot or any attachments. do you have any siggestions as to how to make these records update? is it possible the problem i am running into is within my table, and the query works fine, but the table information is not updating?

 

[boblarson]

That's what I mean. I mean - how are they clearing the form? If you let them clear it with a button then you can just clear the controls and then issue a

If Me.Dirty Then Me.Dirty = False

command afterwards which will save the record.

 

[SoxPats83]

That's what I mean. I mean - how are they clearing the form? If you let them clear it with a button then you can just clear the controls and then issue a

If Me.Dirty Then Me.Dirty = False

command afterwards which will save the record.

right, i tried the me.dirty=false within the vba code where the command button allows the user to clear the record. but unfortunately that is not resolving the issue. if the record is clear and is still not populating in my query, and i still select the clear button, the query acts as if i had just completed the task. very odd.

 

[boblarson]

Have you Requeried after saving the record?

 

[SoxPats83]

Have you Requeried after saving the record?

i have tried, but i have found that requering the form would be pointless as if i go back to the original query, the numbers are still bad. ius there a way to requery a query from within VBA?

 

[boblarson]

Okay, not sure what is going on here.

So, let me try to figure this out a bit.

When you are dealing with your query how and when are you opening it? Is it just being opened by you to view the current state of things? Or are you trying to use it in something?

I take it that the form the user is using is bound to a table or query. When they make their changes and move to another record, that data is saved to the table. If they CLEAR the text box by manually removing the data or some code is setting it to "" then it is not null but instead has an empty string which won't show in your query because it is not null but is in fact populated with an empty string.

So, while typing that last paragraph I think I may have stumbled upon the problem. Does your code for clearing the fields use:

Me.YourControl = ""

or

Me.YourControl = NULL


If the first then you are getting the correct results with your query because you aren't looking for the right thing.

 

[SoxPats83]

Okay, not sure what is going on here.

So, let me try to figure this out a bit.

When you are dealing with your query how and when are you opening it? Is it just being opened by you to view the current state of things? Or are you trying to use it in something?

I take it that the form the user is using is bound to a table or query. When they make their changes and move to another record, that data is saved to the table. If they CLEAR the text box by manually removing the data or some code is setting it to "" then it is not null but instead has an empty string which won't show in your query because it is not null but is in fact populated with an empty string.

So, while typing that last paragraph I think I may have stumbled upon the problem. Does your code for clearing the fields use:

Me.YourControl = ""

or

Me.YourControl = NULL


If the first then you are getting the correct results with your query because you aren't looking for the right thing.

you nailed it! thank you very much! i had "" instead of Null. so all in all the query was good. thanks again!

 

[boblarson]

Whew! Sometimes troubleshooting can be such a pain.

 

[SoxPats83]

Whew! Sometimes troubleshooting can be such a pain.

agreed, and sometimes the answer is right there in front of you the entire time.