RE: [MS_AccessPros] AutoExec or something else?

Jim,

You ignored one of my questions:

Are you suggesting only specific records aren't deleted? 

We can't see your results.

Duane Hookom MVP
MS Access

________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: luvmymelody@yahoo.com
> Date: Wed, 5 Feb 2014 09:09:21 -0800
> Subject: Re: [MS_AccessPros] AutoExec or something else?
>
>
>
> Duane,
>
> my first query deletes all records from the table and the second query
> appends the records from another table that is a linked table.
> Potentially it goes to a form but the form the event is on is on a
> switchboard.
>
> DELETE SupervisorTable.[Dept Id], SupervisorTable.[Dept Ld],
> SupervisorTable.[Pos Ld], SupervisorTable.[Person Nm],
> SupervisorTable.[Person Id], SupervisorTable.[Ast Sup/Lead],
> SupervisorTable.Supervisor, SupervisorTable.[Asc/Ast Dir],
> SupervisorTable.[Dept Head]
> FROM SupervisorTable;
>
>
> INSERT INTO SupervisorTable ( [Dept Id], [Dept Ld], [Person Nm],
> [Person Id], [Ast Sup/Lead], Supervisor, [Asc/Ast Dir], [Dept Head] )
> SELECT tblSourcetblSupervisorList.[Dept Id],
> tblSourcetblSupervisorList.[Dept Ld],
> tblSourcetblSupervisorList.[Person Nm],
> tblSourcetblSupervisorList.[Person Id], tblSourcetblSupervisorList.[Ast
> Sup/Lead], tblSourcetblSupervisorList.Supervisor,
> tblSourcetblSupervisorList.[Asc/Ast Dir],
> tblSourcetblSupervisorList.[Dept Head]
> FROM tblSourcetblSupervisorList;
>
>
>
> Jim Wagner
> ________________________________
>
>
> On Wednesday, February 5, 2014 9:30 AM, Duane Hookom
> <duanehookom@hotmail.com> wrote:
> 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<mailto: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<mailto: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>
>>
> [mailto: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<mailto: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><mailto: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><mailto: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><mailto: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
>
>
> MS_Access_Professionals-digest@yahoogroups.com<mailto:MS_Access_Professionals-digest@yahoogroups.com>
>
> MS_Access_Professionals-fullfeatured@yahoogroups.com<mailto:MS_Access_Professionals-fullfeatured@yahoogroups.com>
>
> MS_Access_Professionals-unsubscribe@yahoogroups.com<mailto:MS_Access_Professionals-unsubscribe@yahoogroups.com>
>
>
>
>
>
>

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

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/