Eliminate multiple inputs

[Guest]

I currently have 20 different queries that all have 1 field called
"destination" in the criteria of each query I have each one calling a
function destInput(). This function simply prompts the user to input a
destination and then takes input writes it to the criteria to return the
specific results that the user entered.

Here is the code for destInput()
Public Function destInput() As String
If strDestination = "" Then
destInput = InputBox("Enter Destination:")
Else
destInput = strDestination
End If
End Function

This works fine. Now, I have another function excelExport() that exports
each query to excel. In this function it calls each query 1 by 1 and thus
each time it calls a query it prompts for the user to input a destination.
This is the problem.

I don't want the user to have to type in the destination more than once. I
would like it if my function could ask for the user to input the destination
once and then write that to each query and then export to excel. Like I said,
I have parts of this and write now it works, its just that I have to sit
there and reinput the same thing over and over.

Any ideas?

 

[Tim Ferguson]

Public Function destInput() As String
If strDestination = "" Then
destInput = InputBox("Enter Destination:")
Else
destInput = strDestination
End If
End Function

Or else in one line:

IIf(Len(strDestination)>0, strDestination, InputBox("etc"))

My main question is, what or where is strDestination? How does it get its
value? Presumably setting it to a non-zero value would prevent the input
box appearing. Generally, the use of global variables is not a Good Idea:
is there any reason why you cannot pass its value as a parameter to the
function?

I don't want the user to have to type in the destination more than
once.

Bet the user doesn't either..

I would like it if my function could ask for the user to input
the destination once and then write that to each query

That sounds reasonable. I don't know your details, but I would probably
go for one of something like:

1) use a parameterised querydef, set up as a MakeTable query pointing at
an Excel worksheet. Then just fill in the parameters collection and run
it in a loop.

2) put the SQL string together in code with suitable substitutions, and
then stick it into a TransferSpreadsheet method call.

Hope that helps


Tim F

 

[Guest]

What do you mean by use a parameterised querydef setup as a Make Table query?

 

[Tim Ferguson]

What do you mean by use a parameterised querydef setup as a Make Table
query?

PARAMETERS MonthNum NUMBER;
SELECT InvNum, OrderBy, DeliverTo, InvDate
INTO NewTable
FROM Invoices
WHERE MONTH(InvDate) = MonthNum
ORDER BY InvDate;



or just use the Parameters menu item in the query design window. A
querydef refers to the queries in the queries pane in the database window
-- in other words, a saved query. It's a Make Table query because of the
INTO clause -- again, it's easiest to set all that up in the query design
grid.

You run the thing in vba like this:

' get a handle to the querydef
Set qdf = QueryDefs("MyMakeTableQuery")

' set the parameter value: in effect the same as
' getting the dialog in the Access GUI
qdf.Parameters("MonthNum") = 9 ' September

' and make it so...
qdf.Execute dbFailOnError


Hope that makes sense


Tim F