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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|