追加查询问题 [英] Append Query problem

问题描述

你好

我自己写这个代码它不是很完美，但它可以做我需要的东西



结果我得到一个数据放置到两个表一个tblLeave和第二个tblLeaveDate

首先只是记录一个员工离开第二个它只是在不同的一天分开的假期日期，以确保我可以跟踪并显示他们的离开出现等等



问题是大多数时候我需要更新离开的日期，因为ppl没有回到最新添加到数据库的日期他们刚刚回来的时间



我在想使用附加查询这意味着我只是从tblLeave创建一个表单并从tblLeaveDate添加一个子表单我添加了一个VBA代码，它将使用来自tblLeave的离开ID将新日期附加到tblLeaveDates



但我不知道如何通过VBA创建附加查询一个人可以帮助并告诉我这样做的正确方法吗？



我知道我可以创建查询购买向导然后只是分配它做按钮但我想学习VBA和ADO

Hello
I Write my self This Code it's not perfect but it do what I need to get

In result I getting a data place to two tables one tblLeave and second tblLeaveDate
First just recording a Leave by Employee Second its just a dates of the leave separated on separate day to make sure I can Track and also show in what they the leave appear ect

The problem is most of the time I need to update the dates of the Leave as ppl not coming back in the date added to the data base most of the time they just coming back later

I was thinking to use something like appended Query that's mean I will just create a Form from tblLeave and add a Sub Form from tblLeaveDate and I add a VBA Code what will append new dates to tblLeaveDates using ID of the Leave from tblLeave

But I don't relay know how to create Append Query by VBA any one can help and show me correct way to do that ??

I know I can create Query buy wizard then just assign it do the button but I want t learn VBA and ADO

Private Sub addRecord_Click()
  
    Dim db As Database
    Dim rs As Recordset
    Dim rs2 As Recordset
    Dim i As Integer
    Dim X As Integer
  
    'Connections
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblLeave")
    Set rs2 = db.OpenRecordset("tblLeaveDate")
    
    
    'Recognizing the ID
    
    X = db.OpenRecordset("SELECT @@Identity")(0)
    
      
     rs.AddNew
            
   'Checking if The Required fields was Full in
   
    If Shift = "" Then
    
        MsgBox "Employee Is Required"
        
    ElseIf txtEmployeeNr = "" Then
     
        MsgBox "Employee Is Required"
        
    ElseIf txtReasonType = "" Then
     
        MsgBox "Reason Type Is Required"
        
     Else
     'Checking for 0 As sometime @@Identity can place 0
        
    If X = 0 Then
    
       X = LastID + 1
    
    End If
   
        rs!LID = X
        rs!EmployeeNr = txtEmployeeNr
        rs!ReasonType = txtReasonType
        rs!Details = txtDetails
        rs!RTWCompleted = cheRTW
        rs!StartDate = txtStartDate
        rs!EndDate = DateofRTW
        rs!AddedBy = [Forms]![frmEmployeeList]![cboUser].Column(1)
        
        rs.Update
        
    For i = 0 To txtDays
        
    
        rs2.AddNew
        
        rs2!LID = X
        rs2!CalendarDate = txtStartDate + i
        
        
        rs2.Update
        
    Next i
        
                
    ' Clean Up and Close
    rs.Close
    rs2.Close
    
    Set rs = Nothing
    Set rs2 = Nothing
    Set db = Nothing
    
    MsgBox "The Adding Process was Successful"

推荐答案

在VBA中运行任何动作查询的最简单方法是使用DoCmd.RunSQL这样：



Dim sSQL as string



sSQL =INSERT INTO myTable（Field1，Field2）VALUES（ &myVar1&，&myVar2&）

DoCmd.RunSQL sSQL



显然编写适当的Access SQL查询！



请注意您的代码发布不使用ADO，它使用DAO（Jet引擎数据对象模型）。多年来，MS告诉我们转向ADO，但在最近的Access版本中，他们一直在推动我们回到DAO。我还建议为OpenRecordset提供其他参数，因为默认情况下你会得到一个带动态光标的动态集。如果您只读，只向前滚动，您将通过使用仅向前快照获得更好的性能。

The easiest way to run any "action" query in VBA is to use DoCmd.RunSQL like this:

Dim sSQL as string

sSQL = "INSERT INTO myTable(Field1, Field2) VALUES(" & myVar1 & ", " & myVar2 & ")"
DoCmd.RunSQL sSQL

Obviously write the appropriate Access SQL query!

Please note that the code you posted does not use ADO, it uses DAO (Jet engine data object model). For years MS told us to move to ADO, but in recent releases of Access they've been nudging us back to DAO. I'd also recommend supplying other arguments to "OpenRecordset" as by default you get a Dynaset with a dynamic cursor. If you are read only, scrolling forward only you will get better performance by using a forward only Snapshot.

这篇关于追加查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！