We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

Bernard Savonet
on
Medium Priority
8,028 Views
Last Modified: 2012-05-05
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?
Comment
Watch Question

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 (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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



CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT

Author

Commented:
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?
CERTIFIED EXPERT

Commented:
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?
CERTIFIED EXPERT

Author

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.
CERTIFIED EXPERT
Commented:
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)

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT

Commented:
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.  
CERTIFIED EXPERT

Author

Commented:
Thx, I've a clear process now.
B.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.