ACCESS FEED: Re: [MS_AccessPros] AutoExec or something else?

I think I found the problem. The tblSupervisor table that is created before the process even starts is being deleted and it cannot find the table during the process. So I fixed that but my Insert statement is giving me errors.

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 11:23 AM, Duane Hookom <duanehookom@hotmail.com> wrote:

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>>