By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,136 Members | 768 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,136 IT Pros & Developers. It's quick & easy.

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

bre1603
P: 39
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:

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. If IsNull(Me.tb_Name) Then
  4.     MsgBox "Please re-enter the table name you wish to export to text."
  5.     Else:
  6.     strSQL = "UPDATE Me.tb_Name SET [SVC_FRM_DT] = Left([SVC_FRM_DT],Len([SVC_FRM_DT])-11)," & _
  7.         "[SVC_TO_DT] = Left([SVC_TO_DT],Len([SVC_TO_DT])-11)," & _
  8.         "[CKPY_PAY_DT] = Left([CKPY_PAY_DT],Len([CKPY_PAY_DT])-11)," & _
  9.         "[BLBL_FNDG_FROM_DT] = Left([BLBL_FNDG_FROM_DT],Len([BLBL_FNDG_FROM_DT])-11)," & _
  10.         "[BLBL_FNDG_THRU_DT] = Left([BLBL_FNDG_THRU_DT],Len([BLBL_FNDG_THRU_DT])-11)," & _
  11.         "[BLIV_CREATE_DTM] = Left([BLIV_CREATE_DTM],Len([BLIV_CREATE_DTM])-12);"
  12.     CurrentDb.Execute strSQL, dbFailOnError
  13.  
  14.     DoCmd.TransferText acExportFixed, "Export_Text_Files", Me.tb_Name, "I:\Regence UMP claims\Mar 2011\Regence Claims_" & Me.tb_Name & ".txt", False
  15.     MsgBox "Export Complete! The file is here: " & "I:\Regence UMP claims\Mar 2011\Regence Claims_" & Me.tb_Name & ".txt"
  16. End If
Hopefully I'm not far off. Any help would be appreciated. Thanks!
May 4 '11 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. 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. :)

Share this Question
Share on Google+
2 Replies

bre1603
P: 39
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:
Expand|Select|Wrap|Line Numbers
  1. 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. :)
May 4 '11 #2

NeoPa
Expert Mod 15k+
P: 31,770
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.
May 10 '11 #3

Post your reply

Sign in to post your reply or Sign up for a free account.