'invalid argument' Error, when preforming a search (ctrl+f) in a form

[marlan]

Hi all you experts,

in a Acc2003 project running in a client's location in Acc2010, he searches for a value in part of a text field a main form. This value is usually copied (ctrl+c) from part of a field in one of 9 sub-forms. These sub-form have about 20K records each (not all loaded at once, but may eventually be all loaded).
Once the desired value is copied to the clipboard, the user opens the search window (ctrl+f), in attempt to find this term in part of a field in main-form.
-EDIT-
Main-form is single-form view, sub-forms are continues-form view.

When clicking on 'Find', the user *sometimes* receives an 'invalid argument' error message.

Why would this happen? what dose this mean?

PS: This is an App about running sins 2007, FE size about 14MB, BE sizes 80MB and 5MB.

PSS: App structure (FE & BEs) has bin modified on quite a large scale a few months ago.

 

[The_Doc_Man]

Are we talking about using CTRL/C to copy the desired value, CTRL/F to open the form search option, and CTRL/V to then insert the search value? You used the word "term" to describe the search value and referenced "part of a field" so this sounds like a text field.

If so, I would wonder if sometimes the field might contain special punctuation that could get in the way of the search, because Access essentially has to use one of its own search features to find that value. E.g. the equivalent of a .FindFirst or .FindNext - which would be sensitive to quoting punctuation.

 

[marlan]

Are we talking about using CTRL/C to copy the desired value, CTRL/F to open the form search option, and CTRL/V to then insert the search value?

Yup, that simple action..

If so, I would wonder if sometimes the field might contain special punctuation that could get in the way of the search, because Access essentially has to use one of its own search features to find that value. E.g. the equivalent of a .FindFirst or .FindNext - which would be sensitive to quoting punctuation.

These errors have occurred a few times in the past few weeks, in the last occurrence both CTRL/C and CTRL/F were of words in English, written in simple Latin letters.

When occurs, the user restarts the computer

 

[The_Doc_Man]

Are you saying that when the error occurs, it restarts the computer? Or is it that the user gets so frustrated that THEY restart the computer as a separate action?

 

[marlan]

I was offline for the weekend, thanks for staying tuned

Are you saying that when the error occurs, it restarts the computer? Or is it that the user gets so frustrated that THEY restart the computer as a separate action?

The user says restarting the app didn't make a difference, so HE restarted the computer, witch has made a temporary difference.

I'm wondering if it is a corruption issue, or a computer/network/network drive issue.

Does it sound like file corruption? FE file? BE File?

Thanks a lot!

 

[The_Doc_Man]

If restarting the computer makes a temporary difference, it MIGHT be a type of corruption for which it is POSSIBLE that a manually initiated Compact & Repair might be constructive. As a precaution, if planning a C&R, you should ALWAYS make a copy of the file first, just in case the C&R goes west on you due to worse corruption than you might have thought.

However, I'm more inclined to think you have bad data somewhere that doesn't meet the technical definition of corruption (or at least, as we normally see it.)

For split database, you must perform the C&R on both files separately. If they are in fact on two different drives, it might not be a bad idea to schedule a disk scan of each drive at the next opportunity. If the BE is on a shared disk with many users, it might need to be a "scheduled, after-hours" scan.

Whatever is going on, the fact that it only occurs sometimes (even though doing the same operation each time) means it is MORE likely to be related to the BE file. But that is not 100% - more like 75/25 or 80/20.

Question: At this client's site, how many users are on the app and does each user have a private FE copy linking to a shared BE? Or is it based on some other configuration than I just described.

Question: When the user restarts the computer (and post 1 verified it is an FE/BE split), WHICH computer gets restarted that has some temporary effect? The host of the FE or the host of the BE?

 

[marlan]

Question: At this client's site, how many users are on the app and does each user have a private FE copy linking to a shared BE?

As for now it is 1 user, it could go up to 5 users, each having his own FE.

Question: When the user restarts the computer (and post 1 verified it is an FE/BE split), WHICH computer gets restarted that has some temporary effect? The host of the FE or the host of the BE?

Only the FE. the BE is in a network drive, part on a large office.

C&R was the first action I've done, for both FE and BE.

Let me add some more on the files structure:

1. New version of FE is distributed by putting the file in a known network location. The user runs a small local app, with copies the FE from network drive to his own c:\temp\AppName.mdb, and opens it.

2. I've added last April a small maintenance app, witch dose the C&R, and then Zips the file to be Back-uped:

Private Function CompactAndRepairFile(FileName As String) As Boolean
    Dim fs As Object, retVal As Boolean
    Dim DestFileName As String
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    DestFileName = GetFileFolder(FileName) & DateToStringFormated(Now, True) & GetFileName(FileName)
    
    retVal = Application.CompactRepair(FileName, DestFileName)
    
    If retVal Then
        fs.CopyFile DestFileName, FileName, True
    End If
    
    fs.DeleteFile DestFileName
    
    CompactAndRepairFile = retVal
    
End Function

And then Zips it for backup:

Sub ZipAFile(FileName As String, Optional Destination As String = "")
    Dim strDate As String, DefPath As String, sFName As String
    Dim oApp As Object, iCtr As Long, i As Integer
    Dim FName, vArr, FileNameZip
        
    FileNameZip = IIf(Len(Destination) < Len("C:\a.zip"), RemoveFileExtention(FileName) & ".zip", Destination)
    DefPath = GetFileFolder(CStr(FileNameZip))  'Application.DefaultFilePath
    If Right(DefPath, 1) <> "\" Then
        DefPath = DefPath & "\"
    End If

    FName = FileName
    If Not FileExists(CStr(FileNameZip)) Then _
        NewZip (FileNameZip)
    Set oApp = CreateObject("Shell.Application")
    oApp.Namespace(FileNameZip).CopyHere FName
'    Do Until oApp.Namespace(FileNameZip).items.Count = i
'    i = i + 1
'        'Application.Wait (Now + TimeValue("0:00:01"))
'    Loop
    On Error GoTo 0
'        MsgBox i & vbCrLf & "You find the zipfile here: " & FileNameZip
    'End If
End Sub

Question: could this series of actions be to harsh on the file?

TIA!

 

[The_Doc_Man]

If the CompactAndRepairFile isn't trying to C&R itself, this should be OK. However, I am a known cautious person about some things. Therefore, I would make an extra copy of the file before actually doing the Application.CompactRepair and then, IF nothing triggered a trap handler during the C&R, you can get rid of the extra copy and proceed with other disposition steps.

In your ZipAFile routine, it is of no BIG concern, but technically your variables FName, vArr, and FlieNameZip are all variants. If that is what you wanted, then fine.

The acid test of whether this sequence is or isn't harsh is whether in another folder you can unzip the file and use it. If you can't, then yes it was harsh.

 

[marlan]

The acid test of whether this sequence is or isn't harsh is whether in another folder you can unzip the file and use it. If you can't, then yes it was harsh.

It works fine in another folder.

I think that takes us back to the initial question: how could I detect if the 'invalid argument' Error is a computer, Network, network drive' or file error?

The next question: If it is a file error, how would I fix it? (assuming all the data is in tact, as for now I have nothing indicating otherwise...)

 

[gemma-the-husky]

maybe the copied text somehow includes a non-visible control character? Would that prevent "find" working correctly?

 

[marlan]

Thanks Dave for your reply

maybe the copied text somehow includes a non-visible control character? Would that prevent "find" working correctly?

would it error, or just not find?
The data this text is copied from is imported from XML using Xpath and DAO.Recordset, or from Excel, using TransferSpreadsheet.

I assume these non-visible control characters were not imported in these processes (even though the processes and data sours were modified in the past year), and then ask myself if these may have bin added as a result of some corruption?...

 

[The_Doc_Man]

If you are worried about code corruption, look up info on the /DECOMPILE switch to force it to get rid of all code and recompile everything. If you had corrupted code, this will get rid of it. If you had corrupted data, the C&R discussed earlier would handle that. If neither action fixes the problem, then it has to be, not corrupted data, but bad data that is legal to be copied but that interacts with the comparison process.

By any chance, does this code have an Option Compare statement? And if so, to what is it set? Is there any chance that the data source is UNICODE but the functions and database setting are Text-mode comparisons?

 

[marlan]

If you are worried about code corruption, look up info on the /DECOMPILE switch to force it to get rid of all code and recompile everything. If you had corrupted code, this will get rid of it. If you had corrupted data, the C&R discussed earlier would handle that. If neither action fixes the problem, then it has to be, not corrupted data, but bad data that is legal to be copied but that interacts with the comparison process.

By any chance, does this code have an Option Compare statement? And if so, to what is it set? Is there any chance that the data source is UNICODE but the functions and database setting are Text-mode comparisons?

Shamed to say, this process in not by code, but Ctrl/F Find tool built in MS Office / Access, so the Option Compare statement is not relevant.

 

[gemma-the-husky]

would it error, or just not find?
The data this text is copied from is imported from XML using Xpath and DAO.Recordset, or from Excel, using TransferSpreadsheet.

it was just a thought. I was surprised at your result, but there's no way of knowing what effect badly formed data would have. The fact that coming from an external file makes it more likely rather than less, I imagine

Equally, it might be a corruption in the field being searched, as others have noted.



Can you get your client to send you the source of what he is copying, so you can try to reproduce it, or at least inspect the source.

 

[The_Doc_Man]

Seems to me there is a "global" setting found from the File >> Options menu that allows you to setthe database comparison method. Check that.

I'm with Dave. Data coming from an external file is often less sanitized than data directly entered to Access. A "funky" but invisible character is my guess for the most likely culprit.