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--

Use single quotes (') rather than double quotes (") inside the RunSQL statement

I am a loud man with a very large hat. This means I am in charge

I should elaborate:
.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];"

Or - to space it out a bit nicer:




I am a loud man with a very large hat. This means I am in charge

I am confused. A combo can only have one item selected at a time so no looping is involved. The reference to
the combo:

WHERE (((tblA.[Items No])=[Forms]![frmAppenCust]![Combo0])

will only use the currently selected value in the combo. Also, it will also require delimiters appropriate to the data type in the bound field field of the combo.

I'm not seeing where the Order By clause is relevant here either and, there is no need to append a single record at a time as it possible to append a whole batch with a single append. I'm also curious about the grouping in the combo rowsource and last but not least, using a space in field names in a table is not a good practice.

Without knowing what you are trying to accomplish with the combo box, it is difficult to provide assistance. I would expect that [Cust No] ought to be unique in the table on which the combo is based but that too is unclear.

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

You are right; the combo box can only use one item at a time. There is more than (1) item to select from. I wanted to loop thru each one of them without selecting them. I want to append each group automatically to the other table. This process is a resort records and get the D's on top of the A's per group of records.

In matters of style, swim with the current. In matters of principle, stand like a rock. Thomas Jefferson

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

Thank you for your suggestion,

I will try this. After I select the fields in tableA, how do I append them to tableB?

In matters of style, swim with the current. In matters of principle, stand like a rock. Thomas Jefferson

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

AllRighty Then,

The thing with the single quote is new. I will try that. I will let you know if it works.

In matters of style, swim with the current. In matters of principle, stand like a rock. Thomas Jefferson

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