Convert/ transfer an Access form into an Excel form that will be used for data input

I have an Access database that works fine with nice forms. We use the forms to input data.
Now, I'd like others to do the form data entry: my idea is to send them an Excel file which displays an Excel form. When all the data is entered in the Excel file, this file is sent back to me, I do some automated checks and then update the Access file from the data in the Excel file.

My question is: the Access form is highly complete, and I would prefer to use a similar-looking form for data entry. Is there an easy way to do that?
LVL 29
Bernard S.CTOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I don't really know about Excel forms...  But why not send a copy of your access mdb, they input data in it and returne it, then you can run an insert query from the copy mdb to the main mdb...
You could also create a second database with just the forms. The Based tables are linked in, so the new database file is not large, and they are entering directly into your main tables.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The answer is no - Excel isn't a data-entry program but is instead an analytical tool. You don't have the same GUI tools available in Excel as you do in Access, so you'd be quite hard-pressed to simulate a "nice-looking" Access form in Excel.

I second the idea of sending them a database (.mde format, by the way) to do this. The concerns there would be:

If you've used any ActiveX controls in your design you'd have to distribute and install them
The enduser would have to have Access installed on their machine (either full or runtime)
You'll have to support it

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Richard DanekeTrainerCommented:
Excel can provide a form for data entry, but the data cannot be validated using the Data, Form option.  
If you enter the data in rows in Excel, you can use Data, Validation to limit or test the data and you can use formulas for VLOOKUP against table data  to fill in some cells.   The data can then be imported into Access with a combination of Append, Delete, and Update queries to add back into access tables.  
A form can be designed with VBA and forms controls for data entry similar to the Access forms and storing the data in a table in Excel - either in another worksheet or in a linked workbook.

If you are wanting others to share in data entry that do not have Access installed, you should look into saving your form as a Data Access Page, then the others on the LAN can use their Internet browser to assist with data entry.
Bernard S.CTOAuthor Commented:
How do I create a Data Access Page (I presume this is buried in some strange place in some strange menu...)
Coudl they be updated off-line?
That would certainly help me for the capture/transfer of information.

Is there a "good way" to avoid redesigning the Excel form while the Access form is here readily available?
Richard DanekeTrainerCommented:
The Data Access Page is one of the Access objects - after Forms, Reports, then Pages.  
The quickest way is to open a form and then choose File, Save As... and select Data Access Page.
Access will create a shortcut to the html based page in the Pages section.  The data access page and the access database must be stored in a shared folder available to everyone that needs to use it.  Typically, to a network drive.
It is okay to move your Access database to the shared drive then create the data access page.
The database will be shared - many people can add/edit at the same time, but off-line data entry is not possible.  What are the issues for working off-line?
Bernard S.CTOAuthor Commented:
1 - Use scenario:
a = an (Excel) file is put on line or sent by mail. It is a form for a credit authorization request.
b= the form in the Excel file is used to enter all the data: name, address, background info, amounts, salary, etc.
c = once the data entered, the XL file is sent to the company, where it is used to add a record into the right table.

2 - More precisions on the initial question
- I think I wil be able to solve most or all the problems in b/ andc/
- I know I can build the Excel form that will be used to transfer the info. This process is going to be a long pain, because there are 100 + fields to place correctly on the form. I was hoping to fime some better way to solve this tedious formmaking process.
Richard DanekeTrainerCommented:
I think I would create a worksheet with the cells needed and arrange in a pleasant format similar to the paper document.  Use lookups (vlookup or hlookup) to pull from tables to complete data and use Data, Validation to qualify the data, to prompt for user input, and to customize error messages as much as possible.  

Hide the gridlines to make the worksheet more form like in appearance.

On a second, hidden worksheet I would transfer the data into a row like format for data import to access.  The simple use of the basic formula =Sheet1!A4 will do that.

You may be able to structure this hidden sheet to be able to copy/paste the Excel row directly into an Access record.  

Use Tools, Proection, Protect Workbook to  keep your hidden worksheet secure.  Use Tools, Protection, Protect worksheet to keep your 'form' secure.

This use of a front worksheet to complete a backend worksheet can be reviewed in the Invoice template that ships with Excel.  (File, New...  From Templates)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard DanekeTrainerCommented:
BTW, the data access page can be used over the big WWW internet, but the tricks lay in publishing the data access page to the web, connecting to your Access database (via an ASP connection) and the setting security for your remote access.   Then, you could connect the office Access to the web Access and update records on a scheudled basis.  
Bernard S.CTOAuthor Commented:
Thx, I've a clear process now.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.