View Single Post
  #1 (permalink)  
Old March 8th, 2004, 05:42 PM
bhunter bhunter is offline
Authorized User
 
Join Date: Feb 2004
Location: Edwards AFB, California, USA.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default copy and append records from table-A to table B

I need to copy records from table-A then append them to table-B. These record were previously selected via combo box. I no longer need to select records to append to table-B. I would like to do this via code, looping thru the records and appending them to the other table in the order stored in the combo box til EOF.

[u]Combo box</u> is as follows:
SELECT tblCust.[Item No]
FROM tblCust
GROUP BY tblCust.[Item No]
ORDER BY tblCust.[Item No];


Append Query is as follows:

INSERT INTO tblB ( RecordNo, [Item No], [Cust No])
SELECT tblA.RecordNo, tblA.[Item No], tblA.[Cust No],
FROM tblA
WHERE (((tblA.[Items No])=[Forms]![frmAppenCust]![Combo0]) AND ((tblA.[Cust No]) Like "D*"))
ORDER BY tblA.RecordNo, tblA.[Item No],tblA.[Cust No];

[u]The second Append query</u> is as follows:

INSERT INTO tblB ( RecordNo, [Item No], [Cust No])
SELECT tblA.RecordNo, tblA.[Item No], tblA.[Cust No],
FROM tblA
WHERE (((tblA.[Items No])=[Forms]![frmAppenCust]![Combo0]) AND ((tblA.[Item No]) Like "A*"))
ORDER BY tblA.RecordNo, tblA.[Item No], tblA.[Run No], tblA.[Cust No];

MY CODE IS AS FOLLOWS:
Sub sResetCustID()
    Dim rstoCustNo As Recordset
    Dim defoqdf As QueryDef
    Dim stroItemNo As String
    Dim db As Object
    Set db = CurrentDb()
    Set rstoCustNo = db.OpenRecordset("pulloItemNum")
    stroItem = rstoCustNo![Item No]
    DoCmd.OpenQuery "qryApCustD"
DoCmd.OpenQuery "qryApCustA"
    rstoCustNo.MoveFirst
    Do Until rstoCustNo.EOF
    Loop
End Sub

I know this code is totaling wrong. I am looping, but when I try to append the data, I get this prompt [Forms]![frmAppenCust]![Combo0])

So I changed my Code to:
Sub sResetCustID()
    Dim rstoCustNo As Recordset
    Dim defoqdf As QueryDef
    Dim stroItemNo As String
    Dim db As Object
    Set db = CurrentDb()
   Set rstoCustNo = db.OpenRecordset("SELECT DISTINCT tblCust.[Item No]FROM tblCust ORDER BY tblCust.[Item No]")
    Set defoqdf = db.QueryDefs("apCustD")
    stroItem = rstoCustNo![Item No]
       rstoCustNo.MoveFirst
    Do Until rstoCustNo.EOF
       With DoCmd
    .RunSQL "SELECT tblA.RecordNo, tblA.[Item No], tblA.[Cust No] FROM tblA WHERE (tblA.[Item No] = ?) AND ((tblA.[Cust No] Like "D*") OR ((tblA.[Cust No] Like "A*")) ORDER BY tblA.RecordNo, tblA.[Item No], tblA.[Cust No];"
    End With

Using this code doesn't show how it is being appended to tblB.

I am using MSAccess 2002. I am getting a error on ((tblA.[Cust No] Like "D*") OR ((tblA.[Cust No] Like "A*")) It doesnt like D*


In matters of style, swim with the current. In matters of principle, stand like a rock. Thomas Jefferson
-------\\\|///
--------(oo)
--oOOo-(_)-oOOo--
__________________
In matters of style, swim with the current. In matters of principle, stand like a rock. Thomas Jefferson

-------\\\\\\|///
--------(oo)
--oOOo-(_)-oOOo--