Inconsistent Query Behavior 
Author Message
 Inconsistent Query Behavior

In the procedure below, the Execute method of the dbCounty database object
behaves inconsistently. Sometimes it accurately update the correct amount of
records, others times it doesnt update any record at all even on the same
data set - when it should. I am at the stage of frustration right now
because I cannot see any reason why it is happening. Would anyone here have
any ideas?

Sub UpdateCSType(County As String, StatFld As String, CSType As String)
    Dim dbCounty As Database
    Dim Tbl As String, sCrit As String

    Set dbCounty = CurrentDb
    Tbl = County & " Traffic2"

    Select Case CSType
        Case Is = "TC"
            sCrit = "[" & StatFld & "] Like '320*' Or [" & StatFld & "] Like
'321*' Or [" & StatFld & "] Like '322*'"
        Case Is = "DU"
            sCrit = "[" & StatFld & "] Like '316.193*'"
    End Select

    dbCounty.Execute "UPDATE [" & Tbl & "] SET [CS_Type] = '" & CSType & "'
WHERE " & sCrit
    Debug.Print "Records updated to TC " & dbCounty.RecordsAffected
    WaitSeconds 1, "Updating CS_Types"
End Sub

Thank you



Fri, 28 Oct 2005 06:14:48 GMT  
 Inconsistent Query Behavior
By the way, the procedure is being called from a library.


Quote:
> In the procedure below, the Execute method of the dbCounty database object
> behaves inconsistently. Sometimes it accurately update the correct amount
of
> records, others times it doesnt update any record at all even on the same
> data set - when it should. I am at the stage of frustration right now
> because I cannot see any reason why it is happening. Would anyone here
have
> any ideas?

> Sub UpdateCSType(County As String, StatFld As String, CSType As String)
>     Dim dbCounty As Database
>     Dim Tbl As String, sCrit As String

>     Set dbCounty = CurrentDb
>     Tbl = County & " Traffic2"

>     Select Case CSType
>         Case Is = "TC"
>             sCrit = "[" & StatFld & "] Like '320*' Or [" & StatFld & "]
Like
> '321*' Or [" & StatFld & "] Like '322*'"
>         Case Is = "DU"
>             sCrit = "[" & StatFld & "] Like '316.193*'"
>     End Select

>     dbCounty.Execute "UPDATE [" & Tbl & "] SET [CS_Type] = '" & CSType &
"'
> WHERE " & sCrit
>     Debug.Print "Records updated to TC " & dbCounty.RecordsAffected
>     WaitSeconds 1, "Updating CS_Types"
> End Sub

> Thank you



Fri, 28 Oct 2005 06:28:07 GMT  
 Inconsistent Query Behavior

Quote:

> By the way, the procedure is being called from a library.



> > In the procedure below, the Execute method of the dbCounty database object
> > behaves inconsistently. Sometimes it accurately update the correct amount
>  of
> > records, others times it doesnt update any record at all even on the same
> > data set - when it should. I am at the stage of frustration right now
> > because I cannot see any reason why it is happening. Would anyone here
>  have
> > any ideas?

> > Sub UpdateCSType(County As String, StatFld As String, CSType As String)
> >     Dim dbCounty As Database
> >     Dim Tbl As String, sCrit As String

> >     Set dbCounty = CurrentDb
> >     Tbl = County & " Traffic2"

> >     Select Case CSType
> >         Case Is = "TC"
> >             sCrit = "[" & StatFld & "] Like '320*' Or [" & StatFld & "]
>  Like
> > '321*' Or [" & StatFld & "] Like '322*'"
> >         Case Is = "DU"
> >             sCrit = "[" & StatFld & "] Like '316.193*'"
> >     End Select

> >     dbCounty.Execute "UPDATE [" & Tbl & "] SET [CS_Type] = '" & CSType &
>  "'
> > WHERE " & sCrit
> >     Debug.Print "Records updated to TC " & dbCounty.RecordsAffected
> >     WaitSeconds 1, "Updating CS_Types"
> > End Sub

> > Thank you

I'm not so experienced in this but a suggestion I had for debugging
would be to insert a line of code immediately prior to the UPDATE SQL.
 The code could create a select query and then open a recordset on the
select query to see if there are any records in it.  Sometimes I've
found my where statement is not what I expected and no records fit the
criterion, so nothing is done.


Fri, 28 Oct 2005 23:07:07 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. HELP -- IIF Inconsistent Behavior?

2. Remoting - very inconsistent behavior - help!

3. Inconsistent behavior of database files

4. Inconsistent behavior with vb app pointing to sql2000

5. HELP: Inconsistent Results when running multiple queries with DoCmd.OpenQuery

6. Queer Query Behavior

7. Pass-Through Query for Stored Proc Weird Behavior

8. Strange Behavior with queries in code

9. SetWindowLong APIs inconsistent behavior. Any Suggestions ....

10. Inconsistent OpenForm

11. inconsistent error in jet4.0?

12. Import multiple excel files with inconsistent number of worksheets in each into ACCESS

 

 
Powered by phpBB® Forum Software