Access 2003, technical issues.

GEN3RIC

OSNN Senior Addict
Joined
4 Apr 2005
Messages
634
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.
 
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 profile posts

Also Hi EP and people. I found this place again while looking through a oooollllllldddd backup. I have filled over 10TB and was looking at my collection of antiques. Any bids on the 500Mhz Win 95 fix?
Any of the SP crew still out there?
Xie wrote on Electronic Punk's profile.
Impressed you have kept this alive this long EP! So many sites have come and gone. :(

Just did some crude math and I apparently joined almost 18yrs ago, how is that possible???
hello peeps... is been some time since i last came here.
Electronic Punk wrote on Sazar's profile.
Rest in peace my friend, been trying to find you and finally did in the worst way imaginable.

Forum statistics

Threads
62,015
Messages
673,494
Members
5,621
Latest member
naeemsafi
Back