• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Access 2003, technical issues.

#1
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
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.

:) :)
 

Members online

No members online now.

Latest posts

Latest profile posts

Hello, is there anybody in there? Just nod if you can hear me ...
Xie
What a long strange trip it's been. =)

Forum statistics

Threads
61,961
Messages
673,239
Members
89,014
Latest member
sanoravies