UPDATE statement where table name is based on unbound textbox (VBA/Access)

[bre1603]

I'm trying to run an UPDATE statement on a table that changes every time the user runs the code.

Here's some info:
(On a form) The user imports a text file, using a button to open a file browser window and another button import to file after it's been selected. An unbound textbox requires the user to name the table before importing. This table name control (tb_Name) is used when exporting by a third button, which exports the file back to text while making some minor format changes.

The big thing I need done is to remove some extra characters in several columns of the data before it is exported. But since my table name changes every time, I can't just run a saved query. I thought I could write the SQL to run before exporting in the OnClick Event of btn_Export_Click, and account for the changing value of the textbox tb_Name. However, I'm unable to figure out how to draw tb_Name into the Update Statement successfully.

Here's what I have so far:

Dim strSQL As String
 
If IsNull(Me.tb_Name) Then
    MsgBox "Please re-enter the table name you wish to export to text."
    Else:
    strSQL = "UPDATE Me.tb_Name SET [SVC_FRM_DT] = Left([SVC_FRM_DT],Len([SVC_FRM_DT])-11)," & _
        "[SVC_TO_DT] = Left([SVC_TO_DT],Len([SVC_TO_DT])-11)," & _
        "[CKPY_PAY_DT] = Left([CKPY_PAY_DT],Len([CKPY_PAY_DT])-11)," & _
        "[BLBL_FNDG_FROM_DT] = Left([BLBL_FNDG_FROM_DT],Len([BLBL_FNDG_FROM_DT])-11)," & _
        "[BLBL_FNDG_THRU_DT] = Left([BLBL_FNDG_THRU_DT],Len([BLBL_FNDG_THRU_DT])-11)," & _
        "[BLIV_CREATE_DTM] = Left([BLIV_CREATE_DTM],Len([BLIV_CREATE_DTM])-12);"
    CurrentDb.Execute strSQL, dbFailOnError
 
    DoCmd.TransferText acExportFixed, "Export_Text_Files", Me.tb_Name, "I:\Regence UMP claims\Mar 2011\Regence Claims_" & Me.tb_Name & ".txt", False
    MsgBox "Export Complete! The file is here: " & "I:\Regence UMP claims\Mar 2011\Regence Claims_" & Me.tb_Name & ".txt"
End If

Hopefully I'm not far off. Any help would be appreciated. Thanks!

[bre1603]

So I solved this problem on my own.

I haven't had much luck with my questions lately - are my issues too boring for the masses? :/

Anyway, I was able to reference the control as the table in the UPDATE statement. It was a matter of formating that reference to make sense to Access as to what I wanted.

Line 6 from the above code now reads:

strSQL = "UPDATE [" & Me.tb_Name & "] SET [SVC_FRM_DT] = Left([SVC_FRM_DT],Len([SVC_FRM_DT])-11)," & _

The rest of my code works fine and is unchanged.

Although I didn't get an external answer to my question, sometimes it just helps to talk out loud. Thanks for listening. :)

[NeoPa]

It looks like you've already discovered the problem here. Me is a VBA reference specific to the module the code runs in. If that is included in a string and passed to the SQL engine then it is neither VBA nor the local module.

In some circumstances a fuller reference to a control on your form is possible. In this case though, the SQL syntax expects a literal string to represent the name of the table. Your solution is the most appropriate way to handle this type of scenario.