473,320 Members | 1,600 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

VBA using SQL

Hello all,

I am trying to write a code in Access VBA usin SQL syntax. It should work thuis way : when a user changes a specific field in a form, his user name should be added into the table and row, where there was the change. I use event "after update". The codes is here:
Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub comment_AfterUpdate()
  3.  
  4. SQL "UPDATE dbo_mf_comment SET [user_id] = '" & Environ("username") & "' " _
  5.      & " FROM dbo_mf_comment JOIN (SELECT dbo_mf_comment.mf_id, MAX(dbo_mf_comment.[date]) AS MaxDate FROM dbo_mf_comment GROUP BY dbo_mf_comment.mf_id) AS newComment ON dbo_mf_comment.mf_id =  '" & Me.mf_id & "' AND  newComment.MaxDate = dbo_mf_comment.[date];"
  6.  
  7. End Sub
  8.  
  9.  
  10. Sub SQL(strSQL, Optional dbgprnt)
  11.     If Not IsMissing(dbgprnt) Then Debug.Print strSQL
  12.     DoCmd.SetWarnings False
  13.     If Nz(strSQL, "") <> "" Then DoCmd.RunSQL strSQL
  14.     DoCmd.SetWarnings True
  15. End Sub
  16.  
There is still some syntax error and I can't see it.
Can you help?

Martina
Nov 11 '11 #1

✓ answered by sierra7

Hi again,
If you go to SQL Management studio and set the default value of the field to hold the user name = 'suser_sname()', it will be automatically updated each time a new comment is added.
I can't find my code to update the name after a change but will get back.
On your own method;
1) I thought the syntax for a correlated sub-query required spercifying an alias. I have never used them (CSQ's) in Access but you might check.

2) I can't see why you need to match dates but this could also be the source of a problem. To start with Access/VBA expects to see hashes around dates so your code might read as follows
Expand|Select|Wrap|Line Numbers
  1. ' AND newComment.MaxDate = #" & dbo_mf_comment.[date] & "#;" 
  2.  
3) I would not trust the Access rendering of the date to match SQL interpretation. For a simple date (without time)it's ok, but you need accurate time to the second. You are passing the data back and forth through the ODBC link and if the milli-seconds parts don't match then the find will fail.
S7

13 2238
ADezii
8,834 Expert 8TB
I don't see where you are Calling the SQL() Sub-Routine and passing it the String Argument and possibly the Optional Parameter.
Nov 11 '11 #2
I don't know if I understand your reply correctly. The SQL is run after the "SQL". I use this in other forms and it works. Here I get the same error even if i don't use the Sub SQL but straight DoCmd.RunSQL (" ... ;"). I get runtime error 3075 - syntax error (missing operator).
Nov 11 '11 #3
ADezii
8,834 Expert 8TB
It appears that you have 2 instances where Double Spaces (" ") exist within the SQL String, try removiing them, either manually, or programmatically (via Line# 8):
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "UPDATE dbo_mf_comment SET [user_id] = '" & Environ("username") & "' " & _
  4.          "FROM dbo_mf_comment JOIN (SELECT dbo_mf_comment.mf_id, MAX(dbo_mf_comment.[date]) AS MaxDate " & _
  5.          "FROM dbo_mf_comment GROUP BY dbo_mf_comment.mf_id) AS newComment ON dbo_mf_comment.mf_id = '" & _
  6.           Me.mf_id & "' AND newComment.MaxDate = dbo_mf_comment.[date];"
  7.  
  8. strSQL = Replace(strSQL, "  ", " ")
  9.  
  10. DoCmd.RunSQL strSQL, dbFailOnError
Nov 11 '11 #4
Thanks a lot. But it does not work still.. I tried to use your suggestion but the same error appeared. Maybe I will postpone this for Monday when my brain will be relaxed after the weekend:) Because I am out of ideas now...
Nov 11 '11 #5
NeoPa
32,556 Expert Mod 16PB
martinaPar:
I am trying to write a code in Access VBA usin SQL syntax.
I don't understand why this discussion.

The immediate response to the quoted text is simply that such a thing makes no sense and is impossible. If we start from that basic understanding we can perhaps determine what exactly is required, or if you prefer, what the question should have been.

I expect communication may be easier from that point.
Nov 11 '11 #6
Hi NeoPa, I am not sure what you are asking for. I have an application used by a few users. They run our process through it. They can add a comment to some cases and this comment is inserted to a table (dbo_mf_comment) in SQL server. I need to find this record (the last one - that is why I am looking for the last date of a specific case - mf_id) and add user name. What else you need to know?
Martina
Nov 14 '11 #7
sierra7
446 Expert 256MB
Martina,
I can't understand your SQL. Does it work OK if you change it to a SELECT statement and pop the ID into a MessageBox?

I would not do it this way anyway. Just write a trigger on the server.

S7
Nov 14 '11 #8
Hi,
it works in SQL management studio. In the subquery MaxDate it finds the last record for each mf_id (which was inserted through the form by the user, so this is "after update event" of the form) a then it adds user name to "user_name" field in the same table. Maybe it is not the most effective way to do this. But I am quit a beginner, especially in VBA.
Martina
Nov 14 '11 #9
sierra7
446 Expert 256MB
Hi again,
If you go to SQL Management studio and set the default value of the field to hold the user name = 'suser_sname()', it will be automatically updated each time a new comment is added.
I can't find my code to update the name after a change but will get back.
On your own method;
1) I thought the syntax for a correlated sub-query required spercifying an alias. I have never used them (CSQ's) in Access but you might check.

2) I can't see why you need to match dates but this could also be the source of a problem. To start with Access/VBA expects to see hashes around dates so your code might read as follows
Expand|Select|Wrap|Line Numbers
  1. ' AND newComment.MaxDate = #" & dbo_mf_comment.[date] & "#;" 
  2.  
3) I would not trust the Access rendering of the date to match SQL interpretation. For a simple date (without time)it's ok, but you need accurate time to the second. You are passing the data back and forth through the ODBC link and if the milli-seconds parts don't match then the find will fail.
S7
Nov 14 '11 #10
The idea of using 'suser_sname()' is perfect. I tried it and it works, but it looks like 'domain\user_id' (with environ it shows only user_id) which is not a big problem. This field will be visible in the form for users, but I can rewrite it in Access so that they see only the user_id.
Thanks a lot.
Concerning your other points..
1) What is CSQ? :) You mean the alias 'MaxDate'? I used table alias in another form and it works fine.
2),3) You are right. But there is still the problem that I cannot still run it because of the syntax error..

But I think your first idea will solve my problem in the best way.. Thanks a lot.
Martina
Nov 14 '11 #11
sierra7
446 Expert 256MB
To strip out the domain and get username only, use;
Expand|Select|Wrap|Line Numbers
  1. (substring(suser_sname(),charindex('\',suser_sname())+(1),len(suser_sname())))
I'll keep it brief because this is T-SQL not VBA and I'll get bumped-off.

S7
Nov 14 '11 #12
NeoPa
32,556 Expert Mod 16PB
martinaPar:
Hi NeoPa, I am not sure what you are asking for.
I ask no question Martina. I simply point out that as your question cannot possibly make sense (as VBA is not something you can, or would want to, write using SQL) it makes little sense for any of us to start trying to answer it. A more sensible approach would be to prompt you to make what you want clear and understandable first. That way we can spend time trying to help you rather than waste loads of it trying to guess what it is you really want.

Such things should be sorted out as soon as possible because the more posts go by without the question becoming clear the harder it is to follow what is going on, so the thread becomes worse than useless. I guess it's too late to worry about that for this thread but you may like to bear it in mind for future questions.
Nov 14 '11 #13
@S7 Yesterday a tried something similar
Expand|Select|Wrap|Line Numbers
  1.  right(suser_sname(),len(suser_sname())-charindex('\',suser_sname())) 
and it works okn as well. So again, thanks a lot for the idea. I have never used default value for a field in a table in SQL server so I have one more thing I have learnt :) I need to work with SQL and VBA, Access forms etc., but wihout any training so I learn by working...
@NeoPa Ok, I am sorry that my question was not understandable. I will keep this on my mind for the next time and try to define the problem better.

Have a nice day both of you,
Martina
Nov 15 '11 #14

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

Similar topics

17
by: beliavsky | last post by:
Many of my C++ programs have the line using namespace std; but the "Accelerated C++" book of Koenig and Moo has many examples where the library names are included one at a time, for example ...
5
by: Enos Meroka | last post by:
Hallo, I am a student doing my project in the university.. I have been trying to compile the program using HP -UX aCC compiler, however I keep on getting the following errors. ...
12
by: Calum Grant | last post by:
In older C++ computer books, you'll often see using namespace std; even in my 1996 copy of Stroustrup. Nowadays, it seems to be considered better to qualify names to make it clearer what...
0
by: canasdaq | last post by:
Can anyone please help me. I was looking at the article "http://www.eggheadcafe.com/articles/20030603.asp". I am new to .net and know nothing in c#. I want to write a menu in asp.net. Can anyone...
11
by: Grasshopper | last post by:
Hi, I am automating Access reports to PDF using PDF Writer 6.0. I've created a DTS package to run the reports and schedule a job to run this DTS package. If I PC Anywhere into the server on...
3
by: John Spiegel | last post by:
Hi All, I've been working on building a .dll in C# (framework 1.1) and using it in VFP8. As always, the example that runs so smoothly in the article fails at a rather basic point in practice. ...
8
by: acb | last post by:
Hi, I wrote a DLL Component (using Visual Studio 2005) and managed to include it into a C# Console application. I am now trying to include this component into a Web project. I copy the DLL...
0
by: Eugene Anthony | last post by:
The problem with my coding is that despite removing the records stored in the array list, the rptPages repeater control is still visible. The rptPages repeater control displayes the navigation...
3
by: JDeats | last post by:
I have some .NET 1.1 code that utilizes this technique for encrypting and decrypting a file. http://support.microsoft.com/kb/307010 In .NET 2.0 this approach is not fully supported (a .NET 2.0...
8
by: =?Utf-8?B?Q2hyaXMgSGFsY3Jvdw==?= | last post by:
Hi there I've successfully added some .NET validation controls to a page (using <asp:RequiredFieldValidator ...), however when I try to set the 'display' property to 'dynamic', my page then...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.