Jim,
I would not run code that doesn't include the field names like every previous posting had:
INSERT INTO SupervisorTable ( [Dept Id], [Dept Ld], [Person Nm],
[Person Id], [Ast Sup/Lead], Supervisor, [Asc/Ast Dir], [Dept Head] )
What happened to the field names? It is much safer to be explicit rather than relying on chance.
Duane Hookom MVP
MS Access
________________________________
To: MS_Access_Professionals@yahoogroups.com
From: luvmymelody@yahoo.com
Date: Wed, 5 Feb 2014 11:31:36 -0800
Subject: Re: [MS_AccessPros] AutoExec or something else?
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?
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<mailto: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<mailto: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<mailto: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<mailto: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:
<mailto:duanehookom@hotmail.com%20wrote:%20%0b 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<mailto:luvmymelody@yahoo.com%3cmailto: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<mailto:luvmymelody@yahoo.com%3cmailto: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 (22) |
Tidak ada komentar:
Posting Komentar