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

VBA using SQL

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

Share this Question
Share on Google+
13 Replies


ADezii
Expert 5K+
P: 8,680
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

P: 7
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
Expert 5K+
P: 8,680
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

P: 7
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
Expert Mod 15k+
P: 31,712
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

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

Expert 100+
P: 446
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

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

Expert 100+
P: 446
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

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

Expert 100+
P: 446
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
Expert Mod 15k+
P: 31,712
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

P: 7
@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

Post your reply

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