插入每隔一行从一个表到另一个（VBA的MS Access 2010）(Inserting every

我与表的产品，这是我命名Products_Backup的手动创建的空复制工作。我想要做的是从产品的“香料”的范畴每隔一行插入到这个空Products_Backup表，因为将有来自共12行只有6行，即在产品表下的“香料”的范畴。问题是，我不知道该怎么做。我试图用MOD运营商对新创建的产品ID，但我的导师告诉我，这是不是一个适当的解决方案，因为他可以轻易地改变这个产品ID的价值，我会得到奇数行，而不是偶数。

    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

所以用这个，我可以插入所有的12个记录到Products_Backup，与MSGBOX告诉我，当插入第10的纪录。但我仍然不知道做每隔一行插入Products_Backup得到6条东西。

Answer 1:

Dim booEveryOther as Boolean
booEveryOther = False

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

   booEveryOther = Not booEveryOther

只需使用被设置为一个布尔值， Not本身与每一个新的纪录。

Answer 2:

我想这应该做的更好

While Not rst.EOF
  s1 = " INSERT INTO Products_Backup (ProductName, ProductID, CategoryNumber, OrderedUnits) " & _
       " VALUES ('" & rst!ProductName & "', " & I & " , '" & rst!CategoryNumber & "', '" & rst!OrderedUnits & "');"
  db.Execute s1

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

  I = I + 1
  rst.MoveNext
Wend