添加记录表而不使用内联查询VBA /访问 [英] Adding records to table without using inline query in VBA/Access

问题描述

我是完全新的VBA。有人告诉我，记录用表和一个保存按钮添加到一个表，并给出了一些非常基本的说明。虽然我已经做到了这一点与在线查询，我被告知要遵循一些严格的方法，如的QueryDef / QueryDefs和.Parameters的使用。

I am completely new to VBA. I have been told to add records to a table by using a form and a Save button and given some very basic instructions. While I have achieved this with inline query, I have been told to follow some strict methods like usage of QueryDef/QueryDefs and .Parameters.

到目前为止，我想一个很基本的项目，只是为了掌握的概念，但我不能给任何记录添加到空表。如果该表不是空的（我手动输入记录），每当我点击Save按钮保存新记录，添加记录数在某种程度上增加了一倍，每个实例。例如，当我保存的第一时间，1记录被添加，第二时间2记录相同类型的加入，4加入等第三时间

So far I am trying a very basic project, just to grasp the concepts, but I am unable to add any record to an empty table. In case the table is not empty(I manually enter a record), whenever I click the Save button for saving newer records, the number of records added are somehow doubling with each instance. For example, when I Save for the 1st time, 1 record is added, 2nd time 2 records of the same type is added, 3rd time 4 are added and so on.

表（tbl_test）有2场 - > ID（主键），来源（长文本）和注册号码（号码）

The table(tbl_test) has 2 fields --> ID(primary key), Source(Long Text) and Reg No (Number).

查询（qry_test）制成的附加功能，我被告知要加EX pressions这使得code这样的 -

The query(qry_test) is made with the Append feature and I have been told to add expressions which makes the code like this -

INSERT INTO tbl_test ( Source, [Reg No] )
SELECT [strSource] AS Expr1, [lngRegNo] AS Expr2
FROM tbl_test;

窗体有2场的来源（txt_Source）和注册号（txt_RegNo），其中有空白的记录源（没有限制）。保存按钮具有下列事件过程 -

The form has 2 fields for Source(txt_Source) and Reg No(txt_RegNo) which have blank Record Sources (Unbound). The Save button has the following Event Procedure -

Private Sub btn_save_Click()

Dim qdf As QueryDef

Set qdf = CurrentDb.QueryDefs("qry_test")

qdf.Parameters("strSource") = Me.txt_Source
qdf.Parameters("lngRegNo") = Me.txt_RegNo

qdf.Execute

End Sub

我对VBA零知识会很高兴地接受任何帮助。这将是巨大的，如果我得到任何形式的来源$ C ​​$ C，它会向所有的细节有关从储蓄形式记录和编辑它们使用这些的QueryDef，参数，记录的东西。

I have zero knowledge about VBA and would gladly accept ANY help. It would be great if I get any sort of source code that will explain to all the details about saving records from forms and editing them using these querydef, parameter and recordset stuff.

推荐答案

欢迎计算器！

如果您使用的是形式，收集存储在一个表中记录的数据，在运行通过 QueryDefs 保存追加查询要做到这一点，在我看来，不是最好的方法。

If you are using a form to collect data for records that are stored in a table, running a saved append query by QueryDefs to do this is, in my opinion, not the best method.

虽然追加查询并添加新记录到一个现有的表，我会倾向于使用追加查询，我已经得到的多的建立，我想添加到现有的表。记录

Whilst an append query does add new records to an existing table, I would tend to use an append query where I've got multiple established records that I want to add to an existing table.

如果我设立，旨在收集新数据，新记录1-AT-A-时间数据输入表，追加查询并没有真正适合这个目的。

If I'm setting up a data entry form that is designed to collect new data for new records 1-at-a-time, an append query doesn't really suit this purpose.

相反，也有一些内置的MS-Access窗体的设计，以帮助收集数据并将其保存到表中的功能。这是因为形式都非常打算设置，使用户可以从一个控制，用户友好的方式表的记录进行交互，而不是直接与表对象本身进行交互。

Instead, there are a number of features built in to the design of MS-Access forms to help collect data and save it to a table. This is because forms are very much intended to be set up so that users can interact with records from a table in a controlled, user-friendly way rather than interact directly with the table object itself.

在这种情况下表单的第一个也是最重要的特征是可能的形式的记录源属性。当你创建一个新的形式，去到设计视图的形式和打开窗体的属性表（F4键）。在窗体的属性表的数据选项卡中，你会发现的记录源属性：

The first and most important feature of a form in this context is probably the form's record source property. When you create a new form, go in to design view for the form and open the form's property sheet (F4 key). In the "Data" tab of the form's property sheet you'll find the record source property:

记录源本质上一组记录连接您的形式，无论这些是在一个表对象，查询对象或一个SQL查询字符串的记录。

The record source essentially connects your form with a set of records, whether those be records in a table object, a query object or an sql query string.

在你的情况下，它会更好，在我看来，要参考它在你的窗体的记录源您的 tbl_Test 表绑定到你的表单：

In your case it would be better, in my opinion, to bind your tbl_Test table to your form by referring to it in your form's record source:

这会看起来像什么也没发生在你的形式，但如果你现在打开添加现有油田面板（Alt + F8），你会发现，与相关的字段中输入您的 tbl_Test 表提供给你：

It will seem like nothing has happened to your form, but if you now open the "Add Existing Fields" panel (alt + F8), you'll notice that the fields associated with your tbl_Test table are available to you:

将它们拖动到您的窗体的节...

Drag them to the detail section of your form...

然后投入到窗体视图的形式：

Then put your form in to Form View:

基本上是你和你的用户所看到的是在您的 tbl_Test 第一个空白记录，但显示的形式，而不是上。

Essentially what you and your users are seeing is the first blank record in your tbl_Test, but displayed on a form instead.

输入这些字段的数据表单上...

Entering data in these fields on the form...

......将会把这些数据中我们在窗体的记录源指定的表...

...will put that data in to the table we specified in the form's record source...

所以，希望你可以看到，窗体的记录源属性设置为你的表，比试图获得追加查询从表单收集数据，并将其交付您的餐桌干净多了。

So hopefully you can see that setting the form's record source property to that of your table, is much cleaner than trying to get an append query to collect data from your form and deliver it your table.

在这一点上你可能会问了几个问题：

At this point you're probably asking a few questions:

当我已经填写了字段的记录在我的表，我如何保存记录？

更可以说这一点，但为了简便起见，我建议你使用一个命令按钮来运行一些VBA来保存记录;类似于你做了什么，而是利用形式的脏属性，而不是使用追加查询：

More can be said about this, but for brevity, I'd recommend using a command button for running some vba to save the record; similar to what you've done, but utilising the form's Dirty property instead using an append query:

下面的click事件VBA为我节省按钮的例子：

Here's the click event VBA for my save button example:

Private Sub cmdSave_Click()

    If Me.Dirty Then

        Me.Dirty = False

    End If

End Sub

Me.Dirty 是一个布尔（TRUE或FALSE）设置的形式;本质上，它被自动设置为true，当用户改变一些东西的形式。为了保存这些变化， Me.Dirty 设置将被设置为False。

Me.Dirty is a Boolean (True or False) setting for the form; essentially it is automatically set to True when a user changes something on the form. To save those changes, the Me.Dirty setting will have to be set to False.

Me.Dirty 有点像在一个羊圈平开门。当牧羊人把羊（数据）中的笔（形式），他们将打开大门笔。开放式门等的形式的 Me.Dirty 被设置为True。锁定绵羊（数据）中，栅极需要被关闭，或在形式的情况下，在 Me.Dirty 属性需要被设置为假。上述基本的VBA检查是否门被打开了，如果有人将其关闭。

Me.Dirty is a bit like the swing gate on a sheep pen. When a shepherd puts a sheep (data) in the pen (form) they will open the gate to the pen. The open gate is like the form's Me.Dirty being set to True. To lock the sheep (data) in, the gate needs to be closed, or in the case of forms, the Me.Dirty property needs to be set to False. The VBA above essentially checks to see if the gate was opened and if it was to close it.

如何移动到表格的新纪录，一旦我保存了当前的一个？

此外，我想给用户一个命令按钮做到这一点，运行一些VBA它的单击事件：

Again, I'd give the user a command button to do this and run some VBA on its click event:

下面是VBA移动到一个新的记录：

Here's the VBA for moving to a new record:

Private Sub cmdNew_Click()

    DoCmd.GoToRecord , , acNewRec

End Sub

摘要

还有很多事情需要考虑比我在这里列出，如：

There is a lot more to consider than what I've outlined here, such as:

• 验证的数据将被保存在
• 检查等形式的活动（如关闭），以确保数据录入不丢失
• 导航到现有的记录

但希望我已经给你在这里至少指向你在一个更好的方向发展。祝您好运！

But hopefully what I've given you here is at least pointing you in a better direction. Best of luck!

这篇关于添加记录表而不使用内联查询VBA /访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！