Access 2003, technical issues.

Discussion in 'Web Design & Coding' started by GEN3RIC, Mar 28, 2006.

  1. GEN3RIC

    GEN3RIC Moderator Folding Team

    Messages:
    634
    Location:
    Woodbury, MN
    This is hard to explain, at my work, they sat me down and said "fix this program". It's in pretty rough shape and I'm no expert with Access :suprised: . The concept of the program is to allow a massive amount of workflow, to scan in documents, check whether they are a folder, placeholder, or document (paper- physically scanned in, or e-doc- imported online). After the doc's have been quality-checked, the system I have set up creates an index text file that assigns a name and category.

    Code:
    Private Sub Form_Open(Cancel As Integer)
     Dim num_RECS As Integer
     Dim file_LIST() As Variant, batch_up_FILE As String
     Dim batch_up_TXT As String
     Me.Requery
     
    project_LBL.Caption = Form_project.project_KEY.Value
    If Not Me.Recordset.RecordCount = 0 Then
     Me.Recordset.MoveLast
    With Me.Recordset
        num_RECS = .RecordCount
        .MoveFirst
     
     
       ReDim file_LIST(.RecordCount) As Variant
       If IsNull(!filetype) Then
           file_TYPE = ".tif"
            Else
            file_TYPE = !filetype
       End If
       file_LIST(1) = !index & file_TYPE
     
        Select Case !select
     
        Case 1
            batch_up_TXT = "f " & !index & " " & !title & vbCrLf & batch_up_TXT
        Case 2
            batch_up_TXT = "p " & !index & " " & !title & vbCrLf & batch_up_TXT
        Case 3
            batch_up_TXT = "e_s " & !index & " " & !index & !filetype & " " & !title & vbCrLf & batch_up_TXT
        Case 4
            batch_up_TXT = "e_s_c " & !index & " " & !index & !filetype & " " & !title & vbCrLf & batch_up_TXT
        Case 5
            batch_up_TXT = "e " & !index & " " & !index & !filetype & " " & !title & vbCrLf & batch_up_TXT
        Case 6
            batch_up_TXT = "c " & !index & " " & !index & !filetype & " " & !title & vbCrLf & batch_up_TXT
        End Select
     
        For i = 2 To .RecordCount
        .MoveNext
         If Not !select = 1 Then
            If IsNull(!filetype) Then
                file_TYPE = ".tif"
                Else
                file_TYPE = !filetype
            End If
            file_LIST(i) = !index & file_TYPE
            End If
     
     
        Select Case !select
     
        Case 1
            batch_up_TXT = "f " & !index & " " & !title & vbCrLf & batch_up_TXT
        Case 2
            batch_up_TXT = "p " & !index & " " & !title & vbCrLf & batch_up_TXT
        Case 3
            batch_up_TXT = "e_s " & !index & " " & !index & !filetype & " " & !title & vbCrLf & batch_up_TXT
        Case 4
            batch_up_TXT = "e_s_c " & !index & " " & !index & !filetype & " " & !title & vbCrLf & batch_up_TXT
        Case 5
            batch_up_TXT = "e " & !index & " " & !index & !filetype & " " & !title & vbCrLf & batch_up_TXT
        Case 6
            batch_up_TXT = "c " & !index & " " & !index & !filetype & " " & !title & vbCrLf & batch_up_TXT
        End Select
     
       Next i
    If Not Dir(global_path & Form_project.project_KEY.Value & "\BATCHES\" & Form_v5_batch_manager.Recordset!batch_label & ".for_upload", vbDirectory) = "" Then
        path_TXT.Value = global_path & Form_project.project_KEY.Value & "\BATCHES\" & Form_v5_batch_manager.Recordset!batch_label & ".for_upload"
        batch_up_FILE = global_path & Form_project.project_KEY.Value & "\BATCHES\" & Form_v5_batch_manager.Recordset!batch_label & ".for_upload\" & Form_v5_batch_manager.Recordset!batch_label & ".txt"
    Else
        MsgBox "Upload path not found"
        path_TXT.Value = "Upload path not found"
        batch_up_FILE = "c:\temp\" & Form_v5_batch_manager.Recordset!batch_label & ".txt"
    End If
     
        Select Case Form_v5_batch_manager.type_TXT
     
        Case "EDOC", "PAPER"
         verify_RETURN = verify_files_FUNC(file_LIST(), .RecordCount, batch_up_FILE)
     
            If verify_RETURN = "0" Then
            verify_TXT.ForeColor = 32768
            verify_TXT.Value = "Upload Ready Captain!"
     
            Else
     
                verify_TXT.ForeColor = 255
                verify_TXT.Value = verify_RETURN
     
            End If
     
        Case "FOLDERS"
            verify_TXT.ForeColor = 32768
            verify_TXT.Value = "Folders Ready!"
     
        Case "RENAME"
            verify_TXT.ForeColor = 32768
            verify_TXT.Value = "Ready to Rename!"
        End Select
    Form_batch_update_sub.edoc_TXT.Value = batch_up_TXT
     
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile(batch_up_FILE, True)
    a.WriteLine (batch_up_TXT)
    a.Close
     
    End With
    Else
        verify_TXT.Value = "No Data Available."
    End If
    'Form_batch_update_sub.edoc_TXT.SetFocus
    End Sub
    Everything is in working order, but when you go to "upload" the files in the directory, it pulls the file list from this text file. It should not be looking for placeholders or folders since they do not physically exist. However when I get to the batch update where it shows whether or not the appropriate files are available for upload, it is telling me that the files with an "f" or "p" are not there. And the worst part, it doesnt always tell me ALL of the "f's" or "p's" aren't there. Just a few of them!

    I am thinking it is some sort of error in the code. Can anybody spot it in what I provided? If not I have 232 pages for you to sift through :laugh:

    Thanks to anybody who looks into this.
     
  2. dave holbon

    dave holbon Moderator

    Messages:
    1,014
    Location:
    London England
    Well the code is ok I think, however the use of the “with” statement carries with it (sorry for the pun) many traps and tricks. It’s only supposed to be used for assignations, where lazy people like myself get tired of continually referring to an object, if you use it (as in this case) for recordsets and then use any type of nested function or looping the results can be as you have experienced as the “with” statement cannot handle loops inside itself correctly and was in any event never meant to be used this way.

    I’d try hard typing all references, get rid of the “with” structure entirely and assign proper variables to recordsets, also remove the Me.recordset and temporarily use the .recordsetsclone method just to make sure (if it’s on a multiple nested sub-form) that you are using the correct recordset in the first place.

    I suspect however that the problem resides in the use of the “with” statement and its inability to see anything outside of its immediate restricted scope.

    :) :)