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

Rabu, 05 Februari 2014

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