Re: [MS_AccessPros] Adding file locations and filenames to a continous form

 

hi Art,

you would not use Replace to STORE the data ... I modified the example that Duane gave you when you want VBA to evaluate what is there.  For instance, in

If IsNull ... and then you pass a value to DLookup

FileList.ItemData(intI)  is delimited with single quotes.  Obviously, this will be an issue if the data itself HAS a single quote in it. You could add Replace here; however, it would be better to use a string variable to construct what you need

respectfully,
crystal
 
~ have an awesome day ~

On 8/30/17 8:31 AM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] wrote:

Here is my code (with the debug statement), where would I put this replace statement?

Dim db As Database
 Dim rs As Recordset
 Dim iRecCount As Integer
 Dim strDocLoc As String
 Dim intI As Integer
 
 Set db = CurrentDb()
 Set rs = db.OpenRecordset("tblDocumentMain")

 iRecCount = Me.FileList.ListCount
 For intI = 0 To Me.FileList.ListCount - 1
  strDocLoc = Me.FileList.ItemData(intI)
  Debug.Print strDocLoc
 If IsNull(DLookup("DocLocation", "tblDocumentMain", "DocLocation = '" & FileList.ItemData(intI) & "'")) Then
 rs.AddNew
 rs!DOCLOCATION = FileList.ItemData(intI)
 rs!DATEOFDOC = Date
 rs!DOCUSER_ID = glngThisEmpID
 rs!DOCTYPE = FileExtensionFromPath(FileList.ItemData(intI))
 rs!ACTIVE = -1
 rs.Update
 End If

 Next intI

 

MsgBox "You have imported " & CStr(iRecCount) & " records.", vbOKCancel
  rs.Close
  Set rs = Nothing
  Set db = Nothing
 
 Me!sfrmDocMain.Form.Requery
Me.FileList.RowSource = ""
 

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Valar Dohaeris"

---

From: crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>

To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>

Sent: ‎Wednesday‎, ‎August‎ ‎30‎, ‎2017‎ ‎07‎:‎40‎:‎24‎ ‎AM

Subject: Re: [MS_AccessPros] Adding file locations and filenames to a continous form

 

... or better yet, use Replace when the string is assigned:

strDocLoc = Replace( FileList.ItemData(intI), "'", "''")

~crystal

On 8/30/17 7:35 AM, crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals] wrote:

Hi Art,

when you refer to a value in code that might have what you are delimiting with, use Replace

For instance, if you are delimiting the value with ' then replace one ' with two '

 "DocLocation = '" & Replace(strDocLoc, "'", "''") & "'"

respectfully,
crystal
 
~ have an awesome day ~

On 8/29/17 4:31 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] wrote:

After I integrated the debug.print I found it was actually working correctly but it bombed because one of the file names contained a '. Art's Tax return.doc. So how would you handle that. Change the name of the file but there could be more than that one that contains a special character.

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Valar Dohaeris"

---

From: Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>

To: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>

Sent: ‎Tuesday‎, ‎August‎ ‎29‎, ‎2017‎ ‎03‎:‎52‎:‎08‎ ‎PM

Subject: Re: [MS_AccessPros] Adding file locations and filenames to a continous form

 

Art,

In the debug mode, what values do you see when you hover your mouse pointer over intI and FileList.ItemData(intI)?

Have you tried using some debug.Print statements in your code?

Dim strDocLoc as String

strDocLoc = FileList.ItemData(intI)

debug.print strDocLoc

If IsNull(DLookup("DocLocation", "tblDocumentMain", "DocLocation = '" & strDocLoc & "'")) Then

Regards,

Duane

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, August 29, 2017 3:16 PM
To: John Viescas JohnV@msn.com [MS_Access_Professionals]
Subject: Re: [MS_AccessPros] Adding file locations and filenames to a continous form

 

 OK the add is still working but when I tried to add a duplicate I received the following error:

RUn-time  Error 3075

Syntax error (missing operator) in query expression'DocLocation='c:\USers\alorenzini\Documents\Art's return 2014.pdf".

When I click debug it high lights this line:

If IsNull(DLookup("DocLocation", "tblDocumentMain", "DocLocation = '" & FileList.ItemData(intI) & "'")) Then

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Valar Dohaeris"

---

From: John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>

To: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>

Sent: ‎Tuesday‎, ‎August‎ ‎29‎, ‎2017‎ ‎09‎:‎54‎:‎36‎ ‎AM

Subject: Re: [MS_AccessPros] Adding file locations and filenames to a continous form

 

Missing an additional close paren at the end (before the Then).

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/

John Viescas Consulting www.viescas.com If you're at all interested in Microsoft Access or SQL Server, this is the place to be! Check out the book recommendations and all the cool links!

 

(Paris, France)

On Aug 29, 2017, at 4:04 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

This line is turning red in in the vba editor....

If IsNull(DLookup("DocLocation", "tblDocumentMain", "DocLocation = '" & FileList.ItemData(intI) & "'") Then

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Valar Dohaeris"

---

From: John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>

To: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>

Sent: ‎Tuesday‎, ‎August‎ ‎29‎, ‎2017‎ ‎05‎:‎41‎:‎48‎ ‎AM

Subject: Re: [MS_AccessPros] Adding file locations and filenames to a continous form

Art- 

Maybe like this: 

Dim db As Database 
Dim rs As Recordset 
Dim iRecCount As Integer 

Dim intI As Integer 
Set db = CurrentDb() 
Set rs = db.OpenRecordset("tblDocumentMain") 

iRecCount = Me.FileList.ListCount 
For intI = 0 To Me.FileList.ListCount - 1 
If IsNull(DLookup("DocLocation", "tblDocumentMain", "DocLocation = '" & FileList.ItemData(intI) & "'") Then 
rs.AddNew 
rs!DOCLOCATION = FileList.ItemData(intI) 
rs!DATEOFDOC = Date 
rs!DOCUSER_ID = glngThisEmpID 
rs!DOCTYPE = FileExtensionFromPath(FileList.ItemData(intI)) 
rs!ACTIVE = -1 
rs.Update 
End If 

Next intI 

John Viescas, Author 
Effective SQL 
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out 
Microsoft Access 2007 Inside Out 
Microsoft Access 2003 Inside Out 
Building Microsoft Access Applications 
http://www.viescas.com/ 
(Paris, France) 




On Aug 29, 2017, at 3:29 AM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote: 



THis is the import code now and it works great!!! So Happy!!!!! 



Private Sub cmdImport_Click() 
Dim db As Database 
Dim rs As Recordset 
Dim iRecCount As Integer 

Dim intI As Integer 
Set db = CurrentDb() 
Set rs = db.OpenRecordset("tblDocumentMain") 

iRecCount = Me.FileList.ListCount 
For intI = 0 To Me.FileList.ListCount - 1 

rs.AddNew 
rs!DOCLOCATION = FileList.ItemData(intI) 
rs!DATEOFDOC = Date 
rs!DOCUSER_ID = glngThisEmpID 
rs!DOCTYPE = FileExtensionFromPath(FileList.ItemData(intI)) 
rs!ACTIVE = -1 
rs.Update 


Next intI 


MsgBox "You have imported " & CStr(iRecCount) & " records.", vbOKCancel 
rs.Close 
Set rs = Nothing 
Set db = Nothing 

Me!sfrmDocMain.Form.Requery 


End Sub 

But there is one more beast to slay!!!! I need to ignore files that already exists... So we don't get duplicated. 


With Warm Regards, 

Arthur D. Lorenzini 
IT System Manager 
Cheyenne River Housing Authority 
Wk.(605)964-4265 Ext. 130 
Fax (605)964-1070 

"Valar Dohaeris" 







From: John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> 
To: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> 
Sent: ‎Monday‎, ‎August‎ ‎28‎, ‎2017‎ ‎03‎:‎19‎:‎43‎ ‎PM 
Subject: Re: [MS_AccessPros] Adding file locations and filenames to a continous form 

Art- 


The ItemsSelected collection returns only those rows that are actually selected - either by the user or by you in code. To have multiple or all of the items selected, the MultiSelect property needs to be set to Simple or Extended, but then the List Box cannot be bound to anything because it will never have a Value other than Null. 

Why not just loop through all the rows? 

Dim intI As Integer 

For intI = 0 To Me.FileList.ItemCount - 1 
rs.AddNew 
rs!DOCLOCATION = FileList.ItemData(intI) 
rs!DATEOFDOC = Date 
rs.Update 
Next intI 

John Viescas, Author 
Effective SQL 
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out 
Microsoft Access 2007 Inside Out 
Microsoft Access 2003 Inside Out 
Building Microsoft Access Applications 
http://www.viescas.com/ 
(Paris, France) 




On Aug 28, 2017, at 9:36 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote: 



OK. Went a little different way. I added a list box FileList to frmMain. The code below fills the listbox correctly. 



Dim fDialog As Office.FileDialog 
Dim varFile As Variant 

' Clear listbox contents. ' 
Me.FileList.RowSource = "" 

' Set up the File Dialog. ' 
Set fDialog = Application.FileDialog(msoFileDialogFilePicker) 

With fDialog 

' Allow user to make multiple selections in dialog box ' 
.AllowMultiSelect = True 

' Set the title of the dialog box. ' 
.Title = "Please select one or more files" 

' Clear out the current filters, and add our own.' 
.Filters.Clear 
.Filters.Add "Access Databases", "*.MDB" 
.Filters.Add "Access Projects", "*.ADP" 
.Filters.Add "All Files", "*.*" 

' Show the dialog box. If the .Show method returns True, the ' 
' user picked at least one file. If the .Show method returns ' 
' False, the user clicked Cancel. ' 
If .Show = True Then 

'Loop through each file selected and add it to our list box. ' 
For Each varFile In .SelectedItems 
Me.FileList.AddItem varFile 
Next 

Else 
MsgBox "You clicked Cancel in the file dialog box." 
End If 
End With 

. 


I added a button called cmdImport to frmMain with the following code: 

Private Sub cmdImport_Click() 
Dim db As Database 
Dim rs As Recordset 

Set db = CurrentDb() 
Set rs = db.OpenRecordset("tblDocumentMain") 
For Each itm In FileList.ItemsSelected 
rs.AddNew 
rs!DOCLOCATION = FileList.ItemData(itm) 
rs!DATEOFDOC = Date 
rs.Update 
Next 
rs.Close 
Set rs = Nothing 
Set db = Nothing 

End Sub 


The issue is its not writing anything to the table.... 



With Warm Regards, 

Arthur D. Lorenzini 
IT System Manager 
Cheyenne River Housing Authority 
Wk.(605)964-4265 Ext. 130 
Fax (605)964-1070 

"Valar Dohaeris" 







From: John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> 
To: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> 
Sent: ‎Monday‎, ‎August‎ ‎28‎, ‎2017‎ ‎12‎:‎21‎:‎59‎ ‎PM 
Subject: Re: [MS_AccessPros] Adding file locations and filenames to a continous form 

Art- 


Just before your For Each, open a recordset to add rows to your table. 

Inside the For Each, instead of Debug.Print, use the .addnew and .update methods on the recordset to create new rows. 

John Viescas, Author 
Effective SQL 
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out 
Microsoft Access 2007 Inside Out 
Microsoft Access 2003 Inside Out 
Building Microsoft Access Applications 
http://www.viescas.com/ 
(Paris, France) 




On Aug 28, 2017, at 7:15 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote: 



OK I cam up with this... 

Dim fDialog As FileDialog, result As Integer 
Set fDialog = Application.FileDialog(msoFileDialogFilePicker) 

'IMPORTANT! 
fDialog.AllowMultiSelect = True 

'Optional FileDialog properties 
fDialog.title = "Select a file" 
fDialog.InitialFileName = "C:\" 
'Optional: Add filters 
fDialog.Filters.Clear 
fDialog.Filters.Add "Excel files", "*.xlsx" 
fDialog.Filters.Add "All files", "*.*" 

'Show the dialog. -1 means success! 
If fDialog.Show = -1 Then 
For Each it In fDialog.SelectedItems 
Debug.Print it 
Next it 
End If 

Which allows me to select multiple files but how do I move them to my table and subform? I know its in the FOr Each loop but not the syntax? 





With Warm Regards, 

Arthur D. Lorenzini 
IT System Manager 
Cheyenne River Housing Authority 
Wk.(605)964-4265 Ext. 130 
Fax (605)964-1070 

"Valar Dohaeris" 







From: John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> 
To: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> 
Sent: ‎Monday‎, ‎August‎ ‎28‎, ‎2017‎ ‎10‎:‎54‎:‎17‎ ‎AM 
Subject: Re: [MS_AccessPros] Adding file locations and filenames to a continous form 

Art- 


Do you not understand the code you posted earlier in the other thread? 

Dim strPath As String 

' If you want to use the Office FileDialog object, 
' comment out the following code and remove the 
' comments from the block below 
' ***** Begin ComDlg code 
' Establish a new ComDlg object 
With New ComDlg 
' Don't allow multiple files 
.AllowMultiSelect = False 
' Set the title of the dialog 
.DialogTitle = "Locate MTT picture File" 
' Set the default directory 
.Directory = CurrentProject.Path & "\Pictures\" 
' .. and file extension 
.Extension = "bmp" 
' .. but show all graphics files just in case 
.Filter = "Image Files (.bmp, .jpg, .gif, .pdf)|*.bmp;*.jpg;*.gif;*.pdf" 
' Tell the common dialog that the file and path must exist 
.ExistFlags = FileMustExist + PathMustExist 
If .ShowOpen Then 
strPath = .FileName ' <=========== 
Else 
Exit Sub 
End If 
End With 
' ***** End ComDlg code 



John Viescas, Author 
Effective SQL 
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out 
Microsoft Access 2007 Inside Out 
Microsoft Access 2003 Inside Out 
Building Microsoft Access Applications 
http://www.viescas.com/ 
(Paris, France) 




On Aug 28, 2017, at 5:05 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote: 



But how do I pull the information from th eOpenFile dialog or whatever? 


With Warm Regards, 

Arthur D. Lorenzini 
IT System Manager 
Cheyenne River Housing Authority 
Wk.(605)964-4265 Ext. 130 
Fax (605)964-1070 

"Valar Dohaeris" 







From: John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> 
To: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> 
Sent: ‎Monday‎, ‎August‎ ‎28‎, ‎2017‎ ‎09‎:‎19‎:‎31‎ ‎AM 
Subject: Re: [MS_AccessPros] Adding file locations and filenames to a continous form 

Art- 


If you want to save multiple files per row in tblDocumentMain, you'll need another table related on DOC_ID with one row per document. If you want to save just one file per row, use code similar to what you did for frmMTTNoteDetails. 

John Viescas, Author 
Effective SQL 
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out 
Microsoft Access 2007 Inside Out 
Microsoft Access 2003 Inside Out 
Building Microsoft Access Applications 
http://www.viescas.com/ 
(Paris, France) 




On Aug 28, 2017, at 4:07 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote: 



Table: tblDocumentMain Page: 1 
Columns 
Name Type Size 
DOC_ID Long Integer 4 
DateEntered Date With Time 8 
DATEOFDOC Date With Time 8 
DOCENTITY Short Text 255 
DOCFOLDER Short Text 255 
DOCTYPE Short Text 255 
DOCSOURCE Short Text 255 
Importance Short Text 255 
DOCREF Short Text 255 
Notes Long Text - 
UpdateDateTime Date With Time 8 
DOCGROUP_ID Long Integer 4 
DOCTREENODE_ID Long Integer 4 
DOCUSER_ID Long Integer 4 
RESPOSNAME Short Text 255 
OK. Open file dialog will be accessed via a command button. The table structure is defined as follows: 

The pertinent fields for this discussion are DOCFOLDER which should contain the path and filename. 


With Warm Regards, 

Arthur D. Lorenzini 
IT System Manager 
Cheyenne River Housing Authority 
Wk.(605)964-4265 Ext. 130 
Fax (605)964-1070 

"Valar Dohaeris" 







From: John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> 
To: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> 
Sent: ‎Monday‎, ‎August‎ ‎28‎, ‎2017‎ ‎08‎:‎57‎:‎48‎ ‎AM 
Subject: Re: [MS_AccessPros] Adding file locations and filenames to a continous form 

Art- 


What is the structure of the tables involved, and where do you plan to add the files? 

John Viescas, Author 
Effective SQL 
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out 
Microsoft Access 2007 Inside Out 
Microsoft Access 2003 Inside Out 
Building Microsoft Access Applications 
http://www.viescas.com/ 
(Paris, France) 




On Aug 28, 2017, at 3:18 PM, dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote: 




I have a continuous form call sfrmDocumentList which records source is a table called tblDocuments. I need to open the file dialog and be able to add multiple files into the subform and also updating the table with the locations... 



THank you, 



Art Lorenzini 

Sioux Falls, SD 

__._,_.___

---

Posted by: crystal 8 <strive4peace2008@yahoo.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (27)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___