Many examples I looked at did not have the ampersands.
But it works now.
Thank You Very Much.
Jim Wagner
On Wednesday, February 5, 2014 12:13 PM, "Embrey, Darrell" <DEmbrey@bcbsm.com> wrote:
You need the concatenation operator on each line similar to the following:
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;"
Darrell
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Jim Wagner
Sent: Wednesday, February 05, 2014 2:08 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] AutoExec or something else?
Sent: Wednesday, February 05, 2014 2:08 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] AutoExec or something else?
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"
Exi t_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;
>
The information contained in this communication is highly confidential and is intended solely for the use of the individual(s) to whom this communication is directed. If you are not the intended recipient, you are hereby notified that any viewing, copying, disclosure or distribution of this information is prohibited. Please notify the sender, by electronic mail or telephone, of any unintended receipt and delete the original message without making any copies.
Blue Cross Blue Shield of Michigan and Blue Care Network of Michigan are nonprofit corporations and independent licensees of the Blue Cross and Blue Shield Association.
__._,_.___
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (21) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar