在ms访问中将记录插入表中 [英] Insert a record into table in ms access

问题描述

我需要使用ms access 2007创建一个小的应用程序。我需要的只是创建一个表单，用于处理与几个db表相关的输入/输出。
我创建了这些表格：患者，治疗和实验结果。

患者表中的主键是ID，它在治疗和实验结果表中作为外键，它被命名为patientID。

我还创建了一个我在本问题开头提到的表单。它有多个选项卡。第一个选项卡打开/创建病人，第二个选项卡用于输入要输入到LabResults表中的数据。

我的问题：我在第二个选项卡上添加了一个按钮，我想附上一个'action'，它会设置一个INSERT查询，其中包含选项卡2中字段（控件）的值，以及从tab1中获取的ID字段（与患者表中的患者ID相对应），然后执行查询。

现在我试图自己做到这一点，但收效甚微。此外，搜索MS网站的解决方案是很难的，因为它总是显示结果中有'查询'：）...并且查询不是我想要使用的smt。 （但是，我会接受任何解决方案）。

Thx

表：

患者

  ID  - 主键，自动生成
 patientID  - 患者记录的内部编号。我可以用这个，但这会让我的生活复杂化:) 
性别
年龄
 dateOfDiagnose 
  

- 字段名称实际上是在塞尔维亚语中，但字段名称并不重要

labtests

  ID  - 主键
 patientID  - 外键，来自患者表
 ...一堆数值数据:) 
  

还有2个表格，但它们基本反映了一些附加信息，并不是重要的。

我的表单需要让用户输入关于患者的数据，然后在治疗过程中在labtests表格中输入多行。有两种类型的治疗，并有一个表相关的，但他们只有几个领域，包含关于治疗开始的信息，其余是关于实验室测试的信息。

解决方案

很可能在VBA下运行SQL，但我怀疑简单地设置窗体可能更容易。例如，如果您将ID设置为Link Child&主实验室结果子窗体（我希望您正在使用）的字段，然后它会自动填写。此外，还可以将控件的默认值设置为之前以非常少的代码输入的值。因此，我建议您添加一些关于您想要实现的注释。

基于后续评论的其他注释

从您的意见中可以看出，您至少有三个相关的表格：

患者 p>

 PatientID PK，计数器

治疗

  TreatmentID PK，Counter 
 PatientID FK 
 TreatmentTypeID FK 
   
 
 
 LabResultID PK， Counter 
 TreatmentID FK 
 PatientID FK < - 可选，可通过TreatmentID获得
 LabResultTypeID FK 
  
另外，您需要一个治疗类型表格，列出两种当前的治疗方法，并允许进一步的治疗类型：       
 
 
  TreatmentTypeID PK，计数器
 TreatmentDescription 
  
您还需要： 
 
 
 < LabResultTypes  
  LabResultTypeID PK，计数器<  - 可以在LabResults表中引用
 TreatmentTypeID FK 
  
除了那些建议之外，还有一些PK的参数，因为您拥有自然键，但我认为使用Access使用自动编号更容易，但是，您需要自然键的索引。
 
 
 
我强烈建议所有表格还包含创建的日期，日期修改/更新以及两者的用户ID。访问不保存这些数据，你必须自己动手。创建日期可以通过默认值进行设置，如果您使用的是2010版本，其他字段可以具有触发器（数据级别的宏），否则，您将需要代码，但这不是太困难。还要注意，可以使用这些相同的触发器来插入相关记录：认识Access 2010宏设计师。  
 
 
您的表格将包含每个患者和两个子表格治疗和实验室结果的记录。治疗子窗体绑定到治疗表上，其具有用于LinkChild和主域的PatientID以及用于治疗类型ID的组合框：
 Rowsource：SELECT TreatmentTypeID，TreatmentDescription FROM TreatmentTypes 
 ColumnCount：2 
 BoundColumn：1 
在处理类型添加到处理子表单后，您可以运行查询或运行SQL在VBA下。您可以对处理子表单或按钮使用After Insert事件，After Insert事件具有优势，但只有在用户保存记录或从记录中移出时才会触发，这是相同的事情。从处理子表单和After插入事件开始，SQL将处于以下位置：
 ''参考Microsoft DAO xx对象库
 Set db = CurrentDB 
 sSQL =INSERT INTO LabResults（TreatmentID，PatientID，LabResultTypeID）_ 
& SELECT_ 
& Me.TreatmentID& ，_ 
& Me.PatientID& ，_ 
& LabResultTypeID FROM LabResultTypes_ 
& WHERE TreatmentTypeID=& Me.TreatmentTypeID 
 db.Execute sSQL 
 
 MsgBoxRecords inserted：& db.RecordsAffected 
 
 Me.Parent.LabResults_Subform.Form.Requery 
  
查询还应该包含日期和用户名，如上所示，如果您的版本低于2010年，并且您尚未设置触发器。
 
 
 
您将被诱惑使用数据表，我怀疑，对于表单。我建议你抵制诱惑，使用单一形式的连续形式，他们更强大。对于一个有趣的方法，您可能希望查看Northwind样本数据库客户订单表单。
 
I need to create a small "application" using ms access 2007. All I need is to create a form that will take care of input/output related to a few db tables. 
I created the tables: patients, treatments and labresults.

Primary key in patients table is ID, and it serves as a foreign key in treatments and labresults tables, where it is named patientID.

I also created a form that I mentioned in the beginning of this question. It has multiple tabs. 1st tab opens/creates the patient, and the second one is used to enter data that is to be entered into the labresults table. Same applies to treatments table.

My problem: I added a button to the 2nd tab, and I want to attach an 'action' that will set an INSERT query with values taken from fields (controls) in tab 2, together with ID field taken from tab1 (that corresponds with patient ID from patients table), and then execute a query.

Right now I'm trying to achieve this myself, but with little success. Also, searchin MS site for solution(s) is kind of hard, since it always show results that have 'query' in it :)... And query isn't smt I want to use. (However, I'll accept any solution).

Thx

Tables: 

patients
ID - primary key, autogenerated
patientID - internal number of the patient record. I could've used this, but it would complicate my life later on :)
gender
age
dateOfDiagnose
- field names are actually in Serbian, but field names aren't that much important

labtests
ID - primary key
patientID - foreign key, from patients table
... bunch of numerical data :)
There are 2 more tables, but they basically reflect some additional info and are not as important.

My form needs to enable user to enter data about the patient, and then enter several rows in labtests table, as treatment progresses. There are 2 types of treatment, and there is a table related to that, but they only have few fields in it, containint info about the start of the treatment, and the rest is info about lab tests.
解决方案
It is quite possible to run SQL under VBA, but I suspect that it may be easier to simply set up the form properly. For example, if you set up the ID as the Link Child & Master fields for the lab results subform (which I hope you are using), then it will be filled in automatically. Furthermore, it is also possible to set the default value of a control to the value that was previously entered with very little code. I therefore suggest that you add some notes on what you wish to achieve.

Some further notes based on subsequent comment

From your comments, it appears that you have a minimum of three relevant tables:

Patients
PatientID PK, Counter
Treatments
TreatmentID PK, Counter
PatientID FK
TreatmentTypeID FK
LabResults
LabResultID PK, Counter
TreatmentID FK 
PatientID FK <-- Optional, can be got through TreatmentID
LabResultTypeID FK
In addition, you will need a TreatmentTypes table that list the two current treatments and allows for further treatment types:

TreatmentTypes 
TreatmentTypeID PK, Counter 
TreatmentDescription 
You will also need:

LabResultTypes
LabResultTypeID PK, Counter <-- This can be referenced in the LabResults table
TreatmentTypeID FK
There are arguments for PKs other than those suggested, in that you have natural keys, but I think it is easier when working with Access to use autonumbers, however, you will need indexes for the natural keys.

I strongly recommend that all tables also include date created, date modified/updated and a user ID for both. Access does not keep this data, you must do it yourself. Date created can be set by a default value, and if you are using the 2010 version, the other fields can have 'triggers' (data-level macros), otherwise, you will need code, but it is not too difficult. Note also that these same 'triggers' could be used to insert relevant records : Meet the Access 2010 macro designer. 

Your form will contain records for each patient and two subforms, Treatments and LabResults. The Treatments subform is bound to the Treatments table having PatientID for LinkChild and Master fields and a combobox for TreatmentTypeID:
Rowsource: SELECT TreatmentTypeID, TreatmentDescription FROM TreatmentTypes
ColumnCount: 2
BoundColumn: 1
After a treatment type is added to the Treatments subform, you can run a query, or run SQL under VBA. You can either use the After Insert event for the form contained by the treatments subform or a button, the After Insert event has advantages, but it is only triggered when the user saves the record or moves from the record, which is the same thing. Working from the treatments subform, and the After Insert event, the SQL would be on the lines of:
''Reference Microsoft DAO x.x Object Library
Set db = CurrentDB
sSQL = "INSERT INTO LabResults (TreatmentID, PatientID, LabResultTypeID) " _
     & "SELECT " _
     & Me.TreatmentID & ", " _
     & Me.PatientID & ", " _
     & "LabResultTypeID FROM LabResultTypes " _
     & "WHERE TreatmentTypeID " = & Me.TreatmentTypeID
db.Execute sSQL

MsgBox "Records inserted: " & db.RecordsAffected

Me.Parent.LabResults_Subform.Form.Requery
The query should also include the date and username, as suggested above, if your version is less than 2010 and you have not set up triggers.

You will be tempted to use datasheets, I suspect, for the subforms. I suggest you resist the temptation and use either single forms of continuous forms, they are more powerful. For an interesting approach, you may wish to look at the Northwind sample database Customer Orders form.

                        
这篇关于在ms访问中将记录插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！