How do I link a .xls invoice to an access database?

[Guest]

I need the description field in a stock .xls invoice to populate when the
item # has been entered into the item field. I have created the access
database with each item having its own unique ID #.

In the .xls invoice I have Item (101) Description (widget) Quantity (2) and
Cost (.10)

I want to simplify invoicing by only having to enter item # and Qty. The
Description and price should be autopopulated via the information in the
database.

Please Help!

Sincerely Frustrated!

 

[keepITcool]

Hi SF,

this can be done with a "parameter" query.

Via data/import external data/new database query

create a query that will retrieve the desired data.
in the WHERE clause type a question mark for the Acct# field.

Your SQL may look like...

SELECT rep.`acct#`, rep.`line#`, rep.acctname
FROM `D:\accts_sh`.`reporting2004$` rep
WHERE (rep.`acct#`=?)


When you close MSQuery , you'll see the ImportData dialog.
Fill in the destination range.

Now click the Parameters Button

Click it and select a cell that will serve as the Acct# selection.
Check the "auto refresh" checkbox.

that's the idea..

Ofcourse the Acct# Parameter cell needs data validation..
so select another range..
insert a new query to retrieve ALL valid account numbers.
Name that query "AcctNrs"

The designate the AcctNrs range to be the datavalidation source for the
"parameter cell" of the main query.

HTH


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >