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.

-- 2015 年 12 月 3 日编辑--

-- edit 12/3/15 --

For Each query In CurrentDb.QueryDefs 到 query.Parameters 之前的整个代码部分理论上可以被省略，如果您已经有一个包含此查询文本的查询(就像您进入 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屋！