Append Query problem

I have an Append Quey that works fine when you right click on the
query ("ReaderIndCancelProforma_Append") and choose open

INSERT INTO Reader_DistrHistory ( DistrId, [Reader Id], [Date on
mailing list], TypeId, ReasonId, Reason, [Date off mailing list] )
SELECT Reader_DistrCurrent.DistrId, Reader_DistrCurrent.[Reader Id],
Reader_DistrCurrent.[Date on mailing list], 6 AS Type, 15 AS Reas,
[Forms]![Reader_CancelProforma]![Remarks] AS Remark, Now() AS off
FROM Reader_DistrCurrent
WHERE (((Reader_DistrCurrent.[Reader Id])=[Forms]![Reader_DB]![Reader
Id]));

The problem I have is when I run the query from a command button on a
form.  It only appends the [Reader Id]

Private Sub Command8_Click()
    On Error GoTo Command8_Click_Err

    DoCmd.SetWarnings False

            DoCmd.OpenQuery "ReaderIndCancelProforma_Append",
acViewNormal, acEdit 'this doesnt work

            DoCmd.OpenQuery
"ReaderIndCancelProformaHistChange_Append", acViewNormal, acEdit 'this
works

            DoCmd.OpenQuery "ReaderIndProFormaCancel_Delete",
acViewNormal, acEdit ' this works

            DoCmd.OpenQuery "ReaderIndProformaCancel_Update",
acViewNormal, acEdit 'this works


    DoCmd.SetWarnings True

Command8_Click_Exit:
    Exit Sub

Command8_Click_Err:
    MsgBox Error$
    Resume Command8_Click_Exit

End Sub

Why should this suddenly happen.  All the other queries work fine

I have other forms that run similar code and they all work fine.

Would appreciate some help on what the problem could be

Rocky Swartz.
0
Rocky
1/29/2010 6:20:51 AM
access.forms 6864 articles. 2 followers. Follow

2 Replies
882 Views

Similar Articles

[PageSpeed] 56

When you say it doesn't work - what actually happens?
You can put in some debugging code to help you find the error.
-----------------
 Private Sub Command8_Click()
    'On Error GoTo Command8_Click_Err

    'DoCmd.SetWarnings False

    Debug.Print Forms!Reader_DB!ReaderId
    Debug.Print Forms!Reader_CancelProforma!Remarks

            DoCmd.OpenQuery "ReaderIndCancelProforma_Append",
 acViewNormal, acEdit 'this doesnt work

End Sub
----------------

To debug, put those lines of code in your button click routine, comment out 
the error handler and the set warnings statements.
Click the button to run the query.
Open the immediate window Ctl+G to see what access got for ReaderID and 
Remarks


If the form Reader_DB is not open when you click the button, the query won't 
run because it won't be able to get the value for ReaaderId.
Same thing for the form Reader_CancelProforma
You can also open both forms Reader_DB and Reader_CancelProforma and open a 
copy of that query saved as a select query to see what data it picks up from 
the 2 forms.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



"Rocky" <consulting@randalstewart.com> wrote in message 
news:54c7c8d7-e812-4860-b701-70864f8e5201@z41g2000yqz.googlegroups.com...
>I have an Append Quey that works fine when you right click on the
> query ("ReaderIndCancelProforma_Append") and choose open
>
> INSERT INTO Reader_DistrHistory ( DistrId, [Reader Id], [Date on
> mailing list], TypeId, ReasonId, Reason, [Date off mailing list] )
> SELECT Reader_DistrCurrent.DistrId, Reader_DistrCurrent.[Reader Id],
> Reader_DistrCurrent.[Date on mailing list], 6 AS Type, 15 AS Reas,
> [Forms]![Reader_CancelProforma]![Remarks] AS Remark, Now() AS off
> FROM Reader_DistrCurrent
> WHERE (((Reader_DistrCurrent.[Reader Id])=[Forms]![Reader_DB]![Reader
> Id]));
>
> The problem I have is when I run the query from a command button on a
> form.  It only appends the [Reader Id]
>
> Private Sub Command8_Click()
>    On Error GoTo Command8_Click_Err
>
>    DoCmd.SetWarnings False
>
>            DoCmd.OpenQuery "ReaderIndCancelProforma_Append",
> acViewNormal, acEdit 'this doesnt work
>
>            DoCmd.OpenQuery
> "ReaderIndCancelProformaHistChange_Append", acViewNormal, acEdit 'this
> works
>
>            DoCmd.OpenQuery "ReaderIndProFormaCancel_Delete",
> acViewNormal, acEdit ' this works
>
>            DoCmd.OpenQuery "ReaderIndProformaCancel_Update",
> acViewNormal, acEdit 'this works
>
>
>    DoCmd.SetWarnings True
>
> Command8_Click_Exit:
>    Exit Sub
>
> Command8_Click_Err:
>    MsgBox Error$
>    Resume Command8_Click_Exit
>
> End Sub
>
> Why should this suddenly happen.  All the other queries work fine
>
> I have other forms that run similar code and they all work fine.
>
> Would appreciate some help on what the problem could be
>
> Rocky Swartz. 


0
Jeanette
1/29/2010 7:01:37 AM
Try   DoCmd.RunSQL  instead of OpenQuery


DoCmd.OpenQuery
"Rocky" <consulting@randalstewart.com> wrote in message 
news:54c7c8d7-e812-4860-b701-70864f8e5201@z41g2000yqz.googlegroups.com...
>I have an Append Quey that works fine when you right click on the
> query ("ReaderIndCancelProforma_Append") and choose open
>
> INSERT INTO Reader_DistrHistory ( DistrId, [Reader Id], [Date on
> mailing list], TypeId, ReasonId, Reason, [Date off mailing list] )
> SELECT Reader_DistrCurrent.DistrId, Reader_DistrCurrent.[Reader Id],
> Reader_DistrCurrent.[Date on mailing list], 6 AS Type, 15 AS Reas,
> [Forms]![Reader_CancelProforma]![Remarks] AS Remark, Now() AS off
> FROM Reader_DistrCurrent
> WHERE (((Reader_DistrCurrent.[Reader Id])=[Forms]![Reader_DB]![Reader
> Id]));
>
> The problem I have is when I run the query from a command button on a
> form.  It only appends the [Reader Id]
>
> Private Sub Command8_Click()
>    On Error GoTo Command8_Click_Err
>
>    DoCmd.SetWarnings False
>
>            DoCmd.OpenQuery "ReaderIndCancelProforma_Append",
> acViewNormal, acEdit 'this doesnt work
>
>            DoCmd.OpenQuery
> "ReaderIndCancelProformaHistChange_Append", acViewNormal, acEdit 'this
> works
>
>            DoCmd.OpenQuery "ReaderIndProFormaCancel_Delete",
> acViewNormal, acEdit ' this works
>
>            DoCmd.OpenQuery "ReaderIndProformaCancel_Update",
> acViewNormal, acEdit 'this works
>
>
>    DoCmd.SetWarnings True
>
> Command8_Click_Exit:
>    Exit Sub
>
> Command8_Click_Err:
>    MsgBox Error$
>    Resume Command8_Click_Exit
>
> End Sub
>
> Why should this suddenly happen.  All the other queries work fine
>
> I have other forms that run similar code and they all work fine.
>
> Would appreciate some help on what the problem could be
>
> Rocky Swartz. 


0
Chegu
1/29/2010 5:35:33 PM
Reply:

Similar Artilces:

Problems saving a worksheet with Links
Does anyone know how I can resolve this issue ... I have a directory which contains 129 worksheets which have links to external data (in a Master Spreadsheet) -- I need to copy these files into a New Directory, but kee the Master Spreadsheet (which they are linked to) in the original location. If I do a simple Cut & Past, the Reference Link to the Master Spreadsheet gets moved to the New Directory (where the file does not exist), but if I open the worksheet (in the original directory/location) and Save As to the New Directory, the worksheet saved in the New Directory maintains its link t...

RPC over HTTP problem #3
Hi, All! My network configuration: DC1, DC2 and MX (MS Exchange 2003, sp1). All of them Windows Server 2003. What was done: In the registry on dc1 and dc2 was created a new key: "NSPI Interface protocol sequences" with value: ncacn_http:6004. MX was promoted to be a GC. Installed RPC over HTTP windows component. MX was changed to be RPC-HTTP back-end server. On the MX Default Web Site was installed cerificate from the local authority running on DC2. On the RPC virtual directory anonymous access and integrated windows authentication were disabled. In the registry of MX the key HK...

how to query my web site from VBA and return a value to VBA
Hello All, From VBA I would like send a value to my web site, and have it return a value. I've learned how to use FollowHyperlink to send a value to an ASP script, but how can the ASP script send a value back to VBA?? Thanks, Brian Austin, TX You can use xmlhttp to make a request to your web page: '********************************************************* Sub Tester() MsgBox WebResponse("http://www.mydomain.com/myactualpage.asp? info=3Dblah") End Sub Private Function WebResponse(sURL As String) As String Dim XmlHttpRequest As Object Se...

Send/Receive Problem
I am using Outlook 2002 on an XP platform. I cannot get Outlook to check for Email at regular intervals. I have the my Outlook set to Send and Receive all my accounts every 10 minutes but nothing happens. The only way I can receive Emails is by manually using the Send/Recv button or pressing F9. Can anyone offer any help. In case it is relevant I am using Norton Internet Security 2003. PWS Not sure it it Yor problem, but Outlook has some problems with Noroton Antivirus runing and chekking e-mails. As far as I know, Outlook may stop recieving e-mails from POP3 servers due to very le...

Font problem with Office 2004 for mac
I have Tiger, whenever I try to Launch any Office program, as the the menu loads to 'optimizing font menu performance' It pops up with 'The font " " has been corrupted and should be removed'. It does this with MANY fonts, many of which I don't even have in my font folder. It does this every time during the program start, and most times even if I go through clicking ok 40-60 times it will sometimes freeze up anyway. Anyway to fix, get around this problem? Please email me at madefornothing@yahoo.com. Hi, this problem affects quite a few users, and there'...

Excel problem #3
I am attaching an excel file where i have a problem In the file are 2 sheets, Main & second I want to get data from second sheet to the main sheet by a formula by which the amount in the total column will be posted in the second sheet falling under various dates. I have done for 6 sept 2003 by way of example I do not know any formula by which i can do this automatically Please help me Attachment filename: example.xls Download attachment: http://www.excelforum.com/attachment.php?postid=444742 --- Message posted from http://www.ExcelForum.com/ Hi one way: ...

Sync net folder problem
I am sharing my calendar to my workmate with net folder. My PC is Win XP and Office 2000 and my workmate's is Win 98 and also Office 2000. I always find that My calender can't be updated from my workmate when I return office after I've taken my notebook for a few days. Can't net folder sync. data offline? Thx. your attention. Ken So Net Folders uses e-mail messages to send updates between computers so naturally you would have to be connected to your e-mail to get any updates. When you leave the office and are not connected you won't get any updates but as soon as ...

chart line style problem
I am making a scatter chart (with lines) in Excel 2007 under Vista. I can select a line style, for example, long dashes. However, if I try to change the axis (change from "automatic" to "fixed" on the horizontal axis), the line on the chart immediately becomes solid again. The legend still shows the proper dashing. I can get the dashing partly back by making the line thinner, but only where the variation is fastest - regions where the derivative is near zero are still solid even for thin lines. I'll appreciate any help! frank I was not able to reproduce this. Can you...

auto forward problems
I setup a 'contact' for 5 existing users in Exchange 5.5 Administrator. I give the contacts the desired SMTP address where they want their mail forwarded to. I set the corresponding 'contact' as the 'alternate recipient' for each of the 5 as detailed in Q255697. 2 of 5 work, the other 3 do not. When sending to each of the 5, 3 return undeliverable stating "A configuration error in the e-mail system caused the message to bounce between two servers or to be forwarded between two recipients." Any ideas? -adam Adam SK wrote: > I setup a 'con...

Exchange 2003 with SP2 problems...
I installed the SP2 for exchange 2003 server tonight, and I'm getting some problems. I added the registry key to increase the DB size to 30GB per the instructions, but I don't get a confirmation in eventid 1216 as it says I should. In fact, the message in eventid 1216 looks mised up. See insert: The Exchange store '16384' is limited to First Storage Group\Mailbox Store (ATL-SBS) GB. The current physical size of this database (the ..edb file and the .stm file) is %3 GB. If the physical size of this database minus its logical free space exceeds the limit of First Storage Gro...

Active Directory/Exchange problem
All, Before I joined my current employer the admin here upgraded from Exchange 5.5 to Exchange 2000(Box A) and then added another Exchange 2000 box to the organisation(Box B)and migrated the data in Box A to Box B. Box B is now the working exchange server and Box A is no longer used. The problem is that if I actually shut down Box A I can add a new user to Active Directory but I am unable to modify a users email/smtp details. All mail can still be transferred with no problems which would lead me to believe that Exchange is Ok but there is some sort of Active Directory link between the two bo...

small problem
hi every body; i wrote a program that it has error ;plz help me :( using System; namespace ConsoleApplication45 { class Program { static void Main(string[] args) { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("***********"); for (int i = 0; i < 1008; i++) { Console.BackgroundColor = ConsoleColor.DarkCyan; Console.Write(" "); } move(); // ***********error is for here***********************************************...

save as version 2003 problem
I'm working in vba in Access to create and save an Excel file. All's good except that one of the workstations this is runnign on is using Office 2007. I'm developing in 2003 and all the other workstatiosn they have are using 2003. It's very important that the files be saved in 2003 format. When I do this, it runs fine and saves as 97/95 objExcelBk.SaveAs sTempPath & sExcelFileName, xlExcel9795 ','56 = xl 2000/2003 I read online in a forum post that "56" is the correct code for saving as 2003 but that's when the code is written in 20...

form and query problem. please help.
All tables are linked with weak entities. However, when i enter data on the form I can't get it to let me enter more than one partipicant without access generating a new invoice id. however i need one invoice to many participants. It wont work and i have no idea what to do at this point. in addition the workshop will not let me add workshop to invoice. this is a small mdb and i'd like to email it to anyone who can assist me with the relationships as I think this is the problem but I don't know what to do. please help me. INVOICE invoiceNO - autonumber invoice prices WORKSHOP wo...

Pulling counts out of query results
I have a query that has one field Type which is set to Count The query results are used in a report. The report has the fields in the detail section as TYPE and CountOfType (1 line only in the detail section) The report looks like this when displayed AS 28 AV 17 OR 5 I need to be able to get the individual AS No (28) and add it to another number elswhere in the report. How can I do this? I have tried using a textbox with an if function (if [type]="AS",CountOfType,0 but that did not work. Any help appreciated. Ray I think you will need to do it in the query. But try addi...

Hidden log on problem
On our XP Home laptop I have 2 users on the welcome screen, while my admin account uses (ctrl alt del) x2. All is fine until it goes into standby when I am in admin. Then on wake up the welcome screen comes back, but (ctrl alt del) x2 does nothing. I can't see a way to get back into my account without rebooting. Is there another way (opening one of the other accounts is as slow as rebooting)? Cheers, S On Mar 30, 9:26=A0am, "spamlet" <spam.mores...@invalid.invalid> wrote: > On our XP Home laptop I have 2 users on the welcome screen, while my a...

Formula Problem #11
I have an excel sheet that has almost 4000 data rows. I need to compare the old sheet to the new sheet and if the part number is equal, I need it to show me the discount from the old sheet in a column in the new sheet. Here is the formula I came up with: =LOOKUP(A4,old!A4:A4000,old!H4:H4000) This compares the A column in the new sheet with the A column in the old sheet and then will report the discount from the H column into the column the formula is written. If I hand type the formula in ever cell changing the row number for the look up cell it works fine. However, when I try to dr...

Problem with secondary home pages GPO
I am trying to set and LOCK the users homepage and secondary home page, So when you launch IE it opens 2 tabs, and users can not change them. Seems like there are 3 places in GPO to set the homepage, but only one of them locks them So I have set \windows components\internet explorer\disable changing home page settings to be the home page I want, that works and lock the setting for the 1 home page. Then I also set the "disable changing secondary home page setting" to the 2nd tab I wanted, and this does not work whe I START IE, but it does work if I'm in IE and hit ...

Problems updating Access 2003 Database
We currently use an Access 2003 data base to create and send forms within the organization. We are seeing instances where the new records are not appending and believe it may be the query that is causing the issue. The INSERT query is: INSERT INTO table_headerdata ( wso_num ) SELECT Max([table_headerdata]![wso_num])+1 AS Expr1 FROM table_headerdata WHERE wso_num < 1000000; We would appreciate any help to resolve this issue. On Fri, 4 Jan 2008 16:50:00 -0800, ehess00 <ehess00@discussions.microsoft.com> wrote: >We currently use an Access 2003 data base to create and send for...

Strange query results/Wild characters???
Hello. My query is showing me strange values from whem loading a value from a form. It's very strange because if you have a form named FormA and inside a field named Field1 and if the field has a default value of 2 and if you run it. Then if you'll make a query with any table or query and put this in the column: Test:([forms]![FormA]![Field1]) your result will be 2, tha same as the the Form Field. My problem is that my query instead of showing me the value of 2 is showing another thing very strange, such as wild characters or value that has nothing to do with it. If I use t...

Change the default format of the query design view
When I use the query design view, I have to increase the size of the table window (from which I'm selecting fields) sideways and vertically to see the field names more clearly and that also means moving the criteria grid further down the page to make room. Is there a way to change the default table window size and default grid position so that I dont' have to do this every time? Thanks, Pat Pat When you find it, let the newsgroup know! You are (unfortunately) not the first person to wish there was a setting...<g> Regards Jeff Boyce Microsoft Office/Access MVP "...

Importing Excel named ranges using MS Query
I want to use multiple ranges (named) as the data source for a pivot table using MS Query. When I import the workbook my options are only to select the "tables" (which are my sheets referenced as sheetname$). I don't want to use the entire sheet, just my named ranges in multiple sheets. Thanks, Kathy H Names ranges should appear in the list of tables, unless they're dynamic ranges. But if there's nothing else on the sheet, you can use the sheetname$ tables. KHanna wrote: > I want to use multiple ranges (named) as the data source for a pivot table > usi...

Problem sending message into outlook
I need to send a message into Outlook from a third party program that automatically sets up the subject line and inserts text into the body of the message. I'm using Delphi's ShellExecute command which just sends a message to Windows and lets Windows handle it. However, when I do this, it removes my signature text which I need and just puts the new text in instead. I tried telling the message window to add my signature, but that option is grayed out. Does anyone know how I can get both the new text and the signature in without having to load the signature into my program or ...

Parsing <param> tag in <object> problem
I am now parsing <param> tag in <object> tag by using IHTMLDOMNode, but i found i can't exactly parsing every <param> tag. The sample tags: <OBJECT type="text/sitemap"> <param name="Keyword" value="Add method"> <param name="Name" value="Add Method (VBA Add-In Object Model)"> <param name="Local" value="html/vamthaddinadd.htm"> <param name="Name" value="Add Method (Visual Basic Extensibility)"> <param name="Local" value="html/vbmthadd.h...

Outlook Junk mail filter and rules problem
Hello- I switched to Outlook from Mozilla Thunderbird. I did this because Thunderbird was unstable (at least for me). It did have a nice spam filter though. I was hoping that Outlook's spam filter would be comparable and the app would be stable. So far it's stable, but I am having a serious problem with the filter. I use Outlook 2004 to track several POP accounts. So when I download a message, I have a rule for each account that puts the message in an appropriate folder under the inbox folder. The problem is when I use the Spam filter, this is what happens. 1. Spam arrives to ...