Access or Excel? (2000)

[armitageshanks]

Hi,
Not too sure which application I should be doing this in........
I've attached a form that I'd like to fill via a user form (or similar). I have fiddled with it a bit and found the user form a bit cumbersome due to space restrictions (I don't want the user to scroll down the user form, rather it take up the entire screen). I did debate whether, if possible, to have more than one user form relating to each area of the form and for the user to link to this through the first user form. Or to do it in Access which would simplify things.................but can I then transport the data to the excel sheet?
Basically there are certain fields where the user can select data, and other fields are automatically filled dependant on the data selected.
I have a feeling I'll be doing it Access, but how do I go about transporting the data to the attached sheet?

[dazednconfused]

Hi

You can export data to Excel, from Access, in a number of ways, transferspreadsheet, DoCmd.OutputTo, or automation. Which way you do depends on exactly how you want to manipulate the data as it leaves Access.

Also, why use Excel and Access? Why not just allow the user to select specific data for viewing through the Access database?

Ian

[HansV]

Your attachment hasn't made it (yet). You could use a multipage control on the userform; this is comparable to the tab control available in Access.

[armitageshanks]

Yes, my mistake, the excel table has been designed (not by me) in the most bizarre way over 100 cells across (although 6 would work fine), so a redesign would be needed to compress the data to a feasible size........ <img src=/S/doh.gif border=0 alt=doh width=15 height=15>.
How would I go about creating a multipage control?

[HansV]

When you wrote "user form", I thought you meant a userform created in the Visual Basic Editor; just like in Excel, you have a toolbox with controls you can put on a userform; the MultiPage control is one of them But now I'm starting to think you meant a form on a worksheet. You can put a MultiPage control on a worksheet too, but I'm not sure that is such a great idea. Can you tell us what you're working with?

[armitageshanks]

No I did mean a user form, as in Ctrl & F11.
The form is one created in excel by some other soul, the user simply fills in the unprotected cells with information.
But going from you previous post a multipage would be fine. I presume the multipage would each have its own controls as if each page were a seperate user form.

[HansV]

Yes, each page of the multipage control has its own set of controls, so you can display a lot of controls in a limited space by using several tab pages.

[armitageshanks]

Thanks Hans, that will do nicely............