Jay Williams
asked on
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 SubASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
DoCmd.RunSQL "UPDATE PDMObjectsT SET Source= '" & strPathFile & "' WHERE Source IS NULL"
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
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 SubSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You guys are good--and FAST! Thanks!
Why did you accept that as the answer? Rey gave you exactly the same answer in his first post.
ASKER
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.
@Jay Williams
you can ask to reopen the thread by clicking on the "Request Attention" located just below your original post.
you can ask to reopen the thread by clicking on the "Request Attention" located just below your original post.
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?