VBA using SQL

[martinaPar]

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:

 
Private Sub comment_AfterUpdate()

SQL "UPDATE dbo_mf_comment SET [user_id] = '" & Environ("username") & "' " _
     & " 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];"
     
End Sub


Sub SQL(strSQL, Optional dbgprnt)
    If Not IsMissing(dbgprnt) Then Debug.Print strSQL
    DoCmd.SetWarnings False
    If Nz(strSQL, "") <> "" Then DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
End Sub

There is still some syntax error and I can't see it.
Can you help?

Martina

[ADezii]

I don't see where you are Calling the SQL() Sub-Routine and passing it the String Argument and possibly the Optional Parameter.

[martinaPar]

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).

[ADezii]

It appears that you have 2 instances where Double Spaces (" ") exist within the SQL String, try removiing them, either manually, or programmaticall y (via Line# 8):

Dim strSQL As String

strSQL = "UPDATE dbo_mf_comment SET [user_id] = '" & Environ("username") & "' " & _
         "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];"

strSQL = Replace(strSQL, "  ", " ")

DoCmd.RunSQL strSQL, dbFailOnError

[martinaPar]

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...

[NeoPa]

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.

[martinaPar]

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

[sierra7]

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

[martinaPar]

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

[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

' AND newComment.MaxDate = #" & dbo_mf_comment.[date] & "#;"

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

[martinaPar]

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

[sierra7]

To strip out the domain and get username only, use;

(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

[NeoPa]

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.

[martinaPar]

@S7 Yesterday a tried something similar

 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