VBA:为什么我的INSERT代码不起作用? [英] VBA: Why is my INSERT code not working?
问题描述
几周前，我已经开始执行此操作，但是现在我不确定该执行什么操作，从而使其不再起作用.我什至都没有收到错误消息，以找出可能出问题的地方.当我单击要在表中插入一行的按钮时，没有任何反应.清除表单并重新查询表，但是代码的INSERT部分不执行任何操作.
I had this working a few weeks ago but now I'm not sure what I did that made it not work anymore. I don't even get an error message to figure out what could be wrong. When I click the button I made to insert a row into the table, nothing happens. The form gets cleared and the table gets requeried, but the INSERT part of the code doesn't do anything.
Public Sub Command125_Click()
'Add row for downtime
Dim dbsCurrent As Database
Set dbsCurrent = CurrentDb
dbsCurrent.Execute " INSERT INTO tbl_Downtime " _
& "(job, suffix, production_date, reason, downtime_minutes, comment, shift) VALUES " _
& "('" & Me.Text116 & "','" & Me.Text118 & "','" & Me.Text126 & "','" & Me.Text121 & "','" & Me.Text123 & "','" & Me.Text128 & "','" & Me.Text144 & "');"
Call ClearControl(Me.Text116)
Call ClearControl(Me.Text118)
Call ClearControl(Me.Text126)
Call ClearControl(Me.Text121)
Call ClearControl(Me.Text123)
Call ClearControl(Me.Text128)
Call ClearControl(Me.Text144)
Me.subrpt_DowntimeTable.Requery
End Sub
我正在尝试基于@Hambone的答案的代码:
Public Sub Command125_Click()
Dim dbsCurrent As Database
Set dbsCurrent = CurrentDb
Dim query As QueryDef
Dim sql As String
For Each query In CurrentDb.QueryDefs
If query.Name = "InsertDowntime" Then
Exit For
End If
Next query
If query Is Nothing Then
sql = "parameters " & _
"P1 text, P2 text, P3 Date, P4 Text, P5 Number, P6 Text, P7 Text;" & _
"insert into [tbl_Downtime] " & _
"(job, suffix, production_date, reason, downtime_minutes, comment, shift) " & _
" VALUES ([P1], [P2], [P3], [P4], [P5], [P6], [P7])"
Set query = CurrentDb.CreateQueryDef("InsertDowntime", sql)
End If
query.Parameters("P1").Value = "test1"
query.Parameters("P2").Value = "test2"
query.Parameters("P3").Value = Now()
query.Parameters("P4").Value = "test3"
query.Parameters("P5").Value = 15
query.Parameters("P6").Value = "Miles O'Brien is a darn good transporter chief"
query.Parameters("P7").Value = "test6"
query.Execute
MsgBox query.Parameters("P1").Value & query.Parameters("P2").Value & query.Parameters("P3").Value & query.Parameters("P4").Value & query.Parameters("P5").Value & query.Parameters("P6").Value & query.Parameters("P7").Value
Me.subrpt_DowntimeTable.Requery
End Sub
推荐答案
MarkB和gmiley关于使用参数绝对正确.前面多了一些代码，后来又节省了无数小时.而且，这是个好习惯.
MarkB and gmiley are absolutely right about using parameters. It's a little more code up front and countless hours saved later. And, it's a good practice to get into.
也就是说，对于本机Access查询(不是ADO数据库查询)，它不是世界上最简单的过程.我认为，在您执行一两次之后，普通的ADO东西就开始变得有意义了，但是对于Access查询，我仍然不得不回过头来抄袭旧的示例才能使其正常工作.
That said, for a native Access query (not a ADO Database query), it's not the most straight-forward process in the world. The normal ADO stuff, in my opinion, starts to make sense after you do it a time or two, but for an Access query, I still have to go back and plagiarize old examples to get it to work.
在您的情况下，我认为这样可以解决问题:
In your case, I think something like this will do the trick:
Dim query As QueryDef
Dim sql As String
For Each query In CurrentDb.QueryDefs
If query.Name = "InsertDowntime" Then
Exit For
End If
Next query
If query Is Nothing Then
sql = "parameters " & _
"P1 text, P2 text, P3 Date, P4 Text, P5 Number, P6 Text, P7 Text;" & _
"insert into [tbl_Downtime] " & _
"(job, suffix, production_date, reason, downtime_minutes, comment, shift) " & _
" VALUES ([P1], [P2], [P3], [P4], [P5], [P6], [P7])"
Set query = CurrentDb.CreateQueryDef("InsertDowntime", sql)
End If
query.Parameters("P1").Value = "test1"
query.Parameters("P2").Value = "test2"
query.Parameters("P3").Value = Now()
query.Parameters("P4").Value = "test3"
query.Parameters("P5").Value = 15
query.Parameters("P6").Value = "Miles O'Brien is a darn good transporter chief"
query.Parameters("P7").Value = "test6"
query.Execute
您正在从文本框中提取数据.我使用硬编码的值来证明，如果您的值不是全部文本，那么这也可以管理数据键入.无需引用"文本或#hash#日期.您显然可以将其更改回Me.TextBox123，并更改数据类型以匹配tbl_Downtime中的实际字段.
You were pulling your data from text-boxes. I used hard-coded values to demonstrate that this also manages data-typing if your values are not all text. No need to 'quote' text or #hash# dates. You can obviously changes these back to Me.TextBox123 and alter the data types to match your actual fields in tbl_Downtime.
-编辑15年3月3日-
-- edit 12/3/15 --
query.Parameters之前的For Each query In CurrentDb.QueryDefs中的整个代码段，如果您已经有一个包含此查询文本的查询，则可以从理论上讲(如您进入Access一样) ，创建了一个查询，从设计"视图转到"SQL"视图，并在其中键入并命名为InsertDowntime):
The entire section of code from For Each query In CurrentDb.QueryDefs all the way prior to query.Parameters could theoretically be omitted if you already had a query with this query text in it (as in you went into Access, created a query, went from Design view to SQL view and typed this in and named it InsertDowntime):
parameters
P1 text, P2 text, P3 Date, P4 Text, P5 Number, P6 Text, P7 Text;
insert into [tbl_Downtime]
(job, suffix, production_date, reason, downtime_minutes, comment, shift)
VALUES ([P1], [P2], [P3], [P4], [P5], [P6], [P7])
因为您不这样做，所以我只是通过代码创建了它.如果尝试再次创建它，则Access将呕吐，因为InsertDowntime已经存在.
Because you don't, I just created that through code. If you tried to create it again, Access would puke because InsertDowntime already exists.
无论哪种方式，一旦存在，就可以通过说
Either way, once it exists, the way you can manage it is by saying
Dim query As QueryDef
Set query = CurrentDb.QueryDefs("InsertDowntime")
然后其他所有 应该就是我所拥有的.
And then everything else should be as I have it.
就个人而言，我将使用选项2 －在Access中创建查询并将其保留为持久对象，并按照我上面列出的方式对其进行访问.我想我可以这么说，但是您的问题是一个VBA式的问题，我还是保留了VBA －再者，我认为动态创建查询的能力很酷.
Personally, I would go option 2 -- create the query in Access and keep it as a persistent object and access it the way I just listed above. I suppose I could have said that, but yours was a VBA-ish question, and I kept it VBA -- plus, I thought the ability to create a query dynamically is sort of cool.
这篇关于VBA:为什么我的INSERT代码不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
