473,574 Members | 2,660 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Converting SQL to VBA

10 New Member
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_UpdateSupIn fo]![txtSupervisorID]

Im using this behind my form so can I use Me.txtSuperviso rID 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]));

What I came up with:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  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
  8. 'CurrentDb.Execute strSQL
Sep 9 '21 #1
5 5723
32,564 Recognized Expert Moderator MVP
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.

Im using this behind my form so can I use Me.txtSuperviso rID 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.txtSuperviso rID 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.
Sep 10 '21 #2
214 Recognized Expert New Member
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_UpdateSupIn fo 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 & ""
Sep 10 '21 #3
457 Recognized Expert Moderator Contributor
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, 60 views)
Sep 10 '21 #4
10 New Member
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]
Sep 13 '21 #5
32,564 Recognized Expert Moderator MVP
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.cboGainingSu pervisor & Me.txtSuperviso rID.

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.
Sep 13 '21 #6

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

Similar topics

by: mustafa | last post by:
Dear sir , I have built my application in visual basic 6.0 and crystal Report8.5 , Now i migrated my application to VB.net using the upgrade wizard.My visual basic form is upgraded to vb.net but my .dsr file that i created using crystalReport is not migrated , when i try to convert it into .rpt file using add new item in Vb.net Envs it...
by: Armand Karlsen | last post by:
I have a website ( http://www.zen62775.zen.co.uk ) that I made HTML 4.01 Transitional and CSS compliant, and I'm thinking of converting it into XHTML to learn a little about it. Which XHTML variant would you recommend? The w3c HTML validator mentions XHTML 1.0 Transitional, Basic, Strict, and XHTML 1.1. Would I be able to make my existing CSS...
by: prabha | last post by:
Hello Everybody, I have to conert the word doc to multiple html files,according to the templates in the word doc. I had converted the word to xml.Also through Exsl ,had finished the multiple output html files. The problem is while reading through the worddoc paragraph,the special characters are not identified. So in the xml file,it's...
by: Robert | last post by:
I have a series of web applications (configured as separate applications) on a server. There is a main application at the root and then several virtual directories that are independant applications. I am testing an upgrade of all of the sites and have converted the main root site...although not necessarily fixed any issues. I move on...
by: Mary | last post by:
Hi, Does anyone know of any software out there that would convert an application written in VBScript to either VB.NET or C#/C++ quite quickly for me, or will I have to re-write the application myself. Any help much appreciated.
by: Map Reader | last post by:
Greetings, I am converting an old VB6 application to use .NET. One of the old controls loads icons from the disk and displays them. However, the transparent color turns to blue somewhere in the process. I narrowed it down to the conversion to IPictureDisp by first loading and saving the icon as a bitmap with no problems; and then repeating...
by: Frederik Vanderhaeghe | last post by:
Hi, I have a problem converting text to a double. Why doesn't the code work: If Not (txtdocbedrag.Text = "") Then Select Case ddlBedrag.SelectedIndex Case 0 Case 1
by: Tor Aadnevik | last post by:
Hi, I have a problem converting values from Single to double. eg. When the Single value 12.19 is converted to double, the result is 12.1899995803833. Anyone know how to avoid this? Regards Totto
by: gg9h0st | last post by:
i'm a newbie studying php. i was into array part on tutorial and it says i'll get an array having keys that from member variable's name by converting an object to array. i guessed "i can get public members but not protected, private, static members"
by: shenanwei | last post by:
DB2 V8.2 on AIX, type II index is created. I see this from deadlock event monitor. 5) Deadlocked Connection ... Participant no.: 2 Lock wait start time: 09/18/2006 23:04:09.911774 ...... Deadlocked Statement: Type : Dynamic Operation: Execute
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.