469,156 Members | 2,136 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,156 developers. It's quick & easy.

Converting SQL to VBA

10 Byte
Hello, I have a test update query that I created and need to convert it to VBA - SQL so I can run it using a button in my form plus I need to learn this anyway... First is there a website that explains this? Second I have trouble with things like referring to controls on a form like:

SupervisorID =[forms]![frm_UpdateSupInfo]![txtSupervisorID]

Im using this behind my form so can I use Me.txtSupervisorID or do I have to do the whole thing?

TEST Query SQL that works:
Expand|Select|Wrap|Line Numbers
  1. UPDATE tbluEmployees SET tbluEmployees.SupervisorID = [Forms]![frm_UpdateSupInfo]![cboSupervisor]
  2. WHERE (((tbluEmployees.SupervisorID)=[forms]![frm_UpdateSupInfo]![txtSupervisorID]));
  3.  

What I came up with:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = " UPDATE tbluEmployees SET tbluEmployees.SupervisorID = [Forms]![frm_UpdateSupInfo]![cboSupervisor] "
  4. strSQL = strSQL & "WHERE (((tbluEmployees.SupervisorID)=[forms]![frm_UpdateSupInfo]![txtSupervisorID]))"
  5.   'verify the SQL works
  6.  Debug.Print strSQL
  7.  
  8. 'CurrentDb.Execute strSQL
2 Weeks Ago #1

✓ answered by cactusdata

When building SQL in VBA, you need to concatenate the values:

Expand|Select|Wrap|Line Numbers
  1. strSQL = "UPDATE tbluEmployees SET tbluEmployees.SupervisorID = " & [Forms]![frm_UpdateSupInfo]![cboSupervisor] & " "
  2. strSQL = strSQL & "WHERE tbluEmployees.SupervisorID = " & [Forms]![frm_UpdateSupInfo]![txtSupervisorID] & ""
or, if frm_UpdateSupInfo is the current form (Me):

Expand|Select|Wrap|Line Numbers
  1. strSQL = "UPDATE tbluEmployees SET tbluEmployees.SupervisorID = " & Me![cboSupervisor].Value & " "
  2. strSQL = strSQL & "WHERE tbluEmployees.SupervisorID = " & Me![txtSupervisorID].Value & ""

5 4135
NeoPa
32,165 Expert Mod 16PB
May I suggest you start again at the beginning and assume we don't already know what you're thinking. After that, and before posting, read it back to yourself to check it makes sense.

SoggyCashew:
Im using this behind my form so can I use Me.txtSupervisorID or do I have to do the whole thing?
We only allow one question per thread so I'll answer this one here (as it actually makes sense). Re-do the rest elsewhere (Another thread.) as described above.

Yes. Within the module of the Form, the associated Form can always be referenced more simply as Me. Thus Me.txtSupervisorID would make sense based on a Control named [txtSupervisorID].

That said, and we can deal with that in more detail when we have a question that actually makes sense, this is only within the context of the VBA code itself. It won't be recognised within a string and it certainly won't be referenced by any SQL you build - even if the building is done within the VBA.
2 Weeks Ago #2
cactusdata
177 Expert 128KB
When building SQL in VBA, you need to concatenate the values:

Expand|Select|Wrap|Line Numbers
  1. strSQL = "UPDATE tbluEmployees SET tbluEmployees.SupervisorID = " & [Forms]![frm_UpdateSupInfo]![cboSupervisor] & " "
  2. strSQL = strSQL & "WHERE tbluEmployees.SupervisorID = " & [Forms]![frm_UpdateSupInfo]![txtSupervisorID] & ""
or, if frm_UpdateSupInfo is the current form (Me):

Expand|Select|Wrap|Line Numbers
  1. strSQL = "UPDATE tbluEmployees SET tbluEmployees.SupervisorID = " & Me![cboSupervisor].Value & " "
  2. strSQL = strSQL & "WHERE tbluEmployees.SupervisorID = " & Me![txtSupervisorID].Value & ""
2 Weeks Ago #3
isladogs
289 Expert 256MB
Agree with using the Me. notation as the code will be used in a form event.

You may find my SQL to VBA converter useful. See SQL to VBA and back again
Attached Files
File Type: zip SQL2VBA.zip (62.5 KB, 8 views)
2 Weeks Ago #4
soggycashew
10 Byte
Thanks all here is what I used...

Expand|Select|Wrap|Line Numbers
  1. strSQL = "UPDATE tbluEmployees SET [SupervisorID]=" & [Forms]![frm_UpdateSupInfo]![cboGainingSupervisor]
  2.     strSQL = strSQL & " WHERE [SupervisorID]=" & [Forms]![frm_UpdateSupInfo]![txtSupervisorID]
2 Weeks Ago #5
NeoPa
32,165 Expert Mod 16PB
Hi SoggyCashew.

From your last post it's more clear what you're after. While there's no doubt your code will work as it is, it does seem as if you haven't quite got what we were trying to say about the use of Me.

Here are a couple of examples that use Replace() and the more standard simple string concatenation approaches :
Expand|Select|Wrap|Line Numbers
  1. With Me
  2.     strSQL = "UPDATE [tbluEmployees] " _
  3.            & "SET    [SupervisorID]=%T " _
  4.            & "WHERE  ([SupervisorID]=%F)"
  5.     strSQL = Replace(strSQL, "%F", .txtSupervisorID)
  6.     strSQL = Replace(strSQL, "%T", .cboGainingSupervisor)
  7. End With
Expand|Select|Wrap|Line Numbers
  1. With Me
  2.     strSQL = "UPDATE [tbluEmployees] " _
  3.            & "SET    [SupervisorID]=" & .cboGainingSupervisor & " " _
  4.            & "WHERE  ([SupervisorID]=" & .txtSupervisorID & ")"
  5. End With
Where the simple dot (.) method is used that is because of the With context set up. If you skipped the With Me & End With lines then these would simply need to be done explicitly as Me.cboGainingSupervisor & Me.txtSupervisorID.

NB. While it may seem strange to add the With lines as it creates more code, it does actually save on preparing the object for use. More important in code where there are a number of object references to process which can all be saved by using that approach.

I hope that helps you to understand more clearly what we've been telling you.
1 Week Ago #6

Post your reply

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

Similar topics

4 posts views Thread by mustafa | last post: by
8 posts views Thread by prabha | last post: by
2 posts views Thread by Map Reader | last post: by
12 posts views Thread by Frederik Vanderhaeghe | last post: by
7 posts views Thread by Tor Aadnevik | last post: by
4 posts views Thread by gg9h0st | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.