Select a table from the Form Module in the Standard Module

[Ben_Entrew]

Dear all,

I got the following issue:

I imported a Table named Temp with a Import Button.
During the import I asked the USER to give also a reporting month as a variable MyValue (String). Data Table has a blank column named reporting month.
How can I fill this column in this table Temp out of the Standard Module ?

I try the following:

DoCmd.RunSQL "UPDATE Form_Data Import_Temp.[Reporting_Month] SET temp.[Reporting_Month] = MyValue "

My Form Module is called Form_Data Import
I set the My Value as Public String Variable in the Standard Module with the name Data Changes.

Thanks in advance guys.

 

[pbaldy]

Try

DoCmd.RunSQL "UPDATE Form_Data Import_Temp.[Reporting_Month] SET temp.[Reporting_Month] = '" & MyValue & "'"

Delete the bits in red if the field is numeric.

 

[Ben_Entrew]

Thanks for the quick respons Paul,

unfortunately it's still not working. I've tried both also without the red signs.

 

[pbaldy]

Are you following the syntax of

UPDATE TableName
SET FieldName = Whatever

The first part doesn't look like a table name.

 

[Ben_Entrew]

I tried this:
DoCmd.RunSQL "UPDATE Temp SET Reporting_Month = '" & MyValue & "'"

Now it asks me to give a parameter for Reporting_Month.

When I type in something it comes the Runtime Error 3037:

Operation must use an updateable query.


Somehow it doesn't recognize this MyValue variable...

 

[pbaldy]

The parameter prompt is Access not being able to find something. If the field name has an inadvisable space instead of the underscore:

DoCmd.RunSQL "UPDATE Temp SET [Reporting Month] = '" & MyValue & "'"

 

[Ben_Entrew]

Sorry,

really don't wanna be a pest.

Now it doesn't aks me for a Parameter, it runs through.

However it doesn't fill in the records under column Reporting Month with the value MyValue. The column is still blank.

 

[pbaldy]

No worries. I'd start with this method to double check the SQL:

http://www.baldyweb.com/ImmediateWindow.htm

Can you post the db here?

 

[Ben_Entrew]

I attached it.

 

[pbaldy]

For starters, any time you set warnings to false don't forget to set them back to true. I didn't test the full import routine, but tweaked the function and ran it, and it updated the table as expected:

Public Sub ChangeTable()
DoCmd.SetWarnings False
'DoCmd.RunSQL "ALTER TABLE Temp ADD COLUMN Reporting_Month3 Text "
'Aus Spass zwei spalten miteinander multiplizieren und die Updatewarnung wird nicht angezeigt
MyValue = "Paul"
DoCmd.RunSQL "UPDATE Temp SET Temp.[Reporting Month] = '" & MyValue & "'"
DoCmd.SetWarnings True

End Sub

I just noticed that you declare MyValue both as public and within the Daten_Import_Click() routine, which I'm surprised the program allowed. Get rid of the one in the sub and try it.

 

[Ben_Entrew]

Now it works,

thank you very very much. Now I can stand in front my weired female manager tomorrow. You saved me.
Thanks again.

Have a nice night Paul.

Regards,
Ben

 

[pbaldy]

Happy to help Ben, and you too!