Help Analyzing Code

denwaps · January 15th, 2013, 10:38 AM

Hello,

I am new to VBA and am trying to figure out the terminology in some existing code in one of our databases.

This code is executed upon a button click on one of the forms, and basically logs the information currently in the form into a log table (tblResearch_Comments in this case).

I'm a little confused as to what a lot of the shortcuts are at the top of the code, and was hoping someone could help me out.

Code:

Private Sub cmdSaveComments_Click()
    With Me
        If IsNull(.txtComments.Value) Then
            Call Application.CurrentDb.Execute("DELETE * FROM tblResearch_Comments WHERE item_num = " & .txtItemNum.Value)
            Call Application.CurrentDb.Execute("UPDATE tblSyncBypassRpt SET Comments = '', Done = 0 WHERE item = " & .txtItemNum.Value)
        Else
            Dim strDate As String:  strDate = " :[" & Now() & "]"
            
            If InStr(UCase(.txtComments.Value), "SKIPTIMESTAMP") <> 0 Then
                strDate = ""
                .txtComments.Value = Replace(.txtComments.Value, "SKIPTIMESTAMP", "")
            End If
        
            If isExisting Then
                Call Application.CurrentDb.Execute("UPDATE tblResearch_Comments SET Comments = '" & Replace(.txtComments.Value, "'", "%^&*") & strDate & "', Research_Complete = " & IIf(IsNull(chkResearchComplete.Value), 0, chkResearchComplete.Value) & " WHERE item_num = " & .txtItemNum.Value)
            Else
                Call Application.CurrentDb.Execute("INSERT INTO tblResearch_Comments VALUES (" & .txtItemNum.Value & ",'" & Replace(.txtComments.Value, "'", "%^&*") & strDate & "', " & IIf(IsNull(chkResearchComplete.Value), 0, chkResearchComplete.Value) & ")")
            End If
            
            Call Application.CurrentDb.Execute("UPDATE tblSyncBypassRpt SET Comments = '" & Replace(.txtComments.Value, "'", "%^&*") & strDate & "', Done = " & IIf(IsNull(chkResearchComplete.Value), 0, chkResearchComplete.Value) & " WHERE item = " & .txtItemNum.Value)
        End If
    End With
    
    Call DoCmd.Close(acForm, "frmResearchComments", acSaveNo)
End Sub

In the first couple lines, what does this mean?:

Code:

Comments = '', Done = 0 WHERE item = " & .txtItemNum.Value

Is this setting a shortcut where the comments box is now reffered to as " ?? Also, what does the & sign represent?

Later in the code, I'm getting very confused by all the symbols piled together, such as:
.txtComments.Value, "'", "%^&*"

Could someone explain what one of those last few Call lines means?

As you can see I'm a bit lost, so thank you for any help you can provide with this!

rampantlion · February 3rd, 2013, 06:42 PM

Based on your questions, I think the best advice I can give you is to purchase and read the following book:
Access 2010 Programmer's Reference (published by Wrox)
It's about $25-30 (looks like they sell it on this site; Amazon has it, too).

Here's a few answers to your questions, in the meantime:
'Me' refers to the form "frmResearchComments", in this case. Anything after the 'With Me' statement will refer to that contained within the form object. For example, ".txtComments" is likely a textbox on the form (perhaps labeled "Comments" or something like that). So, 'Me.txtComments.Value' would contain whatever the user has entered into this textbox on this form.

"Comments = '', Done = 0 WHERE item = " & .txtItemNum.Value"
This is part of a SQL statement, which begins with "UPDATE". "Comments" is a field in the table called "tblSyncBypassRpt"; "Done" is also a field (probably a Boolean--True/False) in this table. A third field is called "item". The "&" is used to concatenate 2 portions together in a string. For example, if Me.txtItemNum.Value = 6, the SQL statement, as written, would be the equivalent of: "WHERE item = 6". I'm assuming you are new to SQL, so forgive me for paraphrasing what the SQL statement will do:
Where the item number = 6, Update the SyncBypassRpt table's Comment and Done fields: with 'nothing' and 'False', respectively. If this table has 10 total records and 3 of those records have a value in "Item" of 6, this SQL will update only 3 records and leave the rest alone.

If you consider yourself a novice in SQL, I would advise getting a more specialized book on the subject (in addition to the Programmer's Reference):
"Access 2007 Pure SQL" (very easy to read, but doesn't show complicated examples)
"SQL Queries For Mere Mortals" (has more complicated examples in it). Enjoy.

denwaps · February 4th, 2013, 08:48 AM

Thank you for the response. That will definitely help get me on the right track, and I'll check out some of those book suggestions too.

rampantlion · February 4th, 2013, 10:09 AM

You are very welcome. Good luck!
Jay