Opening an Excel Spreadsheet from Access

[LisaP]

Hi All,

I have created on my form a command button which when clicked should open a particular Excel Spreadsheet. From reviewing old questions on the Forum, the best way to do this is to enter the path for the hyperlink. This works fine, however, my form contains data for logging computer fault calls and the user has to enter a serial number, which needs to be checked on the Excel spreadsheet (this contains all serial numbers which are on contract). As it stands the user would then have to manually search to see if the serial number is on contract. What would be terrific :^) is if this process could be automated.........somehow.

I really don't know where to start with this, would I have to create a macro in Excel to search for the serial number?? Or would I have to get Access to do that? OR CAN IT EVEN BE DONE??

Help/suggestions/advice gratefully received!

 

[Fornatian]

Had you considered using the Excel file as the source for a linked table? You can do this by choosing New-Linked from the Tables tab.

You should then be able to run a a query in Access to notify you if the field is in the table - indirectly checking against the excel file.

Just an idea.

Ian

 

[LisaP]

Thanks for your help.

I have managed to link my excel table and i have created a query which uses criteria from a text box on my main form:- [Forms]![Calls]![SerialNo] - however I keep getting an error message (Numeric Field Overflow 3349) which crashes the db.

HELP....................

Is it possible to write code which will "behind the scenes" search for matching serial no in the linked table and utlise an if, else statement to return a message box in the instance that the serial no's do and do not match?????