We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Access 2007: VBA script for matching filename to field returns error: User-defined type not defined'

Bevos
Bevos asked
on
Medium Priority
667 Views
Last Modified: 2012-05-11
Hello, one of the experts here worked with me to make a a script to match PDF filenames in a directory with a field [CallNumber] in a table tblStudyDescription. The script has worked well in the past but recently (in a new database) I tried to move it over and am getting the error 'Compile error: User-defined type not defined'.  
I've searched about this on google and people seem to say it is related the references defined in VBA.  These are the ones I've defined right now: 'Visual Basic for applications', 'Microsoft Access 12.0 Object Library', 'OLE Automation', 'Microsoft Office 12.0 Access database engine Object', and 'Microsoft Office 14.0 Object library'.  
Does anyone know how I might fix this?

Thanks so much for any expert advice,
Ryan
Option Compare Database

Private Sub cmdExport_Click()
    Dim strFolderName As String

    strFolderName = BrowseFolder("Choose Folder For Import")

    If Len(strFolderName) > 0 Then
    Me.FileName = strFolderName
        ' Do something with the selected folder
    Else
        'No folder chosen, or user canceled
    End If

End Sub

Private Sub cmdMatch_Click()
strFolderName (Me.FileName)
End Sub




Option Compare Database
Option Explicit

Private Const MAX_PATH = 260

Private Type BROWSEINFO
     hwndOwner As Long
     pidlRoot As Long
     pszDisplayName As Long
     lpszTitle As Long
     ulFlags As Long
     lpfnCallback As Long
     lParam As Long
     iImage As Long

End Type

Private Type OpenFilename
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    Flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Private Declare Function SHGetPathFromIDList Lib "shell32" (ByVal pidList As Long, ByVal lpBuffer As String) As Long
Private Declare Function GetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" (pOpenfilename As OpenFilename) As Long
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OpenFilename) As Long
Private Declare Function lstrcat Lib "kernel32" Alias "lstrcatA" (ByVal lpString1 As String, ByVal lpString2 As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32" (lpBI As BROWSEINFO) As Long
Private Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal hMem As Long)
Private Declare Sub CopyMem Lib "kernel32" Alias "RtlMoveMemory" (lpvDest As Any, ByVal lpvSource As String, ByVal cbCopy As Long)
Public ShowDirsOnly As Boolean
Public FolderDialogTitle As String
Public hwndOwner As Long
Public OpenDialogTitle As String
Public SaveDialogTitle As String


Sub strFolderName(pathd As String)
Dim dbs As Database, i As Double
Dim rst As Recordset
Dim FolderLength As Integer
Dim fName As String, smask As String
Dim objFSO As FileSystemObject
Dim objFolder As Folder
Dim objFile  As File
smask = ".pdf"
Set rst = CurrentDb.OpenRecordset("tblStudyDescription")
Set objFSO = New FileSystemObject
Set objFolder = objFSO.GetFolder(pathd)
For Each objFile In objFolder.Files
  If MatchSpec(objFile.Name, smask) Then
              fName = objFile
              rst.AddNew
              rst![FileName] = fName
              rst![CallNumber] = Call_num(fName)
              rst.Update
  End If
Next objFile
end1:
rst.Close
End Sub
Private Function MatchSpec(FileName As String, FileSpec As String) As Boolean
Dim A As String
A = Right(FileName, 4)
MatchSpec = False
If A = FileSpec Then MatchSpec = True
End Function
Function Call_num(A As String) As String
Dim B() As String, C As Long
B = Split(A, "\")
Call_num = Left(B(UBound(B)), 5)
End Function

Open in new window

Comment
Watch Question

add  microsoft scripting  runtime   object  or   scrrun.dll  
it's the  one  for the FileSytemObject
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
You are missing Option Explicit as the top.
This is good practice as it forces all your variables to be properly declared.

I suspect your culprit is BrowseFolder.

It is not VBA.  Likely it is a user created function.
Search your other db for 'Function BrowseFolder('

If I'm right you'll find it.
Copy the entire function over
If there are no other dependencies, you'll be good to go

Author

Commented:
Hi Nick and Manthanein thanks so much for the comments!  I added the Scrrun.dll and it removed the error in question, but the code still was not working.  I then tried Nick's solution (explicit is now present in both the form and module) and it helped, but the code will still not do the matching.  I'm at a loss for what's wrong here.

Any experts have an idea?  I'm attaching the example database to this question.  The problematic form is 'FileName' and the place where the data is to be transcribed is tblStudyDescription.[FileName].

Thanks again,
Bevo
EE-AbsRev.mdb
right click  on Filename Form  then select Design mode

Select Match PDF Files to Record Number button  
Press F4 to show property sheet
click event
on the On Click event  type [Event Procedure]

and it should  work

Author

Commented:
Sorry Nick and Manthanein, that was a bone-headed mistake!  
I am however still recieveing an error of 'Run-time errr '3421': Data type conversion error.' when I try to run the script.  It points me to the following line: rst![CallNumber] = Call_num(fName).  The fname declaration seems to be working and carring over the information of the PDF in the specified directory but the [CallNumber] is null despite being the field name from tblStudyDescription.  One thing that has changed is that captions are now present in that tbl and the caption for this field [CallNumber] is now 'Call Number'.  Would this effect how I should change the code here?

Thanks for any help as always,
Bevo
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Still a couple of non-existent references.
I needed to add
Windows Script Host Object Model
Microsoft ActiveX Data Objects 6.0

and then your sample compiled
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Your data type mismatch:
rst![CallNumber] = Call_num(fName)

[Callnumber] is a long integer --> from the table
Call_num returns a string -->Function Call_num(A As String) As String

You can't sometimes directly compare a number to a string
CLng(Call_num(fName)) or CString(rst![CallNumber])
One or the other, depending on what makes sense for your data
as for the  [CallNumber] is now 'Call Number'
just  to be safe I think you should change all reference to [callnumber]  to [call number]

Author

Commented:
Hi  Manthanein, changing this to [call number] gives me the error 'Item not found in this collection'.  
Nick, I added those references and tried to change the code like this:
CString(rst![CallNumber]) = Call_num(fName) which produces the error 'The expression On Click you enetered as the event property setting produced the following error: sub or function not defined.'
and rst![CallNumber] = CLng(Call_num(fName))
produced the error of type mismatch again.

Am I entering these codes correctly?

Thanks,
Bevo
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
My bad
CStr(rst![CallNumber])

you should change the function, though, and not need to coerce
Function Call_num(A As String) As Long
Dim B() As String, C As Long
B = Split(A, "\")
Call_num = CLng(Left(B(UBound(B)), 5))
End Function

and no [call number] isn't right.
A field's caption shows up in the label--but fieldname is the important thing in rst!fieldname.

In form Filename, you have
------------------------------------------------------
Option Compare Database
Option Explicit

Private Sub cmdExport_Click()
    Dim strFolderName As String

    strFolderName = BrowseFolder("Choose Folder For Import")

    If Len(strFolderName) > 0 Then
    Me.FileName = strFolderName
        ' Do something with the selected folder
    Else
        'No folder chosen, or user canceled
    End If

End Sub

Private Sub cmdMatch_Click()
strFolderName (Me.FileName)
End Sub

-------------------------------------------------

You have strFolderName Dimmed as a string and then you call it as a sub in modPDF.
That's confusing, to say the least!

That sub also doesn't work in a way I completely understand.
Do you intend that it should find every pdf in a folder and create a record for each one, without any kind of error checking.
I ran your button once on a folder of pdf's I have.
Records are created.
Hit the button again and BANG! errors happen, regarding duplicate records being created

Sample returned

Author

Commented:
Hi Nick, the purpose is to match the field callnumber (which has a value such as 100, 101, etc..) to a pdf which is lablel in the following way 100_OtherDescription.pdf.  So the script is looking for the number before the underscore and matching it to the call number.  
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
That isn't really what is happening though!

Private Sub cmdMatch_Click()
strFolderName (Me.FileName) 'this calls Sub StrFolderName form modPDF and passes in the value from control FileName
'BTW FileName is a BAD thing to call a control since that can very well be used by Access itself.
'and 'FileName' isn't even a filename, it actually holds a folder path
'how about txtMyFolderPath as a name?
End Sub

Ok, so we pass a Folder path to sub strFolderName

Sub strFolderName(pathd As String)
Dim dbs As Database, i As Double
Dim rst As Recordset
Dim FolderLength As Integer
Dim fName As String, smask As String
Dim objFSO As FileSystemObject
Dim objFolder As Folder
Dim objFile  As File
smask = ".pdf"
Set rst = CurrentDb.OpenRecordset("tblStudyDescription")
Set objFSO = New FileSystemObject
Set objFolder = objFSO.GetFolder(pathd)
For Each objFile In objFolder.Files 'for every file in this folder
  If MatchSpec(objFile.Name, smask) Then 'if it is a pdf, doesn't matter if it IamJackTheBear.pdf or any othername, it also doesn't check if this has been done already!

              fName = objFile 'this isn't good, you are setting an string = an object
              'How about  objFile.name, or objFile.path ?
              rst.AddNew
              rst![FileName] = fName
              rst![CallNumber] = Call_num(fName) 'how will you handle it if doesn't return a number?
              rst.Update
  End If
Next objFile
end1:
rst.Close
End Sub

Author

Commented:
Sorry, I'm too much of a novice to understand what you are trying to teach me here Nick.  I had several users on this forum help me with creating this so I don't know how to modify each element as you are suggesting.  Thanks so much for taking the time to give the thoughtful comments.

Bevo
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
No Problem.
Naming conventions are VERY important things.  They make your app self-documenting when used correctly.
They also help YOU get back in your own head space when you have to review your own code at a later date.
You have something strange in your code.  You created a variable called strFolderName.
OK.
Some folks will preface their variables with Hungarian notation prefixes, like str.
I don't, because I keep my stuff short and
Dim myFolderPath as string
tells me that **I** created it (my) and FolderPath tells me what I plan to put in it--and I have created it as a string.
But that's me.
Have a look here
http://www.joelonsoftware.com/articles/Wrong.html
http://www.exceluser.com/explore/vbastds.htm
but most especially here
http://support.microsoft.com/kb/173738
ALL of my unbound controls, I name using the last link.
I immediately know that
a) I created them
b) they are unbound
c) they'll hang together in Intellisense
d)what purpose I had in mind for them
I also know that I won't commit a major PITA--naming something after a SQL, Access, or VBA keyword.

You then have a function called strFolderName.  That could be highly confusing to say the least
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Onward.

In the altered sample I 'fixed' what was keeping your code for running at all.
But, while that code may execute, it has a lot of problems.

Your code takes a folder path and attempts to create a record for every single PDF it finds in that folder.
It doesn't have any provision to check that the PDF's it finds are ACTUALLY ones you want to create records for.
It has no provision for checking whether a record already exists
It has no provision for dealing with PDF's whose names won't actually reduce to a 5 digit number (IamJackTheBear.pdf for example)
It also doesn't give you nay feedback that it actually completes.

So while the code may execute, it isn't ready for production.
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
One problem your code still has is this line
              fName = objFile
fName is a string, but objFile is an object--actually a file with a slew of properties
This will lead to some very unpredictable results.

What do you ACTUALLY want to store?
Just the filename, the full path to the file, just the front part of the file's 8.3 name?
That will depend on what you want to do with that value after you store it.
That isn't something I know

So, I need much more detail about what your sample is intended to do before I can help you sort out it's issues.

Author

Commented:
So, I would like to use the folder browser that is on the form FileName to search for the folder where all the PDFs are stored (such as C:\Project\Pdf\).  The folder will have many pdf documents with filenames in this syntax 'CallNumber_Othertext.pdf' always using the underscore after the call number.  I want to match the field call number in the database to that filename prefix.  I thought the code was close to working and just needed some tweaking regarding the references but I guess I was wrong about this.  
So in the end the field should store the full path: ' C:\Project\Pdf\CallNumber_othertext.pdf' wherever it matches else just leave the filepath as null.

Does this help?  Please let me know and thanks again for continuing to help me despite my ignorance on some of these issues.

Bevo
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
There is presently no comparison of any type going on

If MatchSpec(objFile.Name, smask) Then 'if it is a pdf, doesn't matter if it IamJackTheBear.pdf or any othername
              fName = objFile.path
              rst.AddNew 'this creates a new record, it doesn't edit anything existing
              rst![FileName] = fName
              rst![CallNumber] = Call_num(fName)
              rst.Update
End If

So, how is the comparison to be done?
Do you want the code to walk through every file in the folder, and take everything before the _ and then search the table for a match?
If a match is found, then a field is to be updated with a path?

Could more than one match exist?
Should there be a message saying what records were updated?
Should there be a message saying which files were found that had no matching records?
What should happen if two files with the same prefix are found?
What should happen if a pre-existing value is already stored?

Am I grasping what you required?

Author

Commented:
Yes Nick, those are all very good questions and show that you're getting exactly at the heart of what I tried to get this code to do before I botched it.
So the comparison should look at each pdf document in the folder.
There won't be any duplicate matches (1 pdf per call number maximum)
There doesn't need to be any notification saying that records were updated, or that no matching pdf was found.
There shouldn't be two files with the same prefix in the directory unless someone made an error in labeling the pdf documents.  In that case, I guess an error message is appropriate.
If a value is already stored it should be overwritten.

Thank you again,
Bevo
CERTIFIED EXPERT
Most Valuable Expert 2014
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Oh my goodness! It actually works! I've been trying this for three weeks! You're a true expert Nick  :)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.