Append Query problem - 74486

I have created an append query in vba code which is inside a For...Next loop.
I create an SQL statement to find matching related records in one table, and
then insert these matching records into a temporary table. I repeat for each
value of unique primary key that is provided (in an array).

The issue I have is that the append query only works for the first matching
record, not the subsequent records. I have verified that correct SQL
statements are generated during each iteration of the loop, but for some
reason no inserts occur after the first one. The temp table is cleared at the
beginning of the subroutine.

Any input would be helpful, I thank you very much for any assistance.

Here is my code:
If boolNoRecords = False Then   'the "No Records Found" flag is not set.
        For x = 0 To UBound(lngArrayTestID)
            If lngArrayTestID(x) > 0 Then     'do not allow TestID = 0
                'lngArrayTestID() contains non-duplicated testID values
(primary key).
                strTempSQL1 = "INSERT INTO " & strResultsTable

                strTempSQL1 = strTempSQL1 & " SELECT * FROM "
                strTempSQL1 = strTempSQL1 & strSourceQuery2 & " WHERE "
                strTempSQL1 = strTempSQL1 & "TestID = " & lngArrayTestID(x)
                 DoCmd.SetWarnings False     'turn off warnings
                   DoCmd.RunSQL strTempSQL1   'execute "INSERT INTO" query
                 DoCmd.SetWarnings True      'turn on warnings

            End If
            strTempSQL1 = ""    'make sure it is cleared before starting
        Next x      'repeat the process for all TestID values in
    End If      'end the If boolNoRecords = False clause

Message posted via

2/12/2008 9:11:31 PM
access.formscoding 7493 articles. 0 followers. Follow

0 Replies

Similar Articles

[PageSpeed] 52


Similar Artilces:

Problem replicating public folders from 2000 -> 2003 after migration
Hi all We have almost finished migration of our existing Small Business Server 2000 - Exchange Server 2000 (Build 6249.4 SP3) to a new server running Windows Server 2003 (v5.2.3790) and Microsoft Exchange 2003 Enterprise (Build 7226.6 SP1). Migration of mail has completed successfully but we are having a major issue replicating public folders and system information (eg. Offline address book) to the new server. Replication is set up as per microsoft KB article Q307917 and now (several hours later) we are seeing the following under the replication status: Server Name Replication Sta...

Hello, I've seen some posts regarding a similar problem to what I'm having but cant find the resolutution! I have a database with tableA which contains basic inventory info. I have a field in tableA named location. There is a second table (tableB) which has a list of all the locations our company delivers to. I've been warned to stay away from lookup fields, so I'm using combo boxes on the form to look up the location and then populate the location field in tableA when the value is selected. Here is the issue (and again, I've found some posts similar): Running a...

When I close down Outlook it looks like it closes the normal way but when I go to shut down my computer I ALWAYS get an error box that pops up and says: end program - OUTLOOK.EXE Then I click on end program and it says it can't end program and the computer shuts down. ?????????? Please HELP!!! Please post your Outlook version when posting to this group. There are currently 5 versions and 7 modes of Outlook so making us guess is not nice. :) Common reasons why Outlook does not close cleanly (depends on versions also) 1. PDA docked and set to synch continuously 2. Fax softwar...

IS & IMC Problem.
Hi all, We are getting following error in exchange 5.5. ~~~~~~~~~~~~~~~ Event Type: Error Event Source: MSExchangeIMC Event Category: Internal Processing Event ID: 4116 Date: 4/1/2004 Time: 9:35:12 AM User: N/A Computer: HYDMAIL2 Description: An error was returned from the messaging software the Internet Mail Service uses to process messages on the Microsoft Exchange Server. It is possible that the piece of mail being processed at the time will be returned to the sender as a failed delivery instead of being delivered. The message will be moved to the "BAD" folder, if possible, a...

folder browsing I want to use a system dialog to return the path to a folder the user selects. I need the ability set the default folder and create a folder too. It would also be helpful from a less clicks stand point if the default folder could be expanded so the user doesn't have to. I found FolderBrowserDialog which does what I want except for a few things. It does not expand the default folder which is a problem because if the user creates a folder, a new folder is created named "New Folder" beneath the default folder but since the default folder is collapsed, the ...

Query 08-24-07
I have two tables. One contains contigent employees the other full-time employees. I would like to create a query that combines these two tables, so the contigent and full-time employees are all on one report. How can I accomplish this? Thanks for your help Is there a business reason these two tables are kept separated? For instance, the data elements you keep about "contingent" employees differs from the data you keep about "full-time" employees? If you have two identical tables (same structure), one for each "type" of employee, your data is structured ...

Query to Access : does'nt see tables
Office 2000 SP3 FR From Excel / MS Query, I want to read data in Access tables. I choose the mdb file and so MS Query displays this error message (translated) : "This data source does'nt have any visible table". Original in French : "Cette source de donn´┐Żes ne contient aucune table visible." I transfert my data to a new mdb file : it work's few times (!), then the error message appears again. How to resolve this problem ? Jacques. Gotcha ! MS Query does'nt accept a file name with 2 dot inside ! Example : MyFile.mdb --> Ok My.File.mdb --> "...

Rule Problem
I'm using Outlook 2003. I've created a rule that will check for a specific email account first then check the Subject and then move it to a specific folder. Example: if it's from then check if the subject is Blah then move it to folder Blah. The folder that this rule is moving the messages from the inbox to is a folder within another folder. It is creating duplicate messages that end up in the Parent folder & the Sub folder. I don't have move a copy selected in the rule there for it really shouldn't be creating a duplicate message. How do i ge...

request to solve my problem #3
hello friends this is vijay,i got a problem while doing my project based on vc++ (i.e)"How to implement Fit Window feature in VC++6.0 in an MDI Application" if any of you got any idea about that please do reply me with the proceedure.i will be waiting for your reply bye with regards vijayprasadreddy ...

I have changed my default mail program from Microsoft Outlook to Outlook Express but emails are still going into Microsoft. How do I make them go into Outlook Express? Any help much appreciated.... On Aug 19, 12:35=A0pm, "Ron Head" <> wrote: > I have changed my default mail program from Microsoft Outlook to Outlook > Express but emails are still going into Microsoft. =A0How do I make them = go > into Outlook Express? =A0Any help much appreciated.... You would have to set the account up in Outlook Express as well. Changing the default email ...

com session problem
i keep getting problems when trying to excute a com session object i have followed the customization guide word for word but still get the same problem sya it can not excute it at the POS. i have called it by the dll name then the class name in manager for example dllname.classname and it does not work. i'm only using the example code given in the guide i'm doing this in VB6. Have you registered the DLL using regsvr32? Rob "Tom Platinum systems ltd" <> wrote in message news:BCD80C2D-8724-402F-9AF5-3123B7BF08B0@micro...

Oulook 2002 Rules Wizard Problems
I am running Office XP and use Oulook 2002 (SP3) with an Exchange server. I am a remote user and was initially setup to have new messags delivered to my local PST file. After people in the office complained about not being able to see my calendar, I changed the delivery of new messages to the standard server based mailbox with offline folders. Additionaly I have a server based rule to rediret all internal email to my PDA. Now everytime I launch Outlook I recieve the following message: "You are creating a rule that will be incompatible with versions of Outlook earlier than Outlo...

UNION query taking 5 minutes for approx 2000 records
I am dealing with data from two hospitals, each with their version of the database. I am trying to group records of admissions and discharges from each hospital by patient (URN), date of admission (DOA), date of discharge (DISCH) and time of admission (TOA) where at least one patient record represents a transfer from hosp A (KEMH) to hosp B (PMH) ie where demogr_urPM.ADMIT_SOURCE Like "KEMH*". The individual SELECT queries run quickly enough but the UNION query I am using takes approx 5 min to return about 2000 records. The source tables, demogr_ur (for hosp A) and demogr...

Autofilter problem #2
I have this quite annoying problem with autofilter because I work with large lists very offten. I read somewhere that autofilter is limited to 1000 rows. Is there a solution for this? Using custom filter doesn't help too much. Thanks. Hi see -- Regards Frank Kabel Frankfurt, Germany dustystorm wrote: > I have this quite annoying problem with autofilter because I work > with large lists very offten. I read somewhere that autofilter is > limited to 1000 rows. Is there a solution for this? Using custom > filter doesn'...

Excel Font Problem
Every time I enter a number <1 into my Excel worksheet, it prints in white which is too hard to see. I have the font set at black. How do I get ALL the numbers to print in black One thing to possibly check: 1. Tools > Options > Color and try Reset. HTH Select those cells and see what Format>Conditional Formattting reveals. Sounds like you have a setting that whites the font on values <1 Gord Dibben Excel MVP On Thu, 30 Jun 2005 08:09:04 -0700, Heather S <Heather> wrote: >Every time I enter a number <1 into my Excel worksheet, ...

Links Problem
I have a spreadsheet which contains links to other spreadsheets on m PC. When I send the spreadsheet to a friend and they open it and clic "Don't Update" they get errors where the linked formulas are. I thought that as long as you didn't try to update the links th spreadsheet should be OK and look the same as when it left m computer. HELP !!!!!!!!!!! :mad: :mad: :mad: :mad: :mad: :mad -- Hibbste ----------------------------------------------------------------------- Hibbster's Profile: View this thre...

More info on my Microsoft Query problem.
I am using Microsoft Query to extract data from one Excel file to another. The query is set to return all the information for one specic order number. It does successfully return all the information except for the information listed under one of the columns. It lists and see the column but does not return the actual informmation. More specifally, it is the item price. Item price is written at the top of the column but the actual pricing is not extracted. And I went into Microsoft Query help and followed the instructions. The file is closed, and the data is formatted the same way. A...

RWW, VPN, Exchange connection problems
Running SBS2003 on an HP server. 3 PCs used by onsite staff. 4th PC used for remote access via RWW. Having intermittent connectivity issues. I sign in remotely using an XP workstation w/RWW. I have another PC that I use to access Exchange, and occasionally connect with VPN to access files on the server at the office. I keep getting dropped on RWW with the error "VBScript Remote Desktop Disconnected. An internal error has occurred". When that happens, I also notice that my Oulook client on the other PC shows an error that the connection to Microsoft Exchange has ...

Outlook 2007 help problem
I just installed Outlook 2007 only (not Office 2007) and when I click the help button this is what I get in the Outlook Help box: <?xml version="1.0" ?> <?xml-stylesheet type='text/xsl' href='File://C:\Documents and Settings\Marg & Bob\Local Settings\Temporary Internet Files\MsOfficeHelp12\MOH6E.tmp\toc.xsl'?> - <assistance namespace="OUTLOOK" UseOnlineContent="1"> <title>Outlook</title> There is more to it but I only copied and pasted this portion. It is obviously not the normal help box. Any ...

Append Query 09-13-07
I have and excel spreadsheet I import into a table every week. I wrote this append query to append this table to a datasheet in access. It works except I want to only add the new rows where there is a new router. The impRouter is the key field. It should be in SQL NOT EQUALS TO??? query that appends all the records INSERT INTO _HSIImport ( impArea, impHeadend, impRouter, impDate ) SELECT [Port History9_1_2007].Area, [Port History9_1_2007].Headend, [Port History9_1_2007].[Element Name], ([#date#]) AS Expr1 FROM [Port History9_1_2007]; modified query to add only the new router records...

problem related to the CCombobox
I have one Combobox which is editable ............ when i select the string from the dropdown list we get the string easily but when i enter the string in the editbox of Combo box .....its not selected how i can select the string which i enter in the combobox at run time also plz tell me how i updated my Combobox's Dropdown list with written string thanks & regards preeti jain Is the string case-sensitive? You can use FindString or FindStringExact (depending on what you are trying to do) to locate a string, case-insensitive. So if it returns a non-negative value, the string ex...

there is a problem with the selected printer
When I try to print emails from Outlook 2007 I get the error message below: "There is a problem with the selected printer. You might need to reinstall this printer. Try again, or use a different printer." I used Outlook 2007 at my office and we are on a follow-me printing network. I don't have issues printing any other documents from Office 2007, it's just the emails that have this problem. Does anyone have a resolution to this issue? ...

Strange problem ... items in Inbox disappearing ...
Hi, I have a NT4 domain with Exchange 5.5 Recently I found out emails in my users' inboxes are disappearing. I have been carrying out some testing myself today. 1) 25/09 9:00am : send some external email to my mailbox, eg. hotmail, gmail ... etc 2) Got all of the external testing emails in my inbox 3) 25/09 10:30pm : got around 20 emails in my inbox (some read, some unread) 4) 26/09 00:10am : all emails disappear and my inbox become empty 5) go into Tools -> Deleted items recovery and I found all of the missing emails with a deleted timestamp at 25/09 11:54pm I am sure I didn...

email attachment(s) from query results
Through a series of forms and queries, my users select a list of pdf files that should be sent via email. This list might contain anywhere from 1 to 20 pdf's, and the actual files and number of files vary with each use. At this point, this list is emailed to someone who manually creates an email with the appropriate attachments. This is not a terribly efficient workflow. Already existing in the database, I have a hyperlink field containing the full path to each of the pdf files. What I'm looking for is some way for access to loop through the final query results, retrieve the...

Problem with starting Word 2007
I have a recurring problem with Word 2007. Every few days (I haven't figured out a pattern yet) Word 2007 will not start. All that shows up is the Word 2007 splash screen. In order to get past this I have to remove the normal.dotm file and reboot. Then I can bring up Word 2007. This problem started a few weeks ago and I have not installed anything new on the computer. I'm runnin Windows7 and MSOffice 2007. I've removed a few add-ons in order to pinpoint the issue but so far no luck. Questions: Has anyone seen this problem before? Does anyone have suggestions of wha...