I tried to add some continuations to make it more readable but it did not work, but here it is.
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 SupervisorTable"
db.Execute strSQL, dbFailOnError
MsgBox "delete done"
strSQL = "INSERT INTO SupervisorTable " _
" 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;"
db.Execute strSQL, dbFailOnError
MsgBox "append done"
Exit_ErrorHandler:
Exit Sub
Error_ErrorHandler:
Resume Exit_ErrorHandler
End Sub
Jim Wagner
On Wednesday, February 5, 2014 12:00 PM, Duane Hookom <duanehookom@hotmail.com> wrote:
Jim,
A "compile" error suggests VBA is involved and your recent posting shows the SQL view of a query.
Can you share your current code that is generating the error?
Duane Hookom MVP
MS Access
________________________________
> From: luvmymelody@yahoo.com
>
> Sorry about that. I am getting Compile error: Expected end of statement
> in vba
>
> Jim Wagner
> ________________________________
>
>
> On Wednesday, February 5, 2014 11:39 AM, Duane Hookom
> <duanehookom@hotmail.com> wrote:
>
> Jim,
>
> Again, "We can't see your results."
>
> Can you share what you mean by "giving me errors"?
>
> Duane Hookom MVP
> MS Access
>
> ________________________________
> From: luvmymelody@yahoo.com
>
> 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
>
> ________________________________
> From: luvmymelody@yahoo.com<mailto:luvmymelody@yahoo.com
>
> 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
>
> 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
>
> ________________________________
> From: luvmymelody@yahoo.com<mailto:luvmymelody@yahoo.com
>
> 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;
>
A "compile" error suggests VBA is involved and your recent posting shows the SQL view of a query.
Can you share your current code that is generating the error?
Duane Hookom MVP
MS Access
________________________________
> From: luvmymelody@yahoo.com
>
> Sorry about that. I am getting Compile error: Expected end of statement
> in vba
>
> Jim Wagner
> ________________________________
>
>
> On Wednesday, February 5, 2014 11:39 AM, Duane Hookom
> <duanehookom@hotmail.com> wrote:
>
> Jim,
>
> Again, "We can't see your results."
>
> Can you share what you mean by "giving me errors"?
>
> Duane Hookom MVP
> MS Access
>
> ________________________________
> From: luvmymelody@yahoo.com
>
> 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
>
> ________________________________
> From: luvmymelody@yahoo.com<mailto:luvmymelody@yahoo.com
>
> 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
>
> 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
>
> ________________________________
> From: luvmymelody@yahoo.com<mailto:luvmymelody@yahoo.com
>
> 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;
>
__._,_.___
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (19) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar