?
Solved

How do I get the "Source" field updated to the name of the record's import file?

Posted on 2015-01-21
10
Medium Priority
?
218 Views
Last Modified: 2015-01-29
This loop works to import all the files in the folder, but once the file is imported, I need the "Source field of each record in the import updated to the name of the import file (extension not needed).  How do I reference that?

Private Sub RefreshPDMObjectsBtn_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM PDMObjectsT"
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

blnHasFieldNames = True

strPath = "G:\XE_ECMs\IPP Sharing Development\Audit Data\"

strTable = "PDMObjectsT"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
      strPathFile = strPath & strFile
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
            strTable, strPathFile, blnHasFieldNames
      DoCmd.RunSQL "UPDATE PDMObjectsT SET Source= 'FileNameGoesHere' WHERE Source IS NULL"
'       Kill strPathFile

      strFile = Dir()
Loop

End Sub

Open in new window

0
Comment
Question by:Jay Williams
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
10 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 40562286
change this

DoCmd.RunSQL "UPDATE PDMObjectsT SET Source= 'FileNameGoesHere' WHERE Source IS NULL"

with

DoCmd.RunSQL "UPDATE PDMObjectsT SET Source= '" & strFile &"' WHERE Source IS NULL"

or

Currentdb.execute  "UPDATE PDMObjectsT SET Source= '" & strFile &"' WHERE Source IS NULL", dbfailonerror
0
 
LVL 85
ID: 40562290
Changing the following line would update the table:

DoCmd.RunSQL "UPDATE PDMObjectsT SET Source= '" & strPathFile & "' WHERE Source IS NULL"

However this would update ALL records in that table. Is that what you want?
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40562291
DoCmd.RunSQL "UPDATE PDMObjectsT SET Source= '" & strPathFile  & "' WHERE Source IS NULL"
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 17

Expert Comment

by:Barry Cunney
ID: 40562294
Hi Jay,
If you embed the strFile variable(this variable holds the file name) into your UPDATE statement it should give the required
so concatenate this variable into the UPDATE statement
Private Sub RefreshPDMObjectsBtn_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM PDMObjectsT"
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

blnHasFieldNames = True

strPath = "G:\XE_ECMs\IPP Sharing Development\Audit Data\"

strTable = "PDMObjectsT"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
      strPathFile = strPath & strFile
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
            strTable, strPathFile, blnHasFieldNames
      >> DoCmd.RunSQL "UPDATE PDMObjectsT SET Source= '" & strFile & "' WHERE Source IS NULL"
'       Kill strPathFile

      strFile = Dir()
Loop

End Sub

Open in new window

0
 
LVL 17

Assisted Solution

by:Barry Cunney
Barry Cunney earned 1000 total points
ID: 40562311
To get the file name without the extension you can do something like
Dim  strFileNameWithoutExtension$
 strFileNameWithoutExtension$ = Left(strFile, (InStrRev(strFile, ".", -1, vbTextCompare) - 1))

so the above looks for the . in the file name and gets everything to the left of this

you can then use  strFileNameWithoutExtension$ in the UPDATE statement instead of strFile
0
 

Author Comment

by:Jay Williams
ID: 40562341
You guys are good--and FAST!  Thanks!
0
 
LVL 85
ID: 40562358
Why did you accept that as the answer? Rey gave you exactly the same answer in his first post.
0
 

Author Comment

by:Jay Williams
ID: 40562376
I meant to accept multiple solutions, but I'm new and don't know how this thing works yet.  I'm here to learn--obviously.  Don't want to ignore anyone.  Please accept my sincere apologies and help me correct it if possible.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40564167
@Jay Williams

you can ask to reopen the thread by clicking on the "Request Attention" located just below your original post.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

762 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