MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Database Forms

Posted by John Hung on November 24, 2000 12:12 PM

Problem - Getting data into Database (Access, Excel?)
- Two types of users (up to ten users)
One type with mobile PC to enter data
One type with pen and paper only
- Would like to design Data Gathering in Excel
for the mobile PC users to collect data then
upload (via macro)to database when they return
to the office
- Would use the same format to collect data
with users without the PCs and have them
enter it into the same forms in Excel and
upload to the database

1. Only one license of Access that's why data
will be entered into an Excel spreadsheet.

2. Like a purhase order, where there is a one to many
relationship (one order - multiple items).

3. Items (there are hundreds) are in a table with sku
numbers and descriptions.

What I like to do is create a form to gather the
information (with lookups to pick the Items) and have
the information held in Excel worksheets and upload to
the Access database. Keeping in mind to make data
entry as easy as possible for the users.

Any ideas how to do this?



Posted by Greg on December 03, 2000 8:55 PM

response: What you are looking for would be fairly complicated to do, as far as I know. In order to get the product information you could have them get the information from the Access database and store it in a spreadsheet, which can be done by recording a macro. The look up part you could record a look up macro and incorporate it in a button. The order part you would probably have to either make seperate files by day and then seperate sheets by customer order. Either way it will be pretty mickey mouse. The uploading part would be the easy part, you just have to use DAO from Excel to Access. You just have to figure out how you want to set up the info for the mobile user that is compatible for Access.