RE: [MS_AccessPros] AutoExec or something else?

Jim,

I typically write code like:

Private Sub Form_Load()

' Enable error handler.
    On Error GoTo Error_ErrorHandler
        
    Dim db As DAO.Database
    Dim strSQL as String

    Set db = CurrentDb
    strSQL = "DELETE * FROM tblSupervisors"
    db.Execute strSQL, dbFailOnError
    MsgBox "Hi"
    strSQL = "INSERT INTO tblSupervisors ([FieldListHere]) SELECT [FieldListHere] FROM tblSomeName "
    db.Execute strSQL, dbFailOnError
    
    MsgBox "Hi"
Exit_ErrorHandler:
    Exit Sub

Error_ErrorHandler:
    Resume Exit_ErrorHandler


End Sub

Are you suggesting only specific records aren't deleted? 

Are the records to be deleted potentially bound to an open form?

Could you share the SQL of your queries?

Duane Hookom MVP
MS Access

________________________________
> From: luvmymelody@yahoo.com
>
> So here is my code and it is not deleting the records and appending the
> records. I deleted 100 records and it is still the same. I added some
> msgbox's to see how it is working.
>
> Private Sub Form_Load()
>
> ' Enable error handler.
> On Error GoTo Error_ErrorHandler
>
> Dim db As DAO.Database
>
> Set db = CurrentDb
> db.Execute "qryDeleteAllFromSupervisorTable", dbFailOnError
> MsgBox "Hi"
> db.Execute "qryAppendSupervisorTable", dbFailOnError
>
>
> MsgBox "Hi"
> Exit_ErrorHandler:
> Exit Sub
>
> Error_ErrorHandler:
> Resume Exit_ErrorHandler
>
>
> End Sub
>
> Jim Wagner
> ________________________________
>
>
> On Wednesday, February 5, 2014 9:06 AM, Glenn Lloyd
> <argeedblu@gmail.com> wrote:
>
> Jim,
>
> If you use the code John suggested, you also will not need to fiddle
> with SetWarnings. You can delete both of those DoCmd statements.
>
> Glenn
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John
> Viescas
> Sent: Wednesday, February 5, 2014 11:00 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] AutoExec or something else?
>
>
> Jim-
>
> Don't use OpenQuery. Use:
>
> Dim db As DAO.Database
>
> Set db = CurrentDb
> db.Execute "first query", dbFailOnError
> db.Execute "second query", dbFailOnError
>
> John Viescas, Author
>
> On Feb 5, 2014, at 4:45 PM, Jim Wagner
> <luvmymelody@yahoo.com<mailto:luvmymelody@yahoo.com>> wrote:
>
>
> John,
>
> I put the code in the On Open event and only the first query ran. The
> second query did not run. If I put the code behind a button on the
> form, both queries run. I added msgbox's to see if it gets past the 1st
> and second queries but the second msgbox never appears. Below is my
> code.
>
> Private Sub Form_Open(Cancel As Integer)
> ' Enable error handler.
> On Error GoTo Error_ErrorHandler
> DoCmd.SetWarnings False
> DoCmd.OpenQuery "qryDeleteAllFromSupervisorTable" 'Deletes all
> records in the supervisortable
>
> MsgBox "Hi"
> DoCmd.OpenQuery "qryAppendSupervisorTable" 'Appends
> all records from the tblSourcetblSupervisorList table
> MsgBox "Hi"
> DoCmd.SetWarnings True
>
> Exit_ErrorHandler:
> Exit Sub
>
> Error_ErrorHandler:
> Resume Exit_ErrorHandler
>
> End Sub
>
>
>
> Jim Wagner
> ________________________________
>
> On Wednesday, February 5, 2014 8:41 AM, John Viescas
> <JohnV@msn.com<mailto:JohnV@msn.com>> wrote:
>
> Jim-
>
> AutoExec macro or put code to run the queries in the Load event of the
> form that you open at startup.
>
> John Viescas, Author
>
> On Feb 5, 2014, at 4:00 PM, Jim Wagner
> <luvmymelody@yahoo.com<mailto:luvmymelody@yahoo.com>> wrote:
>
>
> Hello all,
>
> How do I have 2 queries run each time the database is opened. Do I have
> to use an AutoExec macro or is there another way of programming it?
>
> Thank You
>
> Jim Wagner

------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
http://info.yahoo.com/legal/us/yahoo/utos/terms/