DAO Loop

[businesshippieRH]

I have created a loop which seems to work perfectly. The AddSQL bit prints perfectly to the intermediate window. However, the DoCmd.RunSQL doesn't seem to work, and I can't figure out why. Any help is appreciated!
Private Sub Form_Load()
'Add all new entries from Default schedule to "temporary" tbl for enty to new schedule

Dim Updatetbl_RS_NEW As String
    Updatetbl_RS_NEW = "INSERT INTO tbl_RateSchedule_NEW (EqTypeID, DefaultDailyRate, DefaultWeeklyRate, DefaultMonthlyRate, AddedBy, DateAdded) " & _
                        "SELECT EqTypeID, DailyRate, WeeklyRate, MonthlyRate, AddedBy, DateAdded " & _
                        "FROM tbl_RateSchedule_DEFAULT " & _
                                        "WHERE NOT EXISTS (SELECT * FROM tbl_RateSchedule_NEW " & _
                                        "WHERE tbl_RateSchedule_NEW.EqTypeID = tbl_RateSchedule_DEFAULT.EqTypeID)"
DoCmd.RunSQL Updatetbl_RS_NEW
'Since tbl_RS_DEFAULT doesn't have an EquipmentType Field, use loop to get each from tbl_EqTypeValidValues and Add it
Dim DBS As DAO.Database
Dim RS As DAO.Recordset
Dim FinderSQL As String
Dim IntI As Integer

    'Set to look at the list created
        'Find empty...
        Set DBS = CurrentDb
        FinderSQL = "SELECT EqTypeID FROM tbl_RateSchedule_NEW WHERE EquipmentType Is Null"
        Set RS = DBS.OpenRecordset(FinderSQL, dbOpenDynaset)

        'If RS Empty, quit
        If RS.EOF Then Exit Sub
        
        'Do Stuff with Equipment types without names
        With RS
            Do Until .EOF
            'Add to table
            Dim AddSQL As String
                AddSQL = "INSERT INTO tbl_RateSchedule_NEW (EquipmentType) " & _
                            "SELECT EquipmentType " & _
                            "FROM tbl_EquipmentTypeValidValues " & _
                            "WHERE tbl_RateSchedule_NEW.EqTypeID = " & RS!EqTypeID
Debug.Print AddSQL
                DoCmd.SetWarnings (WarningsOff)
                DoCmd.RunSQL AddSQL
                DoCmd.SetWarnings (WarningsOn)
            .MoveNext
            Loop
        End With
'Refresh form RecordSource
Me.Requery
End Sub

 

[businesshippieRH]

I have also tried:

CurrentDb.Execute AddSQL

instead of

DoCmd.SetWarnings (WarningsOff)
DoCmd.RunSQL AddSQL
DoCmd.SetWarnings (WarningsOn)

However, it throws RunTime Error 3061 (not enough parameters). Only the first SQL statement is printed to the intermediate window as the loop stops at this point. :banghead:

 

[jdraw]

Your syntax for the SQL is wrong. You're showing 2 WHERE statements in the code.
That's a no-no.

Try (untested)

...  "WHERE NOT EXISTS (SELECT * FROM tbl_RateSchedule_NEW "  & _
        " AND tbl_RateSchedule_NEW.EqTypeID = tbl_RateSchedule_DEFAULT.EqTypeID)"
......

 

[MarkK]

Looks like that second WHERE clause is in a sub-query, so that's probably OK.

What I would do with this error is copy the SQL string and test it in the design grid. It'll give you more precise error messages, and highlight which fields it's having trouble finding.

 

[jdraw]

Good eye Markk, I missed the second select... focused too much on the alignment of the WHEREs.

 

[MarkK]

I would do . . .

    Dim Updatetbl_RS_NEW As String
    Updatetbl_RS_NEW = _
        "INSERT INTO tbl_RateSchedule_NEW " & _
            "( EqTypeID, DefaultDailyRate, DefaultWeeklyRate, DefaultMonthlyRate, AddedBy, DateAdded ) " & _
        "SELECT EqTypeID, DailyRate, WeeklyRate, MonthlyRate, AddedBy, DateAdded " & _
        "FROM tbl_RateSchedule_DEFAULT " & _
        "WHERE NOT EXISTS " & _
            "( " & _
            "SELECT * " & _
            "FROM tbl_RateSchedule_NEW " & _
            "WHERE tbl_RateSchedule_NEW.EqTypeID = tbl_RateSchedule_DEFAULT.EqTypeID " & _
            ")"

 

[businesshippieRH]

Thanks. MarkK, your statement looks the same as mine (minus the fact that the code is easier to read due to line breaks). However, if I'm missing something, please let me know. Any input is great since I really have nobody else at work that's written more than a macro. That first SQL statement works fine and properly fills the tbl_RateSchedule_NEW.

The problem is that the EquipmentType field does not exist in the DEFAULT table (nor do I really want it to if not necessary- I hate having the same data in every table). However, the EqTypeID is the same throughout the database, and all EqTypeIDs are tied to a valid values list with "Equipment Descriptors" for lack of a better word.

I'm trying to display these Descriptors "EquimentType" on my form (since the IDs really mean nothing to users- it's just an autonumber), which has its Control Source tied to tbl_RateSchedule_NEW. Once users are done inputting new rates, a new table is named, created, and used if a project is using something other than the default rate (this all works as well).

The problem is with the AddSQL statement, or rather the "firing" of it. Almost to the end of the code posted, you'll see my debug for this statement. It prints correctly. It even runs through the loop and I see a statement for each RS!EqTypeID. So: I know it's able to get the right values from the Finder statement.

However, it fails to update the table... If I use DoCmd.Run SQL, nothing happens. If I use CurrentDb.Execute, it fails on the first loop and gives the runtime error.

Sorry if I wasn't clear enough about this in the original post. Thank you all for your help. I'm still learning my way through VBA and SQL, and this one has me absolutely stumped.

 

[WayneRyan]

RH,

Isn't your loop supposed to be an Update instead of an Insert?

Update tbl_RateSchedule_NEW
Set    tbl_RateSchedule_NEW.EquipmentType = tbl_EquipmentTypeValidValues.EquipmentType
From   tbl_RateSchedule_NEW, tbl_EquipmentTypeValidValues
Where  tbl_RateSchedule_NEW.EqTypeID = tbl_EquipmentTypeValidValues.EqTypeID And
       tbl_RateSchedule_NEW.EquipmentType Is Null

But, you can move it to the first insert.
Then, you won't need the rs loop at all.

Updatetbl_RS_NEW = "INSERT INTO tbl_RateSchedule_NEW (EqTypeID, [B]EquipmentType[/B], DefaultDailyRate, DefaultWeeklyRate, 
                   "                                  DefaultMonthlyRate, AddedBy, DateAdded) " & _
                   "SELECT A.EqTypeID, [B]C.EquipmentType[/B], A.DailyRate, A.WeeklyRate, A.MonthlyRate, A.AddedBy, A.DateAdded " & _
                   "FROM (tbl_RateSchedule_DEFAULT As A Left Join tbl_RateSchedule_NEW As B On " & _
                   "          A.EqTypeID = B.EqTypeID) Left Join [B]tbl_EquipmentTypeValidValues As C[/B] On " & _
                   "              a.EqTypeID = C.EqTypeID " & _
                   "Where B.EqTypeID Is Null"

hth,
Wayne

 

[businesshippieRH]

WayneRyan, that worked perfectly. I simply moved it to the first statement. Also: sorry for the long delay, I've been out of town on a job and haven't been able to work on this project in a bit.

Like I said, still learning my way through SQL, and the "As A, B, etc." is new to me. I'll definitely be looking into it a bit more as I know it'll help me in the future.

Thanks again!

Final code:

Private Sub Form_Load()
'Add all new entries from Default schedule to "temporary" tbl for enty to new schedule
DoCmd.SetWarnings (WarningsOff)
Dim Updatetbl_RS_NEW As String
    Updatetbl_RS_NEW = "INSERT INTO tbl_RateSchedule_NEW (EqTypeID, EquipmentType, DefaultDailyRate, DefaultWeeklyRate, " & _
                       "DefaultMonthlyRate, AddedBy, DateAdded) " & _
                    "SELECT A.EqTypeID, C.EquipmentType, A.DailyRate, A.WeeklyRate, A.MonthlyRate, A.AddedBy, A.DateAdded " & _
                    "FROM (tbl_RateSchedule_DEFAULT As A Left Join tbl_RateSchedule_NEW As B On " & _
                   "A.EqTypeID = B.EqTypeID) Left Join tbl_EquipmentTypeValidValues As C On " & _
                   "A.EqTypeID = C.EqTypeID " & _
                   "Where B.EqTypeID Is Null"
Debug.Print Updatetbl_RS_NEW
    DoCmd.RunSQL Updatetbl_RS_NEW
'Refresh form RecordSource
Me.Requery
DoCmd.SetWarnings (WarningsOn)
End Sub