时间戳

首页 > 解决方案 > 如何使用表单中的查询记录向表中添加多条记录

ms-access - 如何使用表单中的查询记录向表中添加多条记录

问题描述

我有一个 GoatMasterTable。我根据 GoatmasterTable 针对特定条件创建了一个查询“Query1”。我已将 query1 转换为新表单中的“Query1”表单，我的标题信息很少，并使用此 Query1 表单来显示多条记录。

我想将标题信息和每一行查询写入一个名为 Medicine 的表中。

当我尝试使用以下代码时，第一行查询重复查询行数。我无法从查询表单的其余记录中选择第二行等等

我正在使用下面的代码 

Private Sub exitprograme5()

Dim i As Integer
Dim Db As Database
Dim Rs As Recordset
Dim Trn As String
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Query1")

Do While Not Rs.EOF
     'To add Next De-Warming Kid Entry
     'MsgBox " Kid De-Warming Record writing"
     Dim Db1 As Database
     Dim Rs1 As Recordset
     Set Db1 = CurrentDb
     Set Rs1 = Db.OpenRecordset("Medicine")

     Rs1.AddNew
     Rs1.Fields("dateofMedication") = Me.Text62.Value
     Rs1.Fields("InjuctionType") = Me.Combo68.Value
     Rs1.Fields("Medicine") = Me.Text58.Value
     Rs1.Fields("Dose") = Me.Text60.Value
     Rs1.Fields("Problem Summary") = "De-Warming to Kids "
     Rs1.Fields("TagID") = Me.Query1.Form!TagID
     Rs1.Fields("BreedType") = Me.Query1.Form!BreedType
     Rs1.Fields("GoatGender") = Me.Query1.Form!GoatGender
     Rs1.Fields("FemaleStatus") = Me.Query1.Form!FemaleStatus
     Rs1.Fields("BodyDescription") = Me.Query1.Form!BodyDescription
     Rs1.Fields("Category") = Me.Query1.Form!Category
     Rs1.Fields("HealthStatus") = Me.Query1.Form!HealthStatus
     Rs1.Update
     Rs1.Close
     Set Rs1 = Nothing
     Db1.Close
     Rs.MoveNext
Loop
'DoCmd.Close
Rs.Close
Set Rs = Nothing
Db.Close

End Sub

标签： ms-access

解决方案

代码不引用rs. 而不是Me.Query1.Form应该是rs!。在循环外打开和关闭 Rs1 和 Db1。

INSERT SELECT 操作 SQL 可能更有效。以下假设字段为文本类型，但 dateofMedication 除外。如果任何字段是数字类型，请删除撇号分隔符。

Private Sub exitprograme5()

  'To add Next De-Warming Kid Entry
  'MsgBox " Kid De-Warming Record writing"
  CurrentDb.Execute "INSERT INTO Medicine(dateofMedication, InjuctionType, Medicine, Dose, " & _
  "[Problem Summary], TagID, BreedType, GoatGender, FemaleStatus, BodyDescription, Category, HealthStatus) " & _
  "SELECT #" & Me.Text62 & "#,'" & Me.Combo68 & "','" & Me.Text58 & "','" Me.Text60 & "','De-Warming to Kids'," & _
  "TagID,BreedType,GoatGender,FemaleStatus,BodyDescription,Category,HealthStatus FROM Query1"

End Sub

推荐阅读