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

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

Jay WilliamsOwnerAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
PatHartmanCommented:
DoCmd.RunSQL "UPDATE PDMObjectsT SET Source= '" & strPathFile  & "' WHERE Source IS NULL"
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Barry CunneyCommented:
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
 
Barry CunneyCommented:
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
 
Jay WilliamsOwnerAuthor Commented:
You guys are good--and FAST!  Thanks!
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Why did you accept that as the answer? Rey gave you exactly the same answer in his first post.
0
 
Jay WilliamsOwnerAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
@Jay Williams

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

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.