Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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--
Reply With Quote
  #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
Reply With Quote
  #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.



Reply With Quote
  #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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 08:22 AM.


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