Bevos
asked on
Access 2007: VBA script for matching filename to field returns error: User-defined type not defined'
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
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
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
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
ASKER
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.[FileN
Thanks again,
Bevo
EE-AbsRev.mdb
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.[FileN
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
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
ASKER
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
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
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
I needed to add
Windows Script Host Object Model
Microsoft ActiveX Data Objects 6.0
and then your sample compiled
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
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]
just to be safe I think you should change all reference to [callnumber] to [call number]
ASKER
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
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
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
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
ASKER
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.
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("t
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
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("t
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
ASKER
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
Bevo
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
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
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.
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.
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.
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.
ASKER
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'
So in the end the field should store the full path: ' C:\Project\Pdf\CallNumber_
Does this help? Please let me know and thanks again for continuing to help me despite my ignorance on some of these issues.
Bevo
So in the end the field should store the full path: ' C:\Project\Pdf\CallNumber_
Does this help? Please let me know and thanks again for continuing to help me despite my ignorance on some of these issues.
Bevo
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?
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?
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Oh my goodness! It actually works! I've been trying this for three weeks! You're a true expert Nick :)
it's the one for the FileSytemObject