Ben_Entrew
Registered User.
- Local time
- Today, 08:07
- Joined
- Dec 3, 2013
- Messages
- 177
Dear all,
I'm a greenhorn belongs to Access VBA.
My intention was that a user can select a reporting month, then to select the import a file, which is saved as ACTUAL_TNS_MyValue.
So far so good everything works. However if I want to work with this table through SQL UPDATE I can't reference to that table.
Thank you in advance guys.
Here the code:
Public Sub Import_Turnover_Click()
Dim filelocation As Variant
Dim f As Object
Dim Message As String, Title As String, Default As String, MyValue As String
'Ask User for Reporting month
Message = "Enter a reporting month (mmyyyy)" ' Set prompt.
Title = "Reporting Month" ' Set title.
Default = " " ' Set default.
' Display dialog box at position 5000, 5000.
MyValue = InputBox(Message, Title, Default, 5000, 5000)
'Opens window to pick a file; filelocation = path+filename
Set f = Application.FileDialog(msoFileDialogFilePicker)
f.show
With f
.allowmultiselect = False
.Title = "Import Total Net Sales file of the reporting month :"
End With
filelocation = f.SelectedItems(1)
DoCmd.TransferText acImportDelim, "Test Import Specification", "Actual_TNS_" & MyValue, filelocation, yes
'Change the signs of Quantity numbers
DoCmd.RunSQL "UPDATE Actual_TNS_MyValue SET Actual_TNS_MyValue.[Quantity] = Actual_TNS_MyValue.[Quantity] * (-1)"
End Sub
I'm a greenhorn belongs to Access VBA.
My intention was that a user can select a reporting month, then to select the import a file, which is saved as ACTUAL_TNS_MyValue.
So far so good everything works. However if I want to work with this table through SQL UPDATE I can't reference to that table.
Thank you in advance guys.
Here the code:
Public Sub Import_Turnover_Click()
Dim filelocation As Variant
Dim f As Object
Dim Message As String, Title As String, Default As String, MyValue As String
'Ask User for Reporting month
Message = "Enter a reporting month (mmyyyy)" ' Set prompt.
Title = "Reporting Month" ' Set title.
Default = " " ' Set default.
' Display dialog box at position 5000, 5000.
MyValue = InputBox(Message, Title, Default, 5000, 5000)
'Opens window to pick a file; filelocation = path+filename
Set f = Application.FileDialog(msoFileDialogFilePicker)
f.show
With f
.allowmultiselect = False
.Title = "Import Total Net Sales file of the reporting month :"
End With
filelocation = f.SelectedItems(1)
DoCmd.TransferText acImportDelim, "Test Import Specification", "Actual_TNS_" & MyValue, filelocation, yes
'Change the signs of Quantity numbers
DoCmd.RunSQL "UPDATE Actual_TNS_MyValue SET Actual_TNS_MyValue.[Quantity] = Actual_TNS_MyValue.[Quantity] * (-1)"
End Sub