I have a function that employees can use to update their table of stored documents. This function uses the DIR function to update a table that can then be accessed through a combo box. With an update of the combo box the user can then display the image on there screen.
My problem is in the last 2 weeks we have been receiving an error when updating this table: Invalid Procedure Call or Argument.
The code I use is below.
Function getprice()
Dim myrs1 As DAO.Recordset
Dim mydb As DAO.Database
Dim myfile
Set mydb = CurrentDb
DoCmd.Hourglass True
DoCmd.SetWarnings False
DoCmd.OpenQuery "priceit_del"
DoCmd.SetWarnings True
Set myrs1 = mydb.OpenRecordset("priceit_sel"
myfile = Dir("f:\pricebid\*.tif"
Do While myfile <> ""
myrs1.AddNew
myrs1!filename = myfile
myrs1!solno = Left(myfile, Len(myfile) - 4)
myrs1.Update
myfile = Dir
Loop
DoCmd.SetWarnings False
DoCmd.OpenQuery "priceit_update"
DoCmd.OpenQuery "priceit_update_archive"
DoCmd.SetWarnings True
DoCmd.Hourglass False
Set myrs1 = Nothing
Set mydb = Nothing
End Function
The opening query is to clear out the table. The 2 ending querys are to update fields in the table so the combo box can lookup the document by different control numbers.
The error stated above happens at the 'myfile=dir' just above the loop command. I can check the output table and it normally has about 80% of the filenames captured.
An additional note is that this directory(pricebid) is used by probably 10 people. 2 of those people are adding documents to the folder all day long. The folder currently contains about 27,000 documents. The other 10 use it to lookup documents, and they may choose to update their table at any point during the day, depending on whether they need the documents saved that day.
Is there a conflict between the new documents being saved and the ability of DIR to get filenames in a timely manner?
Do we simply have too many documents saved for this process of looking up filenames?
My problem is in the last 2 weeks we have been receiving an error when updating this table: Invalid Procedure Call or Argument.
The code I use is below.
Function getprice()
Dim myrs1 As DAO.Recordset
Dim mydb As DAO.Database
Dim myfile
Set mydb = CurrentDb
DoCmd.Hourglass True
DoCmd.SetWarnings False
DoCmd.OpenQuery "priceit_del"
DoCmd.SetWarnings True
Set myrs1 = mydb.OpenRecordset("priceit_sel"
myfile = Dir("f:\pricebid\*.tif"
Do While myfile <> ""
myrs1.AddNew
myrs1!filename = myfile
myrs1!solno = Left(myfile, Len(myfile) - 4)
myrs1.Update
myfile = Dir
Loop
DoCmd.SetWarnings False
DoCmd.OpenQuery "priceit_update"
DoCmd.OpenQuery "priceit_update_archive"
DoCmd.SetWarnings True
DoCmd.Hourglass False
Set myrs1 = Nothing
Set mydb = Nothing
End Function
The opening query is to clear out the table. The 2 ending querys are to update fields in the table so the combo box can lookup the document by different control numbers.
The error stated above happens at the 'myfile=dir' just above the loop command. I can check the output table and it normally has about 80% of the filenames captured.
An additional note is that this directory(pricebid) is used by probably 10 people. 2 of those people are adding documents to the folder all day long. The folder currently contains about 27,000 documents. The other 10 use it to lookup documents, and they may choose to update their table at any point during the day, depending on whether they need the documents saved that day.
Is there a conflict between the new documents being saved and the ability of DIR to get filenames in a timely manner?
Do we simply have too many documents saved for this process of looking up filenames?