word forms, access, and indefinite numbers of entries

[Kenneth_J]

Hey everyone,
I just started doin some work with access and vba, and don't know
much about it yet...any help would be much appreciated.

My goal is to create a form in word that can be filled out, printed,
and have its data be written to an accesss database. One of the
sections of this form is for a list of items which are purchased and
various cost and descriptive information about those items. I began
by creating text boxes for each piece of information and pulling data
out that way, however since there could be any number of listed items
in a given instance of the form, this won't really work.

So is there any reasonable way to allow a user to keep entering in
additional items into the form, and eventually have the database
updated to coincide with them.

Also, it may or may not be important but i'm also setting it up so
that when a user enters a product number in one text box information
is pulled out of the DB and added to the form containing the price and
some of the other descriptive data that goes along with it.

thanks
keith

[Pieter Linden]

ka*******@smcm. edu (Kenneth_J) wrote in message news:<92******* *************** ****@posting.go ogle.com>...

Hey everyone,
I just started doin some work with access and vba, and don't know
much about it yet...any help would be much appreciated.

My goal is to create a form in word that can be filled out, printed,
and have its data be written to an accesss database. One of the
sections of this form is for a list of items which are purchased and
various cost and descriptive information about those items. I began
by creating text boxes for each piece of information and pulling data
out that way, however since there could be any number of listed items
in a given instance of the form, this won't really work.

So is there any reasonable way to allow a user to keep entering in
additional items into the form, and eventually have the database
updated to coincide with them.

Also, it may or may not be important but i'm also setting it up so
that when a user enters a product number in one text box information
is pulled out of the DB and added to the form containing the price and
some of the other descriptive data that goes along with it.

thanks
keith

Make life easy on yourself. Do it in Access. Going about it this way
is going to be hard in the long run, I think. You can't trap for
invalid entries etc... well, I guess you could using macros etc, but
why make your life so hard? Just use the functionality built into
Access and it will do most if not all of the work for you.

[James Fortune]

ka*******@smcm. edu (Kenneth_J) wrote in message news:<92******* *************** ****@posting.go ogle.com>...

Hey everyone,
I just started doin some work with access and vba, and don't know
much about it yet...any help would be much appreciated.

My goal is to create a form in word that can be filled out, printed,
and have its data be written to an accesss database. One of the
sections of this form is for a list of items which are purchased and
various cost and descriptive information about those items. I began
by creating text boxes for each piece of information and pulling data
out that way, however since there could be any number of listed items
in a given instance of the form, this won't really work.

So is there any reasonable way to allow a user to keep entering in
additional items into the form, and eventually have the database
updated to coincide with them.

Also, it may or may not be important but i'm also setting it up so
that when a user enters a product number in one text box information
is pulled out of the DB and added to the form containing the price and
some of the other descriptive data that goes along with it.

thanks
keith

If I remember correctly, MS Word has a table structure that displays
items in rows and columns. I think the Object Tree for Word allows
Access to reference the values in these Word tables using Automation.

James A. Fortune

[Pieter Linden]

> If I remember correctly, MS Word has a table structure that displays

items in rows and columns. I think the Object Tree for Word allows
Access to reference the values in these Word tables using Automation.

James A. Fortune

Yes, you can walk the tables collection and then the columns/rows of
that collection and write the data to a database, if you want...

[James Fortune]

pi********@hotm ail.com (Pieter Linden) wrote in message news:<bf******* *************** ****@posting.go ogle.com>...

If I remember correctly, MS Word has a table structure that displays
items in rows and columns. I think the Object Tree for Word allows
Access to reference the values in these Word tables using Automation.

James A. Fortune

Yes, you can walk the tables collection and then the columns/rows of
that collection and write the data to a database, if you want...

Pieter,

I'm not disagreeing with you. Access is the best way. I was trying
to answer the original question in case he HAD to use Word. Also,
Automation can go the other way. Access can have Word create
documents if needed.

James A. Fortune

[Kenneth_J]

Thanks for the help,
I am somewhat locked into using word unfortunately for this part of
the project...

one other thing i was having trouble with was programatically setting
the type of text form field, to date, number, calculate etc.

is this possible? ...specifically for calculate.

thanks,
keith

[Pieter Linden]

ka*******@smcm. edu (Kenneth_J) wrote in message news:<92******* *************** ****@posting.go ogle.com>...

Thanks for the help,
I am somewhat locked into using word unfortunately for this part of
the project...

one other thing i was having trouble with was programatically setting
the type of text form field, to date, number, calculate etc.

is this possible? ...specifically for calculate.

Keith,

part 1:
Ho boy. If you are going to do this, I would recommend naming your
form fields explicitly so that they correspond with the fields in your
database. Otherwise, getting the data into the database is going to
be a nightmare. If you're consistent, it should be quite easy.

Updating the database based on data in the forms... in real time? I
doubt it. Unless you did something fun like put all the forms to be
imported into a specific folder, wrote a routine in Access that ran on
a timer event, checked the folder for any files, imported them, and
then moved the successfully imported files into another folder.

Pulling data out of the database... Not sure. You might have to have
a macro do that. In a database form it's like doing a lookup. Maybe
you could pass an array of values and return a recordset of values
back to your form and populate that way. (Like I said, MUCH easier in
Access, but yes I know - not an option at the moment.) My guess would
be to include a macro that did all the looking up of information from
Access, pass it as a recordset, and away you go. Easier said than
done, though.

Programmaticall y setting the type of field on your form? Not that I
know of. You could copy and paste fields, I guess.

[Nicole]

> My goal is to create a form in word that can be filled out, printed,

and have its data be written to an accesss database. One of the
sections of this form is for a list of items which are purchased and
various cost and descriptive information about those items. I began
by creating text boxes for each piece of information and pulling data
out that way, however since there could be any number of listed items
in a given instance of the form, this won't really work.

So is there any reasonable way to allow a user to keep entering in
additional items into the form, and eventually have the database
updated to coincide with them.

Also, it may or may not be important but i'm also setting it up so
that when a user enters a product number in one text box information
is pulled out of the DB and added to the form containing the price and
some of the other descriptive data that goes along with it.

Keith, you have really set yourself up a tough row to hoe. You are
essentially trying to get Word to be a database front end, and I think
even if you get the form to work, it will be a troublesome and erratic
child. Your job will be far easier if the data is entered into Access
and then ported to a Word document as needed for printing, emailing,
etc.

Instead of a real-time situation, you may have more luck by having the
user press a "save to database" button that then collects the data and
updates the appropriate record in Access. This doesn't solve your
lookup problem though.

Since you say you are new, perhaps if you explain why you think you
are stuck with Word, there may be an alternative option? I can think
of only one reason why it would be imperative to have the users use
Word to data enter, and that is if people were trying to pull data
from a database without a licensed copy of Access. (That, and the
other reason is because the boss says so... a harder issue to get
around!) If it's a licensing issue and the money is not there to buy
more licenses or a Developer Edition, then an intranet web page may be
the easier and more stable route.

However, I suggest experimenting with MS Query to see if it will solve
your problem, if you must do it this way.

Nicole