This sounds like a referencing problem. What version of access are you
using? While in the VBA editor goto Tools>References and list all the items
that are ticked. Also while you're in the editor goto Debug and click
Compile. What is the line of code that is highlighted in yellow? Copy and
paste that block of code into your reply message and indicate which line was
highlighted.
Stewart
"Carl" <carl.sprake@oxmhc-tr.nhs.uk> wrote in message
news:838d7e69.0310270802.4af1dc83@posting.google.c om...[color=blue]
> Hi Stewart,
>
> I have split the database so that the back end sits on the server and
> everything else is in "My Documents". I am trying to add the code to
> the Switchboard Unload Event. From the Properties window I clicked
> next to On Unload
> and then on the button with the three dots. This took me to the VBA
> editor and I pasted the following
>
> On Error Resume Next
> DoCmd.OuptputTo , "Student List", acFormatXLS, "C:\My
> Documents\Students.xls", False
>
>
> into the function that had been created to give:(the False is on the
> same line as the DoCmd)
>
> Private Sub Form_Unload(Cancel As Integer)
> On Error Resume Next
> DoCmd.OuptputTo , "Student List", acFormatXLS, "C:\My
> Documents\Students.xls", False
>
> End Sub
>
>
> I now get a Compile error:
> Method or Data Member not found
>
> when I try to close the database
>
> Please can you offer any suggestions?
>
> TIA
> Carl
>
> "Stewart Allen" <sagasu@REMOVETHISwave.co.nz> wrote in message[/color]
news:<bn5h4h$7ue$1@news.wave.co.nz>...[color=blue][color=green]
> > Are there any error messages when using the code? Remove the "on error
> > resume next" line and see if any error messages pop up. The[/color][/color]
DoCmd.OutputTo[color=blue][color=green]
> > line is on one line, the newreader has wrapped the code to the next[/color][/color]
line. If[color=blue][color=green]
> > you want Excel to open the newly created file, change the False to True.
> >
> > Yes there is a way to do this automatically when Access closes. If you[/color][/color]
have[color=blue][color=green]
> > a switchboard (which should be the first form to open) place the
> > DoCmd.OutputTo line in the Unload event of the switchboard form. If you[/color][/color]
have[color=blue][color=green]
> > several users opening the databse at the same time, consider splitting[/color][/color]
the[color=blue][color=green]
> > database into 2; one holds the tables only and is placed on a server or
> > dedicated PC, and every user has their own copy of the user interface on
> > their own PCs. These contains all the forms, queries, reports etc and[/color][/color]
have[color=blue][color=green]
> > linked tables to the backend. Check this link for more details
> >
http://members.iinet.net.au/~allenbrowne/ser-01.html
> >
> > Stewart
> >
> >
> > "Carl" <carl.sprake@oxmhc-tr.nhs.uk> wrote in message
> > news:838d7e69.0310212358.8484115@posting.google.co m...[color=darkred]
> > > Thanks Stewart. I couldn't get this to work but I was able to create a
> > > macro and use the OutputTo action from there.
> > >
> > > Please can you tell me if there is any way to automate this when a
> > > user closes out of Access? And if so, does it matter if other users
> > > have the database open at the same time?
> > >
> > > TIA
> > >
> > > Carl
> > >
> > > "Stewart Allen" <sagasu@REMOVETHISwave.co.nz> wrote in message[/color]
> > news:<bn2vot$ijo$1@news.wave.co.nz>...[color=darkred]
> > > > Hi Carl
> > > >
> > > > Create a command button on your form (without using the wizard) and[/color][/color][/color]
in[color=blue][color=green]
> > the[color=darkred]
> > > > On Click event type the following;
> > > >
> > > > On Error Resume Next
> > > > DoCmd.OuptputTo, "Student List", acFormatXLS, "C:\My
> > > > Documents\Students.xls", False
> > > >
> > > >
> > > > For more details look at OutputTo in the help file.
> > > >
> > > > Stewart
> > > >
> > > >
> > > > "Carl" <carl.sprake@oxmhc-tr.nhs.uk> wrote in message
> > > > news:838d7e69.0310210147.7a27e8c4@posting.google.c om...
> > > > > Please can anyone tell me how I can create a macro to save the[/color][/color][/color]
results[color=blue][color=green][color=darkred]
> > > > > of a query as an excel file? The query is called Student List and[/color][/color][/color]
I[color=blue][color=green][color=darkred]
> > > > > would like to save it to "My Documents".
> > > > >
> > > > > We have had some problems with our database crashing. We would
> > > > > therefore like another way to have access to basic student data[/color][/color][/color]
and[color=blue][color=green][color=darkred]
> > > > > thought we automatically save the results of this query when we[/color][/color][/color]
close[color=blue][color=green][color=darkred]
> > > > > Access.
> > > > >
> > > > > Any suggestions on how we could do this would be really[/color][/color][/color]
appreciated[color=blue][color=green][color=darkred]
> > > > >
> > > > > Thanks a lot
> > > > >
> > > > > Carl
> > > > > Oxford UK[/color][/color][/color]
Macro to save query as Excel file 
