A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Linking Excel spreadsheet



 
 
Thread Tools Display Modes
  #1  
Old April 21st, 2008, 07:00 PM posted to microsoft.public.access.forms
Dialyn
external usenet poster
 
Posts: 2
Default Linking Excel spreadsheet

In setting up a database for work I have found the need to link forms done in
excel that need to be completed for each record in the database. I'd like to
be able to do that from the main data input form by adding the worksheet as
an object that opens excel and the appropriate form.

I haven't had a problem adding the worksheet to the form, however, I need to
save each completed form and tie it to that specific record for later
reference and/or printing. When I go to the next record and open the
worksheet I should be getting a new blank form but instead I'm getting the
completed form from the previous record. HELP! I'm not even going to
mention that autofilling some information from the record into the form would
be wonderful, however, right now I just want it to work!
  #2  
Old April 21st, 2008, 11:05 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default Linking Excel spreadsheet

You have a problem. First, data in Excel spreadsheets linked as a table
cannot be changed, it can only be viewed. This is a limitation imposed
because of a lawsuit Microsoft lost a couple of years ago.

Also, you are describing something incorrectly and it is causing confusion.
Are you saying you want to relate a specific Excel worksheet to a record in
an Access table? It is unclear, because you talk about saving forms, but
forms do not contain or relate to any specific data. Only tables contain
data. Forms are a tool to enter, view, and modify data in tables. Queries
can be used in place of tables, but they only present or modify data in
tables, they don't store any data.

If you can better describe what you are trying to do, perhaps we can help
with the how to part.
--
Dave Hargis, Microsoft Access MVP


"Dialyn" wrote:

In setting up a database for work I have found the need to link forms done in
excel that need to be completed for each record in the database. I'd like to
be able to do that from the main data input form by adding the worksheet as
an object that opens excel and the appropriate form.

I haven't had a problem adding the worksheet to the form, however, I need to
save each completed form and tie it to that specific record for later
reference and/or printing. When I go to the next record and open the
worksheet I should be getting a new blank form but instead I'm getting the
completed form from the previous record. HELP! I'm not even going to
mention that autofilling some information from the record into the form would
be wonderful, however, right now I just want it to work!

  #3  
Old April 22nd, 2008, 06:55 PM posted to microsoft.public.access.forms
Dialyn
external usenet poster
 
Posts: 2
Default Linking Excel spreadsheet

I'm sorry I should have been more specific. I am working with a database
that contains owner and unit information for a senior citizens community.
Each unit or condominium is unique with various information related to it.
One of the most frequent needs is for mailing labels and being able to
identify where the mail should be sent, frequently the billing or invoicing
address is not the same as informational mail for the owners.

Because this is a senior citizens community, there are several other things
that we associate with the record. Does this person live alone? Do they
have pets? Have they given us instruction on who to call in the event of an
emergency? If they have a pet, have they told us who to release the pet to
in the event of an emergency? Are they out of town and when do they expect
to return? Also, we maintain information regarding medications they are on,
their doctor, etc. Quite literally I want to be able to access a record and
be able to see everything that relates to that unit including whether or not
that person is mobile or needs assistance to get out of the building in an
emergency.

The medical information is entered onto a paper form for them to keep
available in their unit in the event of a 911 emergency. This is the form I
was referring to. I understand now that this cannot be completed through
Access, however, if I scanned the completed form, would I then be able to
attach it to the record? It is not uncommon for our residents to misplace
things given their ages. I'd like to avoid the necessity of maintaining
paper files of this information as it would become a privacy issue. It would
much easier for me to limit access to the information in access than in hard
copy form.

I have worked with Access in the past, but it was Access 2000 and I'm
working with Access 2003 now. I'm finding little things that aren't
consistant with the previous version and being somewhat of a novice it tends
to get frustrating. Any help you can offer would be greatly appreciated!!

"Dialyn" wrote:

In setting up a database for work I have found the need to link forms done in
excel that need to be completed for each record in the database. I'd like to
be able to do that from the main data input form by adding the worksheet as
an object that opens excel and the appropriate form.

I haven't had a problem adding the worksheet to the form, however, I need to
save each completed form and tie it to that specific record for later
reference and/or printing. When I go to the next record and open the
worksheet I should be getting a new blank form but instead I'm getting the
completed form from the previous record. HELP! I'm not even going to
mention that autofilling some information from the record into the form would
be wonderful, however, right now I just want it to work!

  #4  
Old April 22nd, 2008, 08:25 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default Linking Excel spreadsheet

That is very different from linking an excel spreadsheet.
There is no reason you could not maintain the data in Access, but if you
would rather, you can put all the scanned forms in a folder, create a
hyperlink field in your database and use the FollowHyperLink method to show
the paper form.

If I were doing it, I would enter the data in Access where it could be
maintained easily. I would also create a report that looks like the paper
form and every time I made a change to the unit's record, I would print a
copy and send it to the resident for verification. It would also be useful
for providing the resident a replacement if they loose it.
--
Dave Hargis, Microsoft Access MVP


"Dialyn" wrote:

I'm sorry I should have been more specific. I am working with a database
that contains owner and unit information for a senior citizens community.
Each unit or condominium is unique with various information related to it.
One of the most frequent needs is for mailing labels and being able to
identify where the mail should be sent, frequently the billing or invoicing
address is not the same as informational mail for the owners.

Because this is a senior citizens community, there are several other things
that we associate with the record. Does this person live alone? Do they
have pets? Have they given us instruction on who to call in the event of an
emergency? If they have a pet, have they told us who to release the pet to
in the event of an emergency? Are they out of town and when do they expect
to return? Also, we maintain information regarding medications they are on,
their doctor, etc. Quite literally I want to be able to access a record and
be able to see everything that relates to that unit including whether or not
that person is mobile or needs assistance to get out of the building in an
emergency.

The medical information is entered onto a paper form for them to keep
available in their unit in the event of a 911 emergency. This is the form I
was referring to. I understand now that this cannot be completed through
Access, however, if I scanned the completed form, would I then be able to
attach it to the record? It is not uncommon for our residents to misplace
things given their ages. I'd like to avoid the necessity of maintaining
paper files of this information as it would become a privacy issue. It would
much easier for me to limit access to the information in access than in hard
copy form.

I have worked with Access in the past, but it was Access 2000 and I'm
working with Access 2003 now. I'm finding little things that aren't
consistant with the previous version and being somewhat of a novice it tends
to get frustrating. Any help you can offer would be greatly appreciated!!

"Dialyn" wrote:

In setting up a database for work I have found the need to link forms done in
excel that need to be completed for each record in the database. I'd like to
be able to do that from the main data input form by adding the worksheet as
an object that opens excel and the appropriate form.

I haven't had a problem adding the worksheet to the form, however, I need to
save each completed form and tie it to that specific record for later
reference and/or printing. When I go to the next record and open the
worksheet I should be getting a new blank form but instead I'm getting the
completed form from the previous record. HELP! I'm not even going to
mention that autofilling some information from the record into the form would
be wonderful, however, right now I just want it to work!

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:04 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.