Docmd.runsql

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • jl2886
    New Member
    • Mar 2007
    • 83

    #1

    Docmd.runsql

    Hello. I have two questions:
    Code:
     Private Sub Form_Load()
    Dim assID As Long
       assID = DMax("CLng(Right([LSI_Case_Number],3))", "Master_Log")
       Me.LSI_Case_Number = Month(Date) & Format(Year(Date), "yy") & "-" & Format(assID + 1, "000")
    End Sub
    It reads an indentifier cursor error. In my table LSI_Case_Number is a text variable

    Next question:
    I have a form that has all the variables from my main table: Master_Log, and one of the variables is Disposition.
    Disposition is automatically filled in upon hitting enter(it depends on many of the variables in the form).
    If the disposition says "rejected", I would like the case to be sent to the "Rejected" table(which is just an empty copy of the Master_Log), and deleted from the Master_Log.

    I think I am supposed to use Docmd.runsql, but does it matter if try to use that command in an after_update Disposition. I think it does matter, it needs to be a DoSql sub.
    Here is my poor attempt:
    Code:
    Private Sub DoSql()
    Dim SQL As String
    If Me.dispostion = "Reject" Then
    SQL = "APPEND Rejected"
    End If
    DoCmd.RunSQL SQL
    End Sub
  • Rabbit
    Recognized Expert MVP
    • Jan 2007
    • 12517

    #2
    Originally posted by jl2886
    Hello. I have two questions:
    Code:
     Private Sub Form_Load()
    Dim assID As Long
       assID = DMax("CLng(Right([LSI_Case_Number],3))", "Master_Log")
       Me.LSI_Case_Number = Month(Date) & Format(Year(Date), "yy") & "-" & Format(assID + 1, "000")
    End Sub
    It reads an indentifier cursor error. In my table LSI_Case_Number is a text variable

    Next question:
    I have a form that has all the variables from my main table: Master_Log, and one of the variables is Disposition.
    Disposition is automatically filled in upon hitting enter(it depends on many of the variables in the form).
    If the disposition says "rejected", I would like the case to be sent to the "Rejected" table(which is just an empty copy of the Master_Log), and deleted from the Master_Log.

    I think I am supposed to use Docmd.runsql, but does it matter if try to use that command in an after_update Disposition. I think it does matter, it needs to be a DoSql sub.
    Here is my poor attempt:
    Code:
    Private Sub DoSql()
    Dim SQL As String
    If Me.dispostion = "Reject" Then
    SQL = "APPEND Rejected"
    End If
    DoCmd.RunSQL SQL
    End Sub
    Well, I tested it and it works. Are you sure the last 3 characters will always be numbers?

    An Append SQL is contructed like this:
    Code:
    INSERT INTO TableName1
    SELECT Fields
    FROM TableName2;

    Comment

    • jl2886
      New Member
      • Mar 2007
      • 83

      #3
      So what would my code look like for the append and delete look like for an after update of my dispostion variable.
      The assign id works but only when i load the form. Can I do a click statement that assigns the lsi_case_number . and for some reason the year is 05.

      Comment

      • Rabbit
        Recognized Expert MVP
        • Jan 2007
        • 12517

        #4
        Originally posted by jl2886
        So what would my code look like for the append and delete look like for an after update of my dispostion variable.
        The assign id works but only when i load the form. Can I do a click statement that assigns the lsi_case_number . and for some reason the year is 05.
        I don't know what your code would look like, I don't know anything about how table structure.

        Yes, you can put the code in your on load in the on click event of a control.

        Comment

        Working...