Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2007
    Posts
    70

    Unanswered: Having issues with some code!

    Hi I have the following code attached to a form button:

    Code:
    Private Sub Add_wing_res_Click()
           On Error GoTo Err_Add_wing_res_Click
        
    
        Dim mydb As Database
        Dim Myrst As Recordset
        Dim MyDate As String
        
        Dim fs As TableDef
        Dim l_sampleid As Long
        Dim s_surveyid As String
        Dim myset As Recordset
        Dim mytable As String
        Dim mysetA As Recordset
        Dim mytableA As String
        Dim mysets As Recordset
        Dim mytables As String
        Dim s_samplepointid As String
        Dim v_datedue
        Dim v_olddate
        Dim Ws As Workspace
        Dim rstSample As Recordset
        Dim rstBMHeader As Recordset
        Dim i As Long
        
       
        Set mydb = CurrentDb
        Set Ws = DBEngine.Workspaces(0)
        Set Myrst = mydb.OpenRecordset("tbl_Samples")
        
        Do Until IsDate(MyDate)
            MyDate = InputBox("Enter sample date.")
            If MyDate = "" Then
                Myrst.Close
                
                Exit Sub
            End If
        Loop
        
        Ws.BeginTrans
        For i = 1 To 6
            Select Case i
            Case 1
                If Chk_RUTRES_N1 = True Then
                    s_samplepointid = "W01WGWICD"
                    s_surveyid = "Rout Res"
                Else
                    GoTo Skip
                End If
            Case 2
                If Chk_RUTRES_2T = True Then
                    s_samplepointid = "W01WGWHCD"
                    s_surveyid = "Rout Res"
                Else
                    GoTo Skip
                End If
            Case 3
                If Chk_RUTRES_LT = True Then
                    s_samplepointid = "W01WGWBCD"
                    s_surveyid = "Rout Res"
                Else
                    GoTo Skip
                End If
            Case 4
                If Chk_RUTRES_SC = True Then
                    s_samplepointid = "W01WGWLCD"
                    s_surveyid = "Rout Res"
                Else
                    GoTo Skip
                End If
            Case 5
                If Chk_RUTRES_13 = True Then
                    s_samplepointid = "W01WGWQCD"
                    s_surveyid = "Rout Res"
                Else
                    GoTo Skip
                End If
            Case 6
                If Chk_RUTRES_14 = True Then
                    s_samplepointid = "W01WGW9CD"
                    s_surveyid = "Rout Res"
                Else
                    GoTo Skip
                End If
           
                
            
                 
                
            End Select
            
            With Myrst
                .AddNew
                !SampleDate = MyDate
                !SamplePointID = s_samplepointid
                !SurveyID = s_surveyid
                .Update
                .MoveLast
                l_sampleid = !SampleID
            End With
    
        
    'get the survey record
        Set mysets = mydb.OpenRecordset("tbl_survey", DB_OPEN_DYNASET)     ' Create dynaset.
        sCriteria = "surveyid = " & "'" & s_surveyid & "'"
        mysets.FindFirst sCriteria
        
        
         
    'create a new sample record
    
    
    'create an analysis record for each analysistypeid
        If mysets!analysistypecc = True Then
           mytableA = "tbl_analysis_CC"
           GoSub sub_addanaltable
        End If
       
        If mysets!analysistypeca = True Then
           mytableA = "tbl_analysis_Ca"
           GoSub sub_addanaltable
        End If
    
        If mysets!analysistypead = True Then
           mytableA = "tbl_analysis_ad"
           GoSub sub_addanaltable
        End If
       
        If mysets!analysistypezo = True Then
           mytableA = "tbl_analysis_zo"
           GoSub sub_addanaltable
        End If
    
        If mysets!analysistypezv = True Then
           mytableA = "tbl_analysis_zv"
           GoSub sub_addanaltable
        End If
        
        If mysets!analysistypeAL = True Then
           mytableA = "tbl_analysis_al"
           GoSub sub_addanaltable
        End If
        
        If mysets!analysistypesl = True Then
            mytableA = "tbl_analysis_SL"
            GoSub sub_addanaltable
        End If
        
            
        
        If mysets!analysistypebm = True Then
           mytableA = "tbl_analysis_bm"
           GoSub sub_addanaltable
           giBMInSurvey = -1
           GoSub sub_EditSampleTable
        Else
           giBMInSurvey = 0
           'GoSub sub_EditSampleTable
        End If
       
        If mysets!analysistypege = True Then
           mytableA = "tbl_analysis_ge"
           GoSub sub_addanaltable
        End If
    
    Skip:
    Next i
        
        Ws.CommitTrans
        GoTo Exit_Add_wing_res_Click
        
    sub_addanaltable:
        
        Set mysetA = mydb.OpenRecordset(mytableA, DB_OPEN_DYNASET)     ' Create dynaset.
        sCriteria = "sampleid = " & l_sampleid
        mysetA.FindFirst sCriteria
        
        If mysetA.NoMatch Then
            mysetA.AddNew     ' Enable editing.
            mysetA![SampleID] = l_sampleid
            mysetA.Update    ' Save changes.
        End If
       
        Return
        
    sub_EditSampleTable:
        
        Set rstBMHeader = mydb.OpenRecordset("tbl_analysis_BMHeader", DB_OPEN_DYNASET)     ' Create dynaset.
        sCriteria = "sampleid = " & l_sampleid
        rstBMHeader.FindFirst sCriteria
        
        If rstBMHeader.NoMatch Then
            rstBMHeader.AddNew     ' Enable editing.
            rstBMHeader![SampleID] = l_sampleid
            rstBMHeader.Update    ' Save changes.
        End If
        
        Return
         
    
        
            
    Exit_Add_wing_res_Click:
        mysetA.Close
        mysets.Close
        Myrst.Close
        
        Me.Requery
        MsgBox "Samples were added successfully"
        Exit Sub
    
    Err_Add_wing_res_Click:
        MsgBox Err.Description
        Ws.Rollback
        Resume Exit_Add_wing_res_Click
    End Sub
    I thought it was working fine and I think out of the 1000+ times the button has been clicked its only failed this once, and it has worked fine 3 or 4 times sinse! Let me explain what happened.
    The button was click and all of the enteries into tbl_samples went in fine but the corrosponding enteries into the other tables (tbl_analysis_AL, tbl_analysis_CA and tbl_analysis_ZO) never happened?

    Can anyone think of what may have caused this to happen just once but be fine most of the time?

    Cheers
    OB1

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    What error do you get when it fails (if any)?

    Offhand, I'd say if it works 1000+ times but fails once or twice, it's usually related to some kind of bad/non existent (i.e. an ID field) data being entered/updated. First thing I usually do tho is make sure that it's not related to a specific user or computer who happened to enter the data/clicked a button/did something different, the times it failed. It also sounds like (if it's not populating records in another table it's supposed to), that somehow somevalue is not getting populated that's supposed to.

    Then I'll test some possible data entry issues which may cause an update/button/command/expression to fail. For example, if a user enters something like this for a Data/Filter field: Once in a while, "I will dream a little." # - then I'll come back to the real % of what (life's) all about.

    And then clicks a Filter or Update command button.

    This type of statement can do helter-skelter on commands/expressions. Or again, the user clicks button A, then button C instead of button A, then button B.

    Then I'll see if it's a timing issue where after a certain amount of time or after so many records entered in a row, it fails. The biggest thing is to find out what was done a little bit different which caused it to fail (perhaps it only happens after X is done which is only rarely done or not supposed to be allowed to be done.)

    I've even seen it where when a virus scan initially starts up (or some other event runs) on someone's computer, it causes something to fail. There's a lot of different things.

    I'll usually reserve saying that it's a "network hiccup" thing as a last resort (right before I tell the user that it was an act of God.)

    Lastly and most importantly, if it's not populating records in a relational table (where it ALWAYS should have a corresponding record to the main table), you can try changing the relationship on the tables so that you do get an error rather than just a non-existent record (which would be more detrimental if the other table should ALWAYS have a matching record.) Or make the fields required which should NEVER be blank.

    I hope you find out why and this helps.
    Last edited by pkstormy; 03-14-08 at 15:18.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Dec 2007
    Posts
    70
    Hi pkstormy,
    Thanks for the reply, still cant put my finger on it though. Tried going through your suggestions but have come up with nothing.

    I was looking back through the code and I'm now questioning if I've put the 'Ws.CommitTrans' in the right place. Should it maybe be placed after the following section of the code instead of before it?

    Code:
     Ws.CommitTrans
        GoTo Exit_Add_wing_res_Click
        
    sub_addanaltable:
        
        Set mysetA = mydb.OpenRecordset(mytableA, DB_OPEN_DYNASET)     ' Create dynaset.
        sCriteria = "sampleid = " & l_sampleid
        mysetA.FindFirst sCriteria
        
        If mysetA.NoMatch Then
            mysetA.AddNew     ' Enable editing.
            mysetA![SampleID] = l_sampleid
            mysetA.Update    ' Save changes.
        End If
       
        Return
        
    sub_EditSampleTable:
        
        Set rstBMHeader = mydb.OpenRecordset("tbl_analysis_BMHeader", DB_OPEN_DYNASET)     ' Create dynaset.
        sCriteria = "sampleid = " & l_sampleid
        rstBMHeader.FindFirst sCriteria
        
        If rstBMHeader.NoMatch Then
            rstBMHeader.AddNew     ' Enable editing.
            rstBMHeader![SampleID] = l_sampleid
            rstBMHeader.Update    ' Save changes.
        End If
        
        Return
    I'm still not sure why this would only make a difference some times and not others?
    What do you think?
    Cheers, OB1

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Sorry I didn't get back to you sooner OB1.

    I've never done a
    Dim ws as workspace
    and then a
    ws.BeginTrans
    and
    ws.CommitTrans

    so I apologize but I can't tell you if that's what's causing your issue or not. The advice I gave you in the previous post was a general type of troubleshooting when you get an error "some" of the time. My guess would be that getting your error "some" of the time (but working most of the time) might have something to do with using a workspace variable though and the ws.BeginTrans/ws.CommitTrans (again just a guess.)

    I'll try and do some research on using a workspace variable but in all the years of programming with Access, I've never had to do this (although I believe I did something similar once (a few years back) with SQL Server and using cursors - (or was it a trigger) - been a while since I've done SQL Server programming though).

    Maybe someone else can chime in here that's done this with Access.
    Last edited by pkstormy; 03-18-08 at 15:20.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Dec 2007
    Posts
    70
    Hi pkstormy,

    Thanks for taking the time to reply. Having looked at the code again and again I'm pretty sure everything is as it should be and the WS.comittrans is in the right place. I'm completely stumped. I was remminded today that we had some server issues last week, which is when this started to happen so I can only assume it is something to do with that but because the whole code is in a Transaction you would think that either the whole code would work or the whole code wouldn't work. Seems very strange that part of the code work and part didn't in the same Transaction!
    I am starting to like your idea of an 'act of God'!!

    Thanks again for your help,
    OB1

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    lol... God gets blamed for a lot of stuff that Microsoft are responsible for ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Network guys are the biggest culprit for those once in a while hiccups and data not updating when everything was working great. I've always stressed to the network guys that the MUST let me know when they do something on the network which affects it's performance or coding, especially updating Service Packs (they never learn though <sigh>.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ^^ Agreed.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Dec 2007
    Posts
    70
    Quote Originally Posted by pkstormy
    I'll usually reserve saying that it's a "network hiccup" thing as a last resort (right before I tell the user that it was an act of God.)
    Thought I'd keep you updated, and I think you'd be happy to know it was not an act of God!

    And I have reserved the "network hiccup" as a last resort, but thats what it boils down to!

    The problem occured a few more time and became a bit too regular and it all just happened to coinside with big network problems. Once the network issue was resolved a simple 'compact and repair' of the database and 'hey presto' button functions normally again

    Thanks for your input and I deffinately agree with your comments on the network guys,
    OB1

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Thanks for the update OB1
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •