Is there a way to turn off the prompts of a make table query?

I merely want to update the table to be used as an export to Excel using 
TransferSpreadsheet.  Each time it prompts for deleting old records and 
adding new records.  Is there a was to turn off the prompts?
Bottom line - I want to create an Macro to export the output of a query 
to an Excel spreadsheet.
0
BobC
12/30/2009 2:50:29 AM
access.queries 6343 articles. 1 followers. Follow

3 Replies
1398 Views

Similar Articles

[PageSpeed] 25

BobC,

DoCmd.SetWarnings False  'Off
Your TransferSpreadsheet line here
DoCmd.SetWarnings True  'On

-- 
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors 
II

http://www.regina-whipp.com/index_files/TipList.htm

"BobC" <Bob.CallenNoSpamm@cox.net> wrote in message 
news:Rdz_m.17171$eH1.818@newsfe16.iad...
>I merely want to update the table to be used as an export to Excel using 
>TransferSpreadsheet.  Each time it prompts for deleting old records and 
>adding new records.  Is there a was to turn off the prompts?
> Bottom line - I want to create an Macro to export the output of a query to 
> an Excel spreadsheet. 


0
Gina
12/30/2009 3:29:22 AM
BobC wrote:
> I merely want to update the table to be used as an export to Excel
> using TransferSpreadsheet.  Each time it prompts for deleting old
> records and adding new records.  Is there a was to turn off the
> prompts? Bottom line - I want to create an Macro to export the output of a
> query to an Excel spreadsheet.

As Gina hinted, without really saying so, you won't be able to accomplish 
directly this with a Macro. You will have to use VBA code, which can be 
called by a Macro, but I'm not really sure why a macro would be needed. You 
can call VBA code using a button on a form as simply as you can call a 
macro.

Anyways, I do think some error-handling should be added to Gina's suggestion 
so that if an error occurs, you won't be stuck with Warnings turned off 
until the user turns them back on via Tools|Options or restarts Access.

Sub SpreadSheetXferNoWarnings()
On Error Resume Err_Handler
DoCmd.SetWarnings False  'Off

Your commands to update the table
and transfer the spreadsheed here

Exit_Sub:
DoCmd.SetWarnings True  'On
Exit Sub

Err_Handler:
Msgbox Err.Description
GoTo Exit_Sub
End Sub


-- 
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM" 


0
Bob
12/30/2009 12:27:41 PM
Both Bob's...

BobC...  You can actually do what I suggested in a Macro BUT as Bob Barrows' 
suggested you should put in some error code and THAT you can't accomplish 
with a Macro.

Bob Barrows...  Thanks for catching the MACRO part, my eyes skipped right 
over that!

-- 
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors 
II

http://www.regina-whipp.com/index_files/TipList.htm

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message 
news:eH%23FQuUiKHA.2164@TK2MSFTNGP02.phx.gbl...
> BobC wrote:
>> I merely want to update the table to be used as an export to Excel
>> using TransferSpreadsheet.  Each time it prompts for deleting old
>> records and adding new records.  Is there a was to turn off the
>> prompts? Bottom line - I want to create an Macro to export the output of 
>> a
>> query to an Excel spreadsheet.
>
> As Gina hinted, without really saying so, you won't be able to accomplish 
> directly this with a Macro. You will have to use VBA code, which can be 
> called by a Macro, but I'm not really sure why a macro would be needed. 
> You can call VBA code using a button on a form as simply as you can call a 
> macro.
>
> Anyways, I do think some error-handling should be added to Gina's 
> suggestion so that if an error occurs, you won't be stuck with Warnings 
> turned off until the user turns them back on via Tools|Options or restarts 
> Access.
>
> Sub SpreadSheetXferNoWarnings()
> On Error Resume Err_Handler
> DoCmd.SetWarnings False  'Off
>
> Your commands to update the table
> and transfer the spreadsheed here
>
> Exit_Sub:
> DoCmd.SetWarnings True  'On
> Exit Sub
>
> Err_Handler:
> Msgbox Err.Description
> GoTo Exit_Sub
> End Sub
>
>
> -- 
> Microsoft MVP - ASP/ASP.NET - 2004-2007
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
> 


0
Gina
12/30/2009 1:55:36 PM
Reply:

Similar Artilces:

Build Dynamic Query from Form
I am trying to build a dynamic Query from a Form. I keep getting an error that reads ‘Object qryFilter already exists’ I suspect it has something to do with the string of dates being passed to the Query; strDateCondition = "([Trades].[TDATE] Between [Forms]![SearchForm]![cboFrom] And [Forms]![SearchForm]![cboTo])" I am trying to add a means for a user to Query by Customer and Trader AND all records between two dates. This was working fine for Customer and Trader; when I added in the code to filter by dates I started having problems. I know the SQL will be li...

Form for table
Here's the problem: Access 2003, XP Pro- I have three tables that are related by primary keys. I have a table[Table QC] that has the field [EmployeeID], the data type is number--that field is linked to the autonumber primary key of the Employees Table. So far so good. I am trying to make a form that has a combo box that looks to the Employee Table to get me the drop down of the employees (Last Name, First Name). The SQL statement builds out okay, and I get the correct name config., but because the data type is a number it won't accept the data. I looked at the Northwind example and i...

Pivot tables in Excel
Yippee, I am so happy, I finally figured out how to trick excel into not showing the blank cell in a pivot table without limiting my data set! In the Pivot table layout move the field from row to page. Then double click on the field and click on the "blank" to hide it. Move the field back to row and hit finish. It seems like you always have to "trick" MS software to do what you want. ...

make $$$$ in minutes
If you like receiving Free Cash for doing most anything on the net, then you'll love FreeCash4. We'll help you FATTEN your Wallet! Our Program is simple. Just sign up with your e-mail address and we will send you a link to verify your membership. Next, provide us with a little information about yourself. We need to know Who to send the money to. Once you have completed this step, you just earned some money. FreeCash4-Joining: A quick $5.00! Once inside the member’s area you will have many ways of earning some Free Cash. There is FreeCash4 everyone. You will not be disappointed...

Pivot Table formatting #6
When I format my pivot tables I have "preserve formatting" checked under "PivotTable Options", and "Autoformat Table" unchecked. Even so, I either lose portions of my formatting, or different formatting is applied when I refresh. Does anyone know what I am doing incorrectly or have any other suggestions? Thanks, Phil Other things to try -- if they don't work, you could record a macro as you refresh and reformat the pivot table. Then, run that when you want to update. --Instead of selecting the cells to format the numbers, right-click the field but...

Count unique values
Hi, I've some problems to count unique items (Invoice #) in a pivot table. There is the default solution "Add-a-new-calculated-column" as mentioned on http://contextures.com/xlPivot07.html#Unique, but in my case, it doesn't works. My problem is, that these values aren't in a Excel worksheet; it's a external data source - a SQL-Select via DAO/ADO. Because of that, I don't have the possibility to add a new column. Another reason is, that the pivot table should always be dynamic: Group over this field, group over another field - and always show the number of unique...

From temporary table to master table and then join this in junction table
Hello, I have imported two spreadsheets from excel to two temporary tables called "import_tbl_contract" and "import_tbl_products". I do this on daily basis and import many spreadsheets. I end up with these data: [import_tbl_contract] contract_number contract_title start_date end_date [import_tbl_products] product_number product_text price I now append these data to the following two tables: [tbl_contract] contract_id (PK) contract_title start_date end_date e.g. 1; Syringes and needles; 01-01-2010; 31-12-2010 [tbl_products] product_id ...

how to turn off automatic format in Excel?
Hi Excel automatically change the first character in a cell to be uppercase. I just want lowercase. How can I turn off this function? Thanks Ngoc Hi Go to Tools / Autocorrect and uncheck Capitalize first letter of sentence. Andy. "ngoc" <linh@chello.no> wrote in message news:BOKNb.271$O41.819@amstwist00... > Hi > Excel automatically change the first character in a cell to be > uppercase. I just want lowercase. How can I turn off this function? > Thanks > Ngoc > ...

please help with this query
Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...

Can I set my custom views on Pivot table
I want to get Pivot table data on my invoice, is it possible to set custom views so that I can get Pivot table results on invoice format, I've tried views that offer excel but it doesn't offer custom views please help Thanks ...

List Running Horizontally-Pivot Table Possible?
I just had a customer ask this one and I'm not so sure about it. 1) The customer has setup a list of information that is running horizontally instead of the typical list running vertically. 2) I just taught him how to do pivot tables and he wants to generate a pivot table with his "horizontal" list. 3) I know he can copy and paste special and use the Transpose feature to change the list from horizontally oriented to vertically oriented. 4) the question is: can he leave the table in its original orientation and still generate Pivot tables? I can't seem to make it work ...

Date Format turn to Year
Hi, I tried to convert the date to YEAR and then the year plus 25 Years later. =Year(A1) I'm getting the result 1900 instead of 1965. I tried to add 25 years later to 1990 from 1965. Your help would be much apprecated. Thanks What's in A1? Are you sure it's a real date? "learning_codes@hotmail.com" wrote: > > Hi, > > I tried to convert the date to YEAR and then the year plus 25 Years > later. > > =Year(A1) I'm getting the result 1900 instead of 1965. > > I tried to add 25 years later to 1990 from 1965. > > Your ...

Pivot table and organizing data
This one is really making me scratch my head. Here is the story. I have a list of information which I am pulling in via a query from SQL. Data is good and it correctly comes into Excel (03 or 07). I have five columns with data: Date, Time, AccountID , Status. First two are self-explanatory; third is a 3-letter ID, forth is a status (pass/fail). Ok, now that you have an idea, here is what I need: 1. List the account IDs as a column 2. List the dates as rows 3. Place the alert into the location that corresponds to the appropriate data and account I know this 'sounds' like a strai...

Update Query
Access 2003 XP SP2 I am having a problem with an update query. Table is in a one-to-one relationship, referentail integrity and cascading data are checked. (The fileds I want to update are not in both tables) Table name= payForward Has 15 fields ie: ID, Name, MemID, Oct , Nov, Dec, etc Oct-Sep fields are yes/no type I want to "select" a field (Oct-Sep) via a query parameter and repalce "yes" with "no". Here is my query: UPDATE payForward SET [Enter month]=No The messages I get is 'operation must use an updateable query' Wh...

Crosstab Query 04-06-07
I have a crosstab query that shows the products i sale with the number i have sold for each day. instead of showing the sales for each day i would like to show sales dor each months. How do i show it my month Thanks In query design view, enter this into a fresh column in the Field row: TheYear: Year([SaleDate]) Replace SaleDate with your actual field name. In the next column: TheMonth: Month([SaleDate]) You can now group on these fields instead of on each date. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html ...

Is there a way to convert OE 03 form templates?
I have a user that has upgraded from outlook 2003 to outlook 2007 and has a form template from 2003 that is in a FDM extension. Outlook 2007 is unable to read this form. Is there a way to convert this form, or will the user just have to create a new form with their new version of outlook? Any information appreciated. Thanks, -AllenK What happens when you try to install the form? Any error message? -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.ms...

Making words in a list a value!!
I am trying to create a list that will generate a value in another column once that word or phrase is chosen. Is this possible and how. Exp. In the drop down list I would chose Product, then in the price column the price automatically appears. brco1, Much better than I could ever explain the process. Here's exactly wha you need. http://www.contextures.com/xlFunctions02.html HT -- Case ----------------------------------------------------------------------- Casey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=454 View this thread: http://www.excelforu...

prompting to save workbook every time (even when no changes made)
i have 3 workbooks all very similar and every single time i open any o them, even when not changing anything, i am prompted to save th thing. its so bad that i can literally open the workbook then click the clos button straight away and it STILL prompts me to save it because i seems to think something has changed. as far as i can see i dont have any macros or anything that will ru every time it starts and change something (i did have a = today in on cell which kept the date as today but taking this out made n difference -- Message posted from http://www.ExcelForum.com Hi Neowok! You prob...

combining columns all the way down
I am trying to combine two columns of information in excel but th concatenate function doesnt seem to work for it. It may just be m ineptitude in excel but I just cant seem to figure this out. I have 3 colums Column A__________Column B___________Column C tree_______________ .jpg dog________________.gif House______________.png But I cant seem to be able to make it so that column C has tree.jpg an dog.gif and house.png Is concatenate the wrong thing to use here? or am I making my formula incorrectly -- sparkrom ----------------------------------------------------------------------- sparkro...

Outlook 2007: is there a way to use it as a Diary
I'd like to keep a daily journal of important notes, etc. In other words , I would like to keep a Diary. Does Outlook 2007 have this capability? TIA Sure - Ctrl+8 to view the journal or you can create a Mail and Post folder type and post to it. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH...

CRM prompts for user name and password on domain joined computer but not for all users, only a few
Hello, We are having a problem, it appears to be related to the users that have installed IE 7 but we are not sure. We are using CRM 3 and all our users are domain authed, people are using the web interface and the outlook one. We have a couple users (not all) that are getting a login box everytime they try to do anything in CRM. One for example, when he opens outlook he gets the message that the crm server could not be located, if we click on the accounts folder in outlook it asks for his login info, he enters it and the accounts show, but when he double clicks on an account it asks for h...

Is there a way to test if a control exists on a userform?
I have a sub (ClearUserform) that is called by several userforms. Sometimes the control may not be on the userform passed to ClearUserform, thus I get an error. So I added the On Error Resume Next statement to quickly work around the error. Is there a way to test if the control exists on the userform passed to the sub. Sub MySub() Call ClearUserform(Userform1) End Sub Sub ClearUserform(MyForm As UserForm) On Error Resume Next MyForm.TextBox1 = "" ' i have other controls list here On Error GoTo 0 End Sub -- Cheers,...

Making a template that puts the current date in the document so that does NOT change
I'd like to make a template that sets up some standard headers and formatting for new Word documents for a night school course I'm enrolled in. Among other requirements for all papers is to put the date the paper was created at the top. I'd furthermore like it so that if I need to reopen the document after creating it to say print another copy, the date written at the top will not change. In other words, when I create a new document using the template the current date is put near the top, but when I subsequently open the file for editing or reprinting it does NOT automat...

how to set up a query
I am using sql server 2005 express and have 3 tables Table1 Dept_Id (primary key) Dept_Name Table2 Employee_Id (primary key) Dept_Id (foreign key with table1) Employee_Name Table3 WorkSchedule_Id (primary key) Employee_Id (foreing key with table 2) Date_To_Work (date type) I want to list all the Departments (Dept_Name) that do not have anyone scheduled to work on a particular date (ie '1/20/2010' ) Any help would be appriciated. Thanks in advance, RABMissouri2010 Try this: SELECT dept_name FROM Table1 AS D WHERE NOT EXISTS(SELECT * ...

Excel comparative query
I'm respectfully requesting assistance with the correct formula to use for the following query: I'd searching all of column A on spreadsheet 1 to see if a value in column A of spreadsheet 2 is there, and placing a result X (or another) next to (adjacent cell) the confirmed value on spreadsheet 2. Please help if you can. Chris Hi Chris see your answer in puclic.excel -- Regards Frank Kabel Frankfurt, Germany Chris wrote: > I'm respectfully requesting assistance with the correct formula > to use for the following query: > > I'd searching all of column A on ...