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 data through Access to use a Form as an Interface
I have an Excel File (Excel 97) with a lot of data and to
make data entry easier and more user friendly I have linked the data to a MS Access (Access 97) database using ACCLINK.XLA, I have modified Access to have a subform within the form, (form is based on a query, subform linked directly to the excel linked table) However when the new record button is selected on the Access subform, Data can be entered and appears in the excel file on the next blank line on the worksheet, however it does not refresh into the Access database linked table. So when access is closed (and the form re- opened) using the (RedoFormCommand) button in excel the new line does not appear in the database and hence on the form. it does not seem to take account of the new number of rows that have been added from the Access form The only way I can overcome this is to cut the new line and 'insert cut cells' further up the list. i have tried using a macro that automatically does the cutting, inserting and then re-sorting. But by using the macro when opening the Access form it does not account for the new number of rows. So if they have been re-sorted, it will ignore old data that is now at the bottom of the list and outside the number of lines that seems to be stored in memory for linking to Access. As ACCLINK.XLA is password protected i cannot get into the code to see if I can replicate it in an ordinary macro, and change some things so it picks up the new number of rows to be linked to Access table (hence form) Can anybody offer any help please, cheers. |
Thread Tools | |
Display Modes | |
|
|