插入每隔一行从一个表到另一个（VBA的MS Access 2010） [英] Inserting every OTHER ROW from one table to another (VBA MS Access 2010)

问题描述

（一开始，我很抱歉，如果我的英文不够清楚，我不是母语的人）。

所以我有桌子的产品，这是我命名Products_Backup的手动创建的空复制工作。我想要做的就是插入来自香料的产品类别每隔一行到这个空Products_Backup表，因为将有来自共12行只有6行，是在产品表下的香料的范畴。问题是，我不知道该怎么做。我试图用MOD运营商新创建的产品ID，但我的导师告诉我，这是不是一个妥善的解决办法，因为他可以很容易地改变该产品ID的价值，我会得到，而不是连奇数行。

 私人小组CommandButton0_Click（）
    昏暗DB数据库，第一个作为记录
    昏暗我作为整数，S，S1作为字符串

    设置DB = CurrentDb

    S =选择产品* FROM产品WHERE（（（Products.CategoryNumber）= 2））;。 这个值是香料
    设置RST = db.OpenRecordset（S）
    I = 1
    虽然没有rst.EOF
      S1 =INSERT INTO Products_Backup（产品名称，产品ID，CategoryNumber，OrderedUnits）VALUES（与＆amp;！首先产品名称＆安培;'，＆安培; I和，与＆amp;！首先CategoryNumber＆安培;'，'与＆amp;＆OrderedUnits安培RST;！）;

      MSGBOX（记录插入）

    db.Execute S1

      I = I + 1
      rst.MoveNext

       如果我mod10 = 0然后
         MSGBOX（插入＆放大器; I和.record）
       结束如果

    蜿蜒

    rst.Close

    db.Close

    结束小组
 

所以，这个我可以插入所有的12条到Products_Backup，与MSGBOX告诉我插入10日记录时。 但我仍然不知道该怎么做才能插入每隔一行到Products_Backup获得6条。我将AP preciate任何帮助。

解决方案

暗淡booEveryOther布尔 booEveryOther =假 虽然没有rst.EOF    如果booEveryOther然后       S1 =INSERT INTO ..​​.    结束如果    booEveryOther =不booEveryOther

只需使用设置为 Boolean值不本身与每一个新的记录。

(At first I am sorry if my English is not clear enough, I am not a native speaker).

So I am working with manually created empty copy of table Products, which I named Products_Backup. What I want to do is to insert every other row from "Spices" category of products into this empty Products_Backup table, since there would be only 6 rows from total of 12 rows, that are in Products table under "Spices" category. The problem is that I don't know how to do that. I tried to use MOD operator for newly created ProductID, but my mentor told me that it is not a proper solution, since he could easily change this ProductID's value and I would get odd rows instead of even.

    Private Sub CommandButton0_Click()
    Dim db As Database, rst As Recordset
    Dim I As Integer, s, s1 As String

    Set db = CurrentDb

    s = "SELECT Products.* FROM Products WHERE (((Products.CategoryNumber)=2));" ' This value is for Spices
    Set rst = db.OpenRecordset(s)
    I = 1
    While Not rst.EOF
      s1 = "INSERT INTO Products_Backup (ProductName, ProductID, CategoryNumber, OrderedUnits) VALUES ('" & rst!ProductName & "', " & I & " , '" & rst!CategoryNumber & "', '" & rst!OrderedUnits & "');"

      MsgBox ("Record inserted")

    db.Execute s1

      I = I + 1
      rst.MoveNext

       If I Mod 10 = 0 Then
         MsgBox ("Inserted  " & I & ".record")
       End If

    Wend

    rst.Close

    db.Close

    End Sub

So with this I can insert all 12 records into Products_Backup, with MsgBox telling me when 10th record was inserted. But I still have no idea what to do to insert every other row into Products_Backup to get 6 records. I will appreciate any help.

解决方案

Dim booEveryOther as Boolean
booEveryOther = False

While Not rst.EOF
   If booEveryOther Then
      s1 = "INSERT INTO ...
   End If

   booEveryOther = Not booEveryOther 

Just use a Boolean value that is set to Not itself with every new record.

这篇关于插入每隔一行从一个表到另一个（VBA的MS Access 2010）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！