SQL Update with a variable table name

[Ben_Entrew]

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

 

[pr2-eugin]

Ben_Entrew, Welcome to AWF

You should have concatenated the MyValue as you have in the Import statement..

Dim tmpSQL As String
tmpSQL = "UPDATE Actual_TNS_[COLOR=Red][B]" & MyValue & "[/B][/COLOR] SET Actual_TNS_MyValue.[Quantity] = Actual_TNS_[COLOR=Red][B]" & MyValue & "[/B][/COLOR].[Quantity] * (-1)" 
DoCmd.RunSQL tmpSQL

 

[Ben_Entrew]

Hello Paul Eugin,

first of all thanks for the quick answer.
However it doesn't work out yet. Now it asks me to give a parameter for

Actual_TNS_102013.Quantity

and then for

Actual_TNS_MyValue.Quantity



Any idea how I can use this MyValue in a different Module, don't wanna to blow up the Form Module with some other operations later on?

 

[pr2-eugin]

Okay I missed another Actual_TNS bit.. Try the following code..

Dim tmpSQL As String
tmpSQL = "UPDATE Actual_TNS_[COLOR=Red][B]" & MyValue & "[/B][/COLOR] SET Actual_TNS_[COLOR=Red][B]" & MyValue & "[/B][/COLOR].[Quantity] = Actual_TNS_[COLOR=Red][B]" & MyValue & "[/B][/COLOR].[Quantity] * (-1)" 
DoCmd.RunSQL tmpSQL

To use the MyValue throughout, declare the variable as Public in a Standard module, not behind the Form module.

 

[Ben_Entrew]

Thanks, now it works out.

I will try now the Public Variable declaration

Thanks again.

 

[pr2-eugin]

No problemo.. If you get stuck post back we will try to help you out.. Good Luck !