Passing a variable to an Excel query

[BajanPOET]

I have an SQL query that selects all the Requisition data out of two Access tables Requisition and Items. I need to isolate a single set of requisition data at a time and send that information into a preformatted Excel spreadsheet.

SELECT Items.Description, Items.Qty, Items.EstPrice, Items.Supplier
FROM `O:\GAIA Requisition`.Items Items, `O:\GAIA Requisition`.Requisition Requisition
WHERE Requisition.ReqNo = Items.ReqNo AND ((Requisition.ReqNo=?))


This SQL query brings up all of the requisition data I have stored in my database. It has been set up with a parameter, so that the user can enter the required requisition number. How do I store the current requisition number in an open Access record and pass it on to this query so that I can isolate data for one requisition at a time and show it in a preformatted Excel spreadsheet?

GOD is in charge, though men may say, "Not so!

 

[SkipVought]

Hi,

If you are using MS Query in Excel, via Data/Get External Data/New Database Query....

once you have inserted your QueryTable, you can Edit Parameters.

Skip,

_[red][/red]

 

[BajanPOET]

I see it.

Now how do I pull the only the requisition number of the current record from Access and send it to this query?

Let me give some details as to what I want to accomplish:

I am running the query from Excel but I want to pull the data from an Access database where the two tables Requisition and Items are located.

The variable I want to pass will contain the requisition number for the current requisition record (from the Access table.) What I have set up so far is this:
1. I have an Access application set up with a switchboard where users can create, edit, view and delete requisitions.
2. Once a requisition is created, it is authorized by senior management. (I just placed a toggle button for authorize/unauthorize on a different form that the manager alone has access to.)
3. A button is on this form which is supposed to open an Excel spreadsheet preformatted as a purchase order that is supposed to be populated from the open Access record.
4. Without the parameter part the SQL statement populates the formatted spreadsheet with all the information in the tables - for all the different requisitions. I would like to be able to store the requisition of the current record in a table and pass it along to the SQL statement (which is in Excel) so that when the automatic query is refreshed only data from the requisition that the user is currently working on will be displayed on the spreadsheet.

GOD is in charge, though men may say, "Not so!

 

[SkipVought]

So you have TWO queries that both fire in the Workbook_Open event.

The first query returns the Requisition Nbr.

The second query uses that value as a criteria and returns that Requisition.

Skip,

_[red][/red]

 

[BajanPOET]

Got it. But my main problem is isolating the requisition number from the current record in Access. What would be the SQL statement to just display the current record's Requisition number? That's my main problem. When I try it, I get all the requsition numbers available - I only want the one that the user is currently working on.


GOD is in charge, though men may say, "Not so!

 

[SkipVought]

I thought that you were stuffing the current requisition nbr into a table (a table with one column and one row)

Skip,

_[red][/red]

 

[BajanPOET]

Hadn't thought of that....why are the answers so simple u don't actually think about them? Thanks.

GOD is in charge, though men may say, "Not so!

 

[BajanPOET]

I've been trying hard to implement that - putting the current req # from an Access table into a single column and one row - but I'm not getting very far. How can I go about doing that?

GOD is in charge, though men may say, "Not so!

 

[BajanPOET]

Got it.

GOD is in charge, though men may say, "Not so!

 

[BajanPOET]

Two more questions to finish off my application:

1. How do I disable the prompts that Excel generates when rows are being deleted and amended? These are operations that should be invisible to the user and I don't want them involved by having to OK something that should be done automatically in the background.

2. More importantly - how do I run queries in Workbook_Open that I've created using the Add External Query | New Database Query wizard?

GOD is in charge, though men may say, "Not so!

 

[PHV]

1) Application.DisplayAlerts = False
2) In the DataRange properties you may choose an automatic refresh on open.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BajanPOET]

Thanks... that should help a lot.

GOD is in charge, though men may say, "Not so!

 

[BajanPOET]

The automatic refresh on open works - I see the data as soon as it opens, but it still brings up a dialog box informing the user that the workbook contains "queries to external data that refresh automatically" and asks the user to enable or disable automatic refresh. If the user clicks disable, the dialog box goes away and everything is fine. If the user clicks enable then the query tries to open the database (which is already open) and another dialog box comes up saying that the database is in a state where it cannot be locked. How do I get rid of this?

GOD is in charge, though men may say, "Not so!

 

[SkipVought]

I hadly ever use this feature because of this dialog. I, rather do the refresh in the Workbook_Open event.

right-click the sheet tab

Select View Code

ctr+R - to view the Project Explorer.

Select the ThieWorkbook object

Select the Workbook in Object dropdown

Select Open in Procedure dropdown

private sub workbook_open()
  sheets("YourSheetNameHere").querytables(1).refresh
end sub

assuming that you have only ONE querytable on that sheet.

Skip,

_[red][/red]

 

[BajanPOET]

Hey Skip... I tried that, but it didn't work... I'm still seeing the dialog box pop up. Any other ideas, or is there something I missed? Can I put the name of the named reference in place of the querytables(1) if there's more than one query?

When I tried entering the code, I didn't see Intellisense pop up (u know, when I put type an object name and then place a full stop, VB automatically shows me a list of properties related to that object.) That didn't show up; I'm wondering if I need to put in different code...

GOD is in charge, though men may say, "Not so!

 

[PHV]

Another way is to play with DAO or ADODB recordsets...

 

[SkipVought]

If the dialog is popping up on open, then you still have that feature checked in the Data Range Properties.

UNCHECK it.

Skip,

_[red][/red]

 

[BajanPOET]

Hey Skip - which feature do I need to uncheck? I'm looking at the Data Range properties, but I don't see what I need to uncheck...

GOD is in charge, though men may say, "Not so!

 

[SkipVought]

Any of the Refresh Controls should be UNCHECKED if you don't want that dialog.

Skip,

_[red][/red]

 

[SkipVought]

Sorry, not ANY of the Refresh Controls, but the Refresh data on file open should be UNCHECKED.

Skip,

_[red][/red]