RE: [MS_AccessPros] AutoExec or something else?

Jim,

What happens if you attempt to run the queries from the navigation panel?

Did you change your code to notify you of any errors?

My delete query would look like:

DELETE * FROM SupervisorTable;

Duane Hookom MVP
MS Access

________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: luvmymelody@yahoo.com
> Date: Wed, 5 Feb 2014 09:53:13 -0800
> Subject: Re: [MS_AccessPros] AutoExec or something else?
>
>
>
> Duane,
>
> There are no records being deleted. The delete query is not even
> running. I should get 459 records. I deleted 100 records from the table
> that is being appended and then opened the form. It still has 359
> records. So I deleted all but 7 records and opened the form and there
> are still 7 records. So the answer is that nothing is getting deleted
> or appended.
>
> Jim Wagner
> ________________________________
>
>
> On Wednesday, February 5, 2014 10:46 AM, Duane Hookom
> <duanehookom@hotmail.com> wrote:
> 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<mailto:MS_Access_Professionals@yahoogroups.com>
>> From: luvmymelody@yahoo.com<mailto: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<mailto: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><mailto: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><mailto: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>>
>>>
>>
> [mailto: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><mailto: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>><mailto: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>><mailto: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>><mailto: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><mailto: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><mailto: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><mailto:MS_Access_Professionals-unsubscribe@yahoogroups.com<mailto:MS_Access_Professionals-unsubscribe@yahoogroups.com>>
>
>>
>>
>>
>>
>>
>>
>
> ------------------------------------
>
> 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/