Query not firing...sometimes

[disneygoof via AccessMonster.com]

Geetings to all,

I have a bizzar problem. I have created a "time tracker" that 12 detailers
use. What this form does, I thought, is very simple.

1. they choose an option (design, detialing, checking, etc)
2. they click a button called (start)
3. I capture date and time
4. when they are done, they click stop
5. i transfer a row of data from a working table to a history table
6. I delete the row in the working table.

here's the issue...
sometimes the delete query does not fire.

Private Sub btnStopTrack_Click()
On Error GoTo Err_btnStopTrack_Click

Me.StopDate = Date
Me.StopTIme = Time
Me.STST = "DONE" 'trigger
Me.Requery
Me.Refresh
Me.btnStartTrack.Visible = True
Me.btnStartTrack.SetFocus
Me.btnStopTrack.Visible = False
Me.Requery

DoCmd.OpenQuery "qryDetailingTimeTransferToHoursTable" 'append to
history

DoCmd.OpenQuery "qryDetailingTimeDeleteFromTimeTable" 'delete from
working

If CurrentProject.AllForms("frmBulletin").IsLoaded = True Then
Forms!frmBulletin.Form.UserTracking = ""
End If

DoCmd.Close

Exit_btnStopTrack_Click:
Exit Sub

Err_btnStopTrack_Click:
MsgBox err.Description
Resume Exit_btnStopTrack_Click

End Sub

The Delete query looks for the users login name in the table AND the work
"DONE", if exists, delete that Row. Sometime it works, sometimes it does not.


It seems to be an issue more so at lunch when everyone might be "clocking"
out. I am sure it has something to do with the fact that we have muilt-users
in the same table. However, it does happen off and on through the day. I
have tried a number of options (using a macro, checking for the row in the
table when I load the form, checking for the row in the table from a previous
form) but nothing seems to completely solve the issue. I have been fighting
this for over a month. Last Friday, no issues, yesterday I have 6 issues.
Today so far 1 issue.

Problem.
If the row does not delete, then the user can not clock into another project..
..they are stuck in limbo and I have to manaul delete the row. The Append
query works perfect every time.

About a week ago I added the following code...basically I look to see if the
row has the current user (GetUserName) and the work "DONE". And you can see
I then run the detete query if they exist. But this does not always work
either.

Private Sub Form_Open(Cancel As Integer)

Dim leftOver As Integer

leftOver = Nz(DLookup("[ProjectID]", "tblDetailingTimeTracker", "
[Username] ='" & GetUserName()& "' and Not IsNull([STST])"), 0)

If leftOver <> 0 Then
DoCmd.OpenQuery "qryDetailingTimeDeleteFromTimeTable"
Me.Requery
End If

End Sub

HELP!!!!!!!!!!
Thanks David

 

[Allen Browne]

Instead of OpenQuery, use Execute. This avoids the confirmation diaogs (I
suspect you turned SetWarnings off), but still lets you know about errors
(if you use dbFailOnError) and also test if it succeeded (using
RecordsAffected.)

For details, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

Using Execute also opens the possiblity of using a transaction (so the
insert does not occur without the delete.) If you are interested in learning
the traps and techniques for that, see:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Geetings to all,

I have a bizzar problem. I have created a "time tracker" that 12
detailers
use. What this form does, I thought, is very simple.

1. they choose an option (design, detialing, checking, etc)
2. they click a button called (start)
3. I capture date and time
4. when they are done, they click stop
5. i transfer a row of data from a working table to a history table
6. I delete the row in the working table.

here's the issue...
sometimes the delete query does not fire.

Private Sub btnStopTrack_Click()
On Error GoTo Err_btnStopTrack_Click

Me.StopDate = Date
Me.StopTIme = Time
Me.STST = "DONE" 'trigger
Me.Requery
Me.Refresh
Me.btnStartTrack.Visible = True
Me.btnStartTrack.SetFocus
Me.btnStopTrack.Visible = False
Me.Requery

DoCmd.OpenQuery "qryDetailingTimeTransferToHoursTable" 'append to
history

DoCmd.OpenQuery "qryDetailingTimeDeleteFromTimeTable" 'delete from
working

If CurrentProject.AllForms("frmBulletin").IsLoaded = True Then
Forms!frmBulletin.Form.UserTracking = ""
End If

DoCmd.Close

Exit_btnStopTrack_Click:
Exit Sub

Err_btnStopTrack_Click:
MsgBox err.Description
Resume Exit_btnStopTrack_Click

End Sub

The Delete query looks for the users login name in the table AND the work
"DONE", if exists, delete that Row. Sometime it works, sometimes it does
not.


It seems to be an issue more so at lunch when everyone might be "clocking"
out. I am sure it has something to do with the fact that we have
muilt-users
in the same table. However, it does happen off and on through the day. I
have tried a number of options (using a macro, checking for the row in the
table when I load the form, checking for the row in the table from a
previous
form) but nothing seems to completely solve the issue. I have been
fighting
this for over a month. Last Friday, no issues, yesterday I have 6 issues.
Today so far 1 issue.

Problem.
If the row does not delete, then the user can not clock into another
project..
they are stuck in limbo and I have to manaul delete the row. The Append
query works perfect every time.

About a week ago I added the following code...basically I look to see if
the
row has the current user (GetUserName) and the work "DONE". And you can
see
I then run the detete query if they exist. But this does not always work
either.

Private Sub Form_Open(Cancel As Integer)

Dim leftOver As Integer

leftOver = Nz(DLookup("[ProjectID]", "tblDetailingTimeTracker", "
[Username] ='" & GetUserName()& "' and Not IsNull([STST])"), 0)

If leftOver <> 0 Then
DoCmd.OpenQuery "qryDetailingTimeDeleteFromTimeTable"
Me.Requery
End If

End Sub

 

[disneygoof via AccessMonster.com]

Allen,

Thanks for the feedback. I reviewed the two links, however I am not sure I
understand how these are going to help my Delete Query to run everytime? You
are correct, I do have all warnings off. No user gets a warning or message
about action queries. Infact, I have "Confrim Action Queries" in the options
menu disabled.

Of the two queries I have to execute:
DoCmd.OpenQuery "qryDetailingTimeTransferToHoursTable" 'append to
history
DoCmd.OpenQuery "qryDetailingTimeDeleteFromTimeTable" 'delete from
working
The frist one works perfect everytime (transfer the data from one table to
another). The second one does not always execute, or if it does, it is not
doing it's job all the time. Sometimes the ROW in the temp table is deleted
and soemtimes it is NOT. And it does not happen to the same person all the
time.

I guess what I am looking to know is "WHY doesn't the delete query execute
everytime correctly? WHAT is causing its inconsistant behavior?"

Thanks,
David

Instead of OpenQuery, use Execute. This avoids the confirmation diaogs (I
suspect you turned SetWarnings off), but still lets you know about errors
(if you use dbFailOnError) and also test if it succeeded (using
RecordsAffected.)

For details, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

Using Execute also opens the possiblity of using a transaction (so the
insert does not occur without the delete.) If you are interested in learning
the traps and techniques for that, see:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html

Geetings to all,

[quoted text clipped - 88 lines]

End Sub

 

[Allen Browne]

There are lots of things that could cause a record not to delete. For
example, it could be in use, locked by a user or process; there could be a
related record that's preventing the deletion, the delete query might
contain criteria that don't include the records, or joins that cause it not
to be affected, or ...

The point I'm making is that you won't even know *if* it worked (let alone
*why* it worked or not) if you use RunSQL or OpenQuery. The only way to get
feedback about what's going on is to use Execute with the dbFailOnError
switch. Then when it fails, you will get an error message telling you why.

 

[disneygoof via AccessMonster.com]

I agree...no feedback. After I sent my last response I desided to look at
your second option with the dbFailOnError. It took me some time to
understand some of it, but I think I have it understood. I have been
evaluating and testing some code...I like it so far. I have about 90% of it
working correctly for me. I am down to one issue now, that I have not solved
yet...but working on it.

Thank you for the info and code to review. I'll let you know what I get from
it.
David.

There are lots of things that could cause a record not to delete. For
example, it could be in use, locked by a user or process; there could be a
related record that's preventing the deletion, the delete query might
contain criteria that don't include the records, or joins that cause it not
to be affected, or ...

The point I'm making is that you won't even know *if* it worked (let alone
*why* it worked or not) if you use RunSQL or OpenQuery. The only way to get
feedback about what's going on is to use Execute with the dbFailOnError
switch. Then when it fails, you will get an error message telling you why.

Thanks for the feedback. I reviewed the two links, however I am not sure
I

[quoted text clipped - 44 lines]

 

[disneygoof via AccessMonster.com]

OK...I tried the code...modifying as needed for by db. Seemed to work OK...
However, every once in a while if errors did exist, the user got locked out
of the record. So I went back through some trial and errors and found that I
was firing "requery" statements, instead of refresh statements. With some
adjustments, I got my queries that won't fire to fire...I was still having
issues with the locked record, so for know I am back to my code. I still
like you thoughts and I plan to revisit when time permits for database
improvments.

thanks again,
David

I agree...no feedback. After I sent my last response I desided to look at
your second option with the dbFailOnError. It took me some time to
understand some of it, but I think I have it understood. I have been
evaluating and testing some code...I like it so far. I have about 90% of it
working correctly for me. I am down to one issue now, that I have not solved
yet...but working on it.

Thank you for the info and code to review. I'll let you know what I get from
it.
David.

There are lots of things that could cause a record not to delete. For
example, it could be in use, locked by a user or process; there could be a

[quoted text clipped - 12 lines]

 

[Allen Browne]

Perhaps there is a dirty record before the query runs?

If you are running your code from a bound form, force the save of any edits
in progress:
If Me.Dirty Then Me.Dirty = False

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

OK...I tried the code...modifying as needed for by db. Seemed to work
OK...
However, every once in a while if errors did exist, the user got locked
out
of the record. So I went back through some trial and errors and found
that I
was firing "requery" statements, instead of refresh statements. With some
adjustments, I got my queries that won't fire to fire...I was still having
issues with the locked record, so for know I am back to my code. I still
like you thoughts and I plan to revisit when time permits for database
improvments.

thanks again,
David

I agree...no feedback. After I sent my last response I desided to look at
your second option with the dbFailOnError. It took me some time to
understand some of it, but I think I have it understood. I have been
evaluating and testing some code...I like it so far. I have about 90% of
it
working correctly for me. I am down to one issue now, that I have not
solved
yet...but working on it.

Thank you for the info and code to review. I'll let you know what I get
from
it.
David.

There are lots of things that could cause a record not to delete. For
example, it could be in use, locked by a user or process; there could be
a

[quoted text clipped - 12 lines]

 

[disneygoof via AccessMonster.com]

Allen,

There shouldn't be anything. The form (continuous in nature) is used by the
user to select a project that he/she wants open; to edit, track time, or view
other info. So when they click on the proper button next to the project
number, the edit form opens. The Button I am referng to is a search button I
created using an inputbox. There can be 100+ projects so this search, takes
them to the project they are looking for. All it does is set that record
current...that works well. However, I'll try your suggestion, maybe there is
something lingering that I am unaware of...

Perhaps there is a dirty record before the query runs?

If you are running your code from a bound form, force the save of any edits
in progress:
If Me.Dirty Then Me.Dirty = False

OK...I tried the code...modifying as needed for by db. Seemed to work
OK...

[quoted text clipped - 29 lines]

a
[quoted text clipped - 12 lines]

 

[disneygoof via AccessMonster.com]

Allen,

It took me a while, but I got your suggestion to work for me...had to learn a
little first. THANK YOU!!!

David

Allen,

There shouldn't be anything. The form (continuous in nature) is used by the
user to select a project that he/she wants open; to edit, track time, or view
other info. So when they click on the proper button next to the project
number, the edit form opens. The Button I am referng to is a search button I
created using an inputbox. There can be 100+ projects so this search, takes
them to the project they are looking for. All it does is set that record
current...that works well. However, I'll try your suggestion, maybe there is
something lingering that I am unaware of...

Perhaps there is a dirty record before the query runs?

[quoted text clipped - 7 lines]

a
[quoted text clipped - 12 lines]

 

[Allen Browne]

Good news! Thanks for posting.