Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old February 17th, 2004, 05:18 PM
Authorized User
 
Join Date: Feb 2004
Location: Edwards AFB, California, USA.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default QueryDef and/or Recordset?

I need to append data from one table to another table using the results from a group query as the parameter in the append query. The group query once was part of a combo box.

The purpose of this query is to resort data by flight number. The only problem is the criteria. The sort is "D" before "A".
Therefore, I need two append queries for each group.

I need to run this process via code instead of by form. The pulldown box is as follows: SELECT tblFTP.[Flight No]FROM tblFTP GROUP BY tblFTP.[Flight No] ORDER BY tblFTP.[Flight No];

The append queries are as follows: INSERT INTO tblB (the other table) ( RecordNo, [Flight No], [Run No], [TIS No], [Date Flown])
SELECT tblFTP.RecordNo, tblFTP.[Flight No], tblFTP.[Run No], tblFTP.[TIS No], tblFTP.[Date Flown]FROM tblFTP
WHERE (((tblFTP.[Flight No])=[Forms]![frmAppenFTP]![Combo0]) AND ((tblFTP.[Run No])[u] Like "D*"))</u>
ORDER BY tblFTP.RecordNo, tblFTP.[Flight No], tblFTP.[Run No], tblFTP.[TIS No];

The second append query is the same but its criteria is [u]Like A*"</u>

I do not wish to use the combo box to select the groups to append to the other table. I would like to append each group automatically clicking a command button, looping thru the group or Combo box, appending data until EOF.
__________________
In matters of style, swim with the current. In matters of principle, stand like a rock. Thomas Jefferson

-------\\\\\\|///
--------(oo)
--oOOo-(_)-oOOo--
  #2 (permalink)  
Old February 18th, 2004, 04:48 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

You didn't say which version of Access you are using but here are my suggestions:
1) The query you use to select the [Flight No] doesn't need a group by if you can use
Code:
 SELECT DISTINCT tblFTP.[Flight No]FROM tblFTP ORDER BY tblFTP.[Flight No]
2) You might not need two append queries, I think you can just have
Code:
SELECT tblFTP.RecordNo, tblFTP.[Flight No], tblFTP.[Run No], tblFTP.[TIS No], tblFTP.[Date Flown]FROM tblFTP
WHERE (tblFTP.[Flight No] = ?) AND ((tblFTP.[Run No] Like "D*") OR ((tblFTP.[Run No] Like "A*")) ORDER BY tblFTP.RecordNo, tblFTP.[Flight No], tblFTP.[Run No] DESC, tblFTP.[TIS No];
as your sub query with ? to be replaced as below.
3) Instead of the combo box open a recordset with the query in (1) and loop through the rows returned. For each one use the replace function to insert the [Flight No] into the query in (2) and execute it. Alternatively set the query in (2) as a stored query, a querydef, with a parameter for the [Flight No] and execute it passing the Flight No as its parameter.



--

Joe
  #3 (permalink)  
Old February 18th, 2004, 04:34 PM
Authorized User
 
Join Date: Feb 2004
Location: Edwards AFB, California, USA.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Joe,

I am using Access 2002. I am trying to be an expert in development, but it seems I'm not. This is what I have so far,
Sub sResetFTP()
    Dim rstFTPNumbers As Recordset
    Dim defFTPNumber As QueryDef
    Dim sSql As String
    Dim strcut As String
    Dim strFlight As String
    strcut = "appFTPD-TableB"
    Dim db As Object
    Set db = CurrentDb()
    Set rstFTPNumbers = db.OpenRecordset("SELECT DISTINCT tblFTP.[Flight No]From tblFTP ORDER BY tblFTP.[Flight No];")
    Set defFTPNumber = db.QueryDefs(strcut)
    rstFTPNumbers.MoveFirst
    Do Until rstFTPNumbers.EOF
    strFlight = rstFTPNumbers![Flight No]
    'DoCmd.OpenQuery "appFTPD-TableB"
    sSql = "INSERT INTO tblB ( RecordNo, [Flight No], [Run No], [Emitter/WRPR No], [TIS No], Site, [Date Flown], ***SELECT tblFTP.RecordNo, tblFTP.[Flight No], tblFTP.[Run No], tblFTP.[Emitter/WRPR No]tblFTP.[TIS No], tblFTP.Site, tblFTP.[Date Flown], *** where (((rstFTPNumbers![Flight No]) And (tblFTP.[Run No]) Like D*)ORDER BY tblFTP.RecordNo, tblFTP.[Flight No], tblFTP.[Run No], tblFTP.[Emitter/WRPR No], tblFTP.[TIS No];"
    Beep
    rstFTPNumbers.MoveNext
    Loop
    End Sub
For sure, I am looping thru my recordset but the action is not happening.



  #4 (permalink)  
Old February 18th, 2004, 04:52 PM
Authorized User
 
Join Date: Feb 2004
Location: Edwards AFB, California, USA.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

One more thing Joe,

I need to append these records to [u]tblB</u> via recordset by flight number; with the D* RunNo first for each group of flights then the A*
Then the next group. I am sorting test results for each flight....

Sub sResetFTP()
    Dim rstFTPNumbers As Recordset
    Dim defFTPNumber As QueryDef
    Dim sSql As String
    Dim strcut As String
    Dim strFlight As String
    strcut = "appFTPD-TableB"

(your suggestion)
Where do I use it or how

    '"SELECT tblFTP.RecordNo, tblFTP.[Flight No], tblFTP.[Run No], tblFTP.[Emitter/WRPR No]tblFTP.[TIS No], tblFTP.Site, tblFTP.[Date Flown], From tblFTP where (((rstFTPNumbers![Flight No]=?) And (tblFTP.[Run No]) Like D*)OR (tblFTP.[Run No]) Like A*)ORDER BY tblFTP.RecordNo, tblFTP.[Flight No], tblFTP.[Run No], tblFTP.[Emitter/WRPR No], tblFTP.[TIS No];" Dim db As Object

    Set db = CurrentDb()
    Set rstFTPNumbers = db.OpenRecordset("SELECT DISTINCT tblFTP.[Flight No]From tblFTP ORDER BY tblFTP.[Flight No];")
    Set defFTPNumber = db.QueryDefs(strcut)
    rstFTPNumbers.MoveFirst
    Do Until rstFTPNumbers.EOF
    strFlight = rstFTPNumbers![Flight No]

    'DoCmd.OpenQuery "appFTPD-TableB"

(My append code)
  'sSql = "INSERT INTO tblB ( RecordNo, [Flight No], [Run No], [Emitter/WRPR No], [TIS No], Site, [Date Flown], SELECT tblFTP.RecordNo, tblFTP.[Flight No], tblFTP.[Run No], tblFTP.[Emitter/WRPR No]tblFTP.[TIS No], tblFTP.Site, tblFTP.[Date Flown], From tblFTP where (((rstFTPNumbers![Flight No]) And (tblFTP.[Run No]) Like D*)ORDER BY tblFTP.RecordNo, tblFTP.[Flight No], tblFTP.[Run No], tblFTP.[Emitter/WRPR No], tblFTP.[TIS No];"
    Beep
    rstFTPNumbers.MoveNext
    Loop

    End Sub



Similar Threads
Thread Thread Starter Forum Replies Last Post
Recordset empty when opening querydef DeAnzaJig Access VBA 3 November 14th, 2005 09:31 AM
ADODB.Recordset (0x800A0CB3)Current Recordset does tks_muthu Classic ASP Databases 0 June 16th, 2005 07:22 AM
querydef emady Access 1 June 2nd, 2005 10:58 AM
Convert ADO recordset to DAO recordset andrew_taft Access 1 May 5th, 2004 02:31 PM
How do I get the QueryDef part to work bhunter Access 0 February 19th, 2004 01:27 PM





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