Link to home
Start Free TrialLog in
Avatar of Bernard Savonet
Bernard SavonetFlag for France

asked on

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

Hi,
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?
Avatar of jmantha709
jmantha709

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.
Avatar of Scott McDaniel (EE MVE )
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



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.
Avatar of Bernard Savonet

ASKER

DoDahD
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?
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?
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.
ASKER CERTIFIED SOLUTION
Avatar of Richard Daneke
Richard Daneke
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.  
Thx, I've a clear process now.
B.