Using Excel form to gather data for use in Access database
John Williamson
geographically dispersed team concerning a new project. Some of the team
members do not have Access 2000 and some do not have any database on their
work stations. They all have Excel 2000 or 97. Can I use a form in Excel
(that I design to resemble the Access form for data entry) to collect the
same fields of data (number, text, memo, and yes/no)? I would like the team
members working in Excel to open a form, enter their observations, hit a
button for data entry, and have the form progress to the next row/record for
their next observation. As you can see from the question, I am not
experienced in Excel programming, but I suspect this is the right news group
for this question, even though it may be rudimentary. Thanks for your
insights, best regards, John
Ronald Dodge
There's a few things you can do. One of the things I have done is to first,
prepare the worksheet manually of all the formats and information you want
in it. Use row 3 as the header row to leave the top 2 rows for other things
that you may want to use it for. In cell A2, have that set as a number
formatted with 0 decimal places, and white font on white background, as the
info in it would be needed to make things easier, but would not be needed to
be visible. This particular cell would hold the number of the first row
below row 3 (the header row) that is empty.
STEP 2: Build Data Entry Form In VBA
Next you will want to build the form in the VBA. This will allow you to
customize your form with the varioius tools and make it more user friendly.
This part is relatively easy to do for the most part, but can be tricky in a
few places, like if you want only particular option available, that would
need to be setup with a part of the workbook that is set aside to have a
list of the only options available for that particular field within the
form.
It would be nice to rename each of the text boxes and other direct entry
objects to a name that reflects the type of the info that is being recorded
on the form (This is to make the coding a bit easier to understand once
coded and easier to debug in the event it needs to be debugged. Note, the
name of it will not contain spaces). For instance, you may want the PO #
text box be named as PONum. Also, it would be better to rename the userform
and command button names so as once again, understanding the code and
debugging is easier to handle. Another thing that would help is using
labels to go with the text boxes/lists fields.
STEP 3: Building The Initial Code To Record Data Into Worksheet
Now that you have prepared the worksheet and made up a friendly user form
along with friendly names to the entry boxes and form name, you can now work
on the coding side. You will initially put in the code to record the data o
nto the worksheet. How this will work, building onto with what's been done
on the first 2 steps, here's an example of a code that records data into a
cell.
Assuming the PO # is the first field of the table within the DB, you will
have the following code going onto the worksheet in column A.
You would first, doubleclick on the command button that the others will
click on to enter the data into the worksheet. Once you do that, a private
sub will come up and have the sub and sub end lines in place already. Leave
those lines alone, and go in between those lines to record the actual code.
To start it off, you will want to put in the code to enter the data into the
worksheet. The following code for the PO # would be as follows.
Range ("A" & Cells(2,1).Value) = <UserFormName>.<TextBoxName>.Text
Note, the Cells command has the row number as the first argument, then the
column number as the second argument. That means, the 2 is for row 2, and
the 1 is for column 1 or column A, thus cell A2. The .value after that
command just takes the value of the cell. In this case, assuming row 3 is
empty, it would be 3. Now comes the Range command. Now that you have the A
inside double quotes ("), then that is the letter part of the reange
reference. The '&' is a concatenate type deal, than you have the Value of 3
added to it to have it show A3. Now that we have it refering to Range A3,
it will then equal to what ever is in the PONum text box. You will use this
type of a line for each of the fields within the form to be recorded onto
the worksheet.
Next comes moving the number in Cell A2 to the next empty row which should
be A4 in this case, so the next line of code would be:
Range("A2") = Range("A2") + 1
This is the end of the initial code building, though you may want to test it
out with using appropriate formatted data entry text before going onto the
next step to make sure this step is properly coded. If not, then go back
and debug.
STEP 4: Putting In Error Checking Code
Once Step 3 has past with it's error checking for the bugs with the initial
coding, then it's time to put in the code to check against errors that the
user may put in. This would involve things like making sure user don't
enter alpha where it's only numeric, not numbers where it's only alpha, make
sure it's appropriate dates in the date fields, etc. This is the part of
the coding that can get to be hairy, but is a necessary part to safeguard
that the data being recorded is in the appropriate format and in the
appropriate fields. Some ppl think of this as Error Trapping Coding.
Once again, after you have put in the code, you will want to test it out to
be sure if it's suitable or not. Not only do you want to test it out with
information to cause it to error out, but also with information that is
suppose to be accepted as there are few cases where the supposed code that
was initial expected to work won't work due to some IEEE standard or other
situation that would cause it to fail to work. Floating numbers is a prime
example of this, so this is the part that really gets to be rather
creative/hairy in the coding at times.
STEP 5: Converting to Access DB.
I haven't dealt with it all that much, but you can convert the Excel data in
lists into an Access table. The way I know how to do is on the Excel side
of it. In Excel, goto Data, then Convert to Access. However, before you do
this, you do have to save the file.
You can theroetically skip step 4, and go onto this step, but it's not
recommended cause you want to be sure the data that is being taken into
Access is properly entered in Excel first.
I know there's a lot to it, but hopefully, this will get you started and I
am sure you will still have more questions as you go along. These steps for
the most part are just general steps with a few technical examples of
coding. One other things that is highly suggested is that you document what
the code is doing and the intent of the code so as it can be more easily
figured out for debugging purpose.
Ronald Dodge
Production Statistician
"John Williamson" <jwill...@williamsonresearch.com> wrote in message
news:e9NM07kEBHA.1568@tkmsftngp07...
Tom Ogilvy
http://support.microsoft.com/support/kb/articles/Q161/5/14.asp
XL97: How to Use a UserForm for Entering Data
Appicable to xl97 and later.
Regards,
Tom Ogilvy
John Williamson <jwill...@williamsonresearch.com> wrote in message
news:e9NM07kEBHA.1568@tkmsftngp07...
John Williamson
Thanks, Ron, for the extensive submission. You've given me alot to work
with.
I was experimenting with Data>MSAccessForm in Excel. It opens an Access
session, creates a form, and creates an Excel object in the Access database.
I have not yet figured out what this is trying to do, but I can understand
the approach you suggested. It's quite a bit of coding for 43 fields!
Thanks again, John.
"Ronald Dodge" <rdo...@cinci.rr.com> wrote in message
news:q9x67.54199$dd1.7...@typhoon.neo.rr.com...
John Williamson
"Tom Ogilvy" <twog...@email.msn.com> wrote in message
news:OfL3Q$qEBHA.568@tkmsftngp02...