Circluar SQL Error in Form Control

[Monterey_Manzer]

Hi All,



I may have bitten off a bit more than I can chew here and I would appreciate any help in masticating this problem. I am trying to condense the amount of administrative tasks that need to be completed for an action and I may possibly be trying to get too fancy here. However, it would be a particularly pleasant coup if I could pull this off.

In the following example I am trying to append a specific value from one table to another based on a set of criteria in the form being used. The code for this action is as follows:

Private Sub Statuscbo_AfterUpdate()
    Dim SMARTID As String, RGDLimit As Date, OGDLimit As Date, ThisYear As Integer
    Dim db As DAO.Database
On Error GoTo ErrHandler

    ThisYear = Year(Date)
    Debug.Print ThisYear
' Setting the limit dates for Original and Revised Grad Dates (OGDLimit and RGDLimit respectively)
    OGDLimit = #7/1/2013#
    RGDLimit = #8/1/2013#
    
' Setting the database that will be performing these actions
    Set db = CurrentDb

' Setting the value of the SMARTID variable based upon the below criteria
    If Me.Statuscbo.Value = "Approved" And Formcbo.Value = "Award Length Change Request" And Me.Active_Request_.Value = "Yes" Then
        SMARTID = Me.SMART_ID.Value
        
' Appending SMARTIDs to Intern Tracker for those participants whose change in grad date requires them to
' go on an internship.  This is nested within the above If...Then statement and will only execute if the above conditions
' are met.
        If Me.ReGradDate.Value > RGDLimit And Me.OGradDate.Value < OGDLimit Then
            db.Execute "INSERT INTO InternsTracker2013 SELECT [SMART ID] From CRTracker " & _
                "WHERE CRTracker.[SMART ID] = '" & Me.SMART_ID.Value & "'"
        End If
    Else: SMARTID = ""
    End If
Exit Sub
ErrHandler:
    MsgBox Err.Number & ":  " & Err.Description
End Sub

I keep getting a circular reference error in the SQL statement within the db.Execute command. I'm trying to ensure that the SMARTID appended is only the one from the record being modified in the form. If anyone has any idea of how I can correct this I would be very appreciative.

A second issue I have is the dates I set for OGDLimit and RGDLimit. I want to set up something like OGDLimit=#7/1/Year(Date)# but this also gives me an "Expected Expression" error as well. If anyone knows how I can set my dates so that the year value stays current I would greatly appreciate that too.

Thanks!

[June7]

Need to identify the field(s) to receive data.

db.Execute "INSERT INTO InternsTracker2013 ([SMART ID]) SELECT [SmartID] FROM CRTracker WHERE [SmartID]='" & Me.SMART_ID & "'"

If the above filter criteria limits the dataset to one record, could instead:
db.Execute "INSERT INTO InternsTracker2013 ([SMART ID]) VALUES('" & Me.SMART_ID & "')"

OGDLimit="#7/1/" & Year(Date) & "#"

[ssanfu]

A second issue I have is the dates I set for OGDLimit and RGDLimit. I want to set up something like OGDLimit=#7/1/Year(Date)# but this also gives me an "Expected Expression" error as well. If anyone knows how I can set my dates so that the year value stays current I would greatly appreciate that too.

This will change the year:

   ThisYear = Year(Date)
   Debug.Print ThisYear
   ' Setting the limit dates for Original and Revised Grad Dates (OGDLimit and RGDLimit respectively)
   OGDLimit = DateSerial(ThisYear, 7, 1)   '#7/1/2013#
   RGDLimit = DateSerial(ThisYear, 8, 1)   '#8/1/2013#

Not sure what you are trying to do, but try this:

Private Sub Statuscbo_AfterUpdate()
   Dim db As DAO.Database
   Dim SMARTID As String, RGDLimit As Date, OGDLimit As Date, ThisYear As Integer
   Dim sSQL As String

   On Error GoTo ErrHandler

   ' Setting the database that will be performing these actions
   Set db = CurrentDb

   ThisYear = Year(Date)
   Debug.Print ThisYear
   ' Setting the limit dates for Original and Revised Grad Dates (OGDLimit and RGDLimit respectively)
   OGDLimit = DateSerial(ThisYear, 7, 1)   '#7/1/2013#
   RGDLimit = DateSerial(ThisYear, 8, 1)   '#8/1/2013#

   ' Setting the value of the SMARTID variable based upon the below criteria
   If Me.Statuscbo = "Approved" And Formcbo = "Award Length Change Request" And Me.Active_Request_ = "Yes" Then
      SMARTID = Me.SMART_ID

      '       Appending SMARTIDs to Intern Tracker for those participants whose change in grad date requires them to
      '       go on an internship.  This is nested within the above If...Then statement and will only execute if the above conditions
      '       are met.
      If Me.ReGradDate > RGDLimit And Me.OGradDate < OGDLimit Then
         sSQL = "INSERT INTO InternsTracker2013"
         sSQL = sSQL & " SELECT [SMART ID] From CRTracker WHERE [SMART ID] = '" & SMARTID & "'"
         Debug.Print sSQL

         db.Execute sSQL, dbFailOnError
      End If
   
   End If

   Exit Sub


ErrHandler:
   MsgBox Err.Number & ":  " & Err.Description
End Sub

BTW, having tables named with a year is usually an indication of a non-normalized structure. Next year, 2014, you will have to create a new table, new queries, new forms, new reports and revised your code. You should remove the year from the table name and add another field in the table for the year. (Do not name the field "Year". "Year is a reserved word in Access.)

[ssanfu]

@June,

This is the example in Help

INSERT INTO Employees SELECT Trainees.* FROM Trainees WHERE HireDate < Now() - 30;

I never use this form of the insert statement, but shouldn't (doesn't) this insert records?? Am I missing something?

The OP is selecting one field ([SMART ID]),

......SELECT [SMART ID] From CRTracker WHERE [SMART ID] = '" & SMARTID & "'"

or does it have to be all fields in the table?

.....SELECT CRTracker.* From CRTracker WHERE [SMART ID] = '" & SMARTID & "'"

[June7]

Ooops! Just tested and yes it does work. Every example I found on web showed specifying the destination fields and thought I had encountered issues before when I didn't.

So what is wrong with the original SQL? I can't see anything. Is SmartID a number or text type? If it is number then remove the apostrophe delimiters.

If the result should be only one record inserted, try the alternate without the SELECT.

[ssanfu]

I wasn't sure if the OP was trying to update many smart IDs or just one.....

[June7]

"I'm trying to ensure that the SMARTID appended is only the one from the record being modified in the form." makes me think the result should be one new record in the destination table.

[Monterey_Manzer]

Thanks Guys! I'll be trying out the various code suggestions and let you know which works best.

June7 - The SMARTID is a Text data field. The intended result is only a single record inserted.

Steve: Thanks a bunch for the help with the dates. This will apply to many situations beyond this one. With regard to the table names, I am aware of this folly and will work to correct it.

[Monterey_Manzer]

Alright June7 and Steve,

I modified the code somewhat to make sure the user of the form was aware they were adding a participant but otherwise it's still the code that Steve presented. However, when I run it I get a Type Mismatch error. Any thoughts?

Private Sub Statuscbo_AfterUpdate()
    Dim SMARTID As String, RGDLimit As Date, OGDLimit As Date, ThisYear As Integer, sSQL As String, AddPart As String
    Dim db As DAO.Database
On Error GoTo ErrHandler

    ThisYear = Year(Date)
   Debug.Print ThisYear
   ' Setting the limit dates for Original and Revised Grad Dates (OGDLimit and RGDLimit respectively)
   OGDLimit = DateSerial(ThisYear, 7, 1)   '#7/1/2013#
   RGDLimit = DateSerial(ThisYear, 8, 1)   '#8/1/2013#

   ' Setting the value of the SMARTID variable based upon the below criteria
   If Me.Statuscbo = "Approved" And Formcbo = "Award Length Change Request" And Me.Active_Request_ = "Yes" Then
      SMARTID = Me.SMART_ID

      '       Appending SMARTIDs to Intern Tracker for those participants whose change in grad date requires them to
      '       go on an internship.  This is nested within the above If...Then statement and will only execute if the above conditions
      '       are met.
      If Me.ReGradDate > RGDLimit And Me.OGradDate < OGDLimit Then
Repeat:
      AddPart = InputBox("Add Participant to Internship Tracker?" & vbCr & vbCr & Chr(9) & _
                "Enter Y or N", "Add Participant?")
        If AddPart = "Y" Or "y" Then
         sSQL = "INSERT INTO InternsTracker"
         sSQL = sSQL & " SELECT [SMART ID] From CRTracker WHERE [SMART ID] = '" & SMARTID & "'"
         Debug.Print sSQL
         db.Execute sSQL, dbFailOnError
        ElseIf AddPart = "N" Or "n" Then
            Exit Sub
        Else
            MsgBox "Please make a valid selection", , "Selection Invalid"
            GoTo Repeat
        End If
      End If
   
   End If

   Exit Sub
ErrHandler:
    MsgBox Err.Number & ":  " & Err.Description
End Sub

Oh, it's probably worth noting that the sSQL string failed to print to the Immediate window but the InputBox I added did work just fine. Also I get the type mismatch error when I enter N as a response to my InputBox as well.

[Monterey_Manzer]

Further update: I removed all the InputBox stuff as well as the extra If...Then statement associated with it. I now get code that actually prints the sSQL code but now I get error# 91: Object variable or With block variable is not set. Well at least I've made it a little farther down the code! Any thoughts anyone has to this rather lengthy comment string would be greatly appreciated. June& and Steve, you guys have already been quite helpful and I am very grateful.

[June7]

You have declared variable db as a database object but don't set it before using it with Execute.

Set db = CurrentDb.Connection

Don't really need the db variable.

CurrentDb.Execute "..."

[ssanfu]

I think it is easier if you only have two choices OK or cancel. So I modified your code. I built a table and a form. The code executes, but it inserts only one Intern Smart ID record.

Option Compare Database
Option Explicit

Private Sub Statuscbo_AfterUpdate()
   Dim SMARTID As String, RGDLimit As Date, OGDLimit As Date, ThisYear As Integer, sSQL As String, AddPart As String
   Dim Msg, Style, Title, Response

   On Error GoTo ErrHandler

   ThisYear = Year(Date)
   '   Debug.Print ThisYear
   ' Setting the limit dates for Original and Revised Grad Dates (OGDLimit and RGDLimit respectively)
   OGDLimit = DateSerial(ThisYear, 7, 1)   '#7/1/2013#
   RGDLimit = DateSerial(ThisYear, 8, 1)   '#8/1/2013#

   ' Setting the value of the SMARTID variable based upon the below criteria
   If Me.Statuscbo = "Approved" And Formcbo = "Award Length Change Request" And Me.Active_Request_ = "Yes" Then

      'get the current value for smart ID
      SMARTID = Me.SMART_ID

      '       Appending SMARTIDs to Intern Tracker for those participants whose change in grad date requires them to
      '       go on an internship.  This is nested within the above If...Then statement and will only execute if the above conditions
      '       are met.
      If Me.ReGradDate > RGDLimit And Me.OGradDate < OGDLimit Then

         Msg = "Add Participant to Internship Tracker?"    ' Define message.
         Style = vbYesNo + vbQuestion + vbDefaultButton2    ' Define buttons.
         Title = "What to do?"    ' Define title.
         ' Display message.
         Response = MsgBox(Msg, Style, Title)
         If Response = vbYes Then    ' User chose Yes.
            sSQL = "INSERT INTO InternsTracker ([SMART ID])"
            sSQL = sSQL & " Values('" & SMARTID & "');"
            '            Debug.Print sSQL
            CurrentDb.Execute sSQL, dbFailOnError

            '--- debugging---
            MsgBox "Intern value Inserted"
            '-- debugging---
         Else    ' User chose No.
            Msg = "Intern not added"    ' Define message.
            Style = vbOKOnly + vbInformation    ' Define buttons.
            Title = "NOT ADDED"    ' Define title.

            Response = MsgBox(Msg, Style, Title)

            '
         End If
      End If

   End If

   Exit Sub
ErrHandler:
   MsgBox Err.Number & ":  " & Err.Description
End Sub

Using an Input box where there are only two choices allowed and requiring the user to type in something allows errors to be made. So (I think) the message box option is easier. You only have two buttons....

Is this closer to what you want?

[Monterey_Manzer]

Thanks a million guys!

Steve, your code works beautifully! I definitely prefer the MsgBox interface but wasn't sure how to gather the user's response. I'm really only familiar with using MsgBoxes for displaying information but I will definitely be employing similar solutions in the future.

This really opens up a lot more modifications I can make to streamline administrative processes. You guys rock!

Thanks,
Ryan

[June7]

The MsgBox has two versions - just a popup OKonly message and a function that can return a value for capture. The parens make the difference. The response can set a variable or with only two possible respones, just be in an If Then:

If MsgBox(Msg, Style, Title) = vbYes Then