Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-04-26
21
Medium Priority
?
605 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

0
Comment
Question by:Bevos
  • 10
  • 8
  • 3
21 Comments
 
LVL 7

Expert Comment

by:manthanein
ID: 35471904
add  microsoft scripting  runtime   object  or   scrrun.dll  
it's the  one  for the FileSytemObject
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35481334
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
0
 

Author Comment

by:Bevos
ID: 35481851
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 7

Expert Comment

by:manthanein
ID: 35482382
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
0
 

Author Comment

by:Bevos
ID: 35483935
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35484712
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35484800
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
0
 
LVL 7

Expert Comment

by:manthanein
ID: 35485397
as for the  [CallNumber] is now 'Call Number'
just  to be safe I think you should change all reference to [callnumber]  to [call number]
0
 

Author Comment

by:Bevos
ID: 35485761
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35486012
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
0
 

Author Comment

by:Bevos
ID: 35486121
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.  
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35486769
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

0
 

Author Comment

by:Bevos
ID: 35488595
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35489096
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35489113
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35489124
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.
0
 

Author Comment

by:Bevos
ID: 35489169
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35489224
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?
0
 

Author Comment

by:Bevos
ID: 35489263
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
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 2000 total points
ID: 35489355
As noted,
You need references to
Windows Script Host Object Model
Microsoft ActiveX Data Object 6.0

Replace Sub strFolderName in modPDF with this code
Sub strFolderName(pathd As String)
Dim rst As Recordset
Dim fName As String
Dim smask As String
Dim myFilePrefix As Long
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
        myFilePrefix = Call_num(objFile.Name)
        Set rst = CurrentDb.OpenRecordset("select FileName, Callnumber from tblStudyDescription where callnumber = " & myFilePrefix, dbOpenDynaset)
        If rst.RecordCount = 1 Then
            rst.Edit
            rst![FileName] = objFile.Path
            rst.Update
        End If
        rst.Close
        Set rst = Nothing
    End If
Next objFile

MsgBox "Done!"

End Sub

Open in new window

Replace Function Call_num in modPDF with this code
Function Call_num(A As String) As Long
Dim B() As String, C As Long
B = Split(A, "_")
Call_num = B(0)
End Function

Open in new window

0
 

Author Comment

by:Bevos
ID: 35489370
Oh my goodness! It actually works! I've been trying this for three weeks! You're a true expert Nick  :)
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question