p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access (http://p2p.wrox.com/forumdisplay.php?f=18)
-   -   copy and append records from table-A to table B (http://p2p.wrox.com/showthread.php?t=10250)

bhunter March 8th, 2004 05:42 PM

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

Steven March 8th, 2004 07:49 PM

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

Steven March 8th, 2004 07:52 PM

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:

Code:

.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];"




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

jurgenw March 9th, 2004 01:02 AM

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
jwelz@hotmail.com

bhunter March 9th, 2004 01:43 PM

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

bhunter March 9th, 2004 01:59 PM

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

bhunter March 9th, 2004 02:02 PM

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


All times are GMT -4. The time now is 09:50 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.