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: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (16) |
Tidak ada komentar:
Posting Komentar