Converting SQL to VBA

[soggycashew]

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:

UPDATE tbluEmployees SET tbluEmployees.SupervisorID = [Forms]![frm_UpdateSupInfo]![cboSupervisor]
WHERE (((tbluEmployees.SupervisorID)=[forms]![frm_UpdateSupInfo]![txtSupervisorID]));
 

What I came up with:

Dim strSQL As String
 
strSQL = " UPDATE tbluEmployees SET tbluEmployees.SupervisorID = [Forms]![frm_UpdateSupInfo]![cboSupervisor] "
strSQL = strSQL & "WHERE (((tbluEmployees.SupervisorID)=[forms]![frm_UpdateSupInfo]![txtSupervisorID]))"
  'verify the SQL works
 Debug.Print strSQL
 
'CurrentDb.Execute strSQL

[NeoPa]

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.

[cactusdata]

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

strSQL = "UPDATE tbluEmployees SET tbluEmployees.SupervisorID = " & [Forms]![frm_UpdateSupInfo]![cboSupervisor] & " "
strSQL = strSQL & "WHERE tbluEmployees.SupervisorID = " & [Forms]![frm_UpdateSupInfo]![txtSupervisorID] & ""

or, if frm_UpdateSupInfo is the current form (Me):

strSQL = "UPDATE tbluEmployees SET tbluEmployees.SupervisorID = " & Me![cboSupervisor].Value & " "
strSQL = strSQL & "WHERE tbluEmployees.SupervisorID = " & Me![txtSupervisorID].Value & ""

[isladogs]

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

[soggycashew]

Thanks all here is what I used...

strSQL = "UPDATE tbluEmployees SET [SupervisorID]=" & [Forms]![frm_UpdateSupInfo]![cboGainingSupervisor]
    strSQL = strSQL & " WHERE [SupervisorID]=" & [Forms]![frm_UpdateSupInfo]![txtSupervisorID]

[NeoPa]

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 :

With Me
    strSQL = "UPDATE [tbluEmployees] " _
           & "SET    [SupervisorID]=%T " _
           & "WHERE  ([SupervisorID]=%F)"
    strSQL = Replace(strSQL, "%F", .txtSupervisorID)
    strSQL = Replace(strSQL, "%T", .cboGainingSupervisor)
End With

With Me
    strSQL = "UPDATE [tbluEmployees] " _
           & "SET    [SupervisorID]=" & .cboGainingSupervisor & " " _
           & "WHERE  ([SupervisorID]=" & .txtSupervisorID & ")"
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.