I have an Access database (MIP_MCC_Review.mdb) with two tables (MCC and MIP)
that contain data submitted by users using forms of the same names. I have a
query that I built in Excel Microsoft Query that will get specific data
(rows) from the tables in Access and import that data into Excel in a new
worksheet of a workbook that I use (MCC_MIP_Review.xls) for calculations on
the data. I wrote a macro that will create the new worksheet (MIP_Data) and
that works fine. What I want to do now is to modify the same macro to launch
a form I created (dialog box) with one text box and an OK button so the user
can enter a five character string that will determine what specific rows to
get from the database. Once the user enters the search string and clicks OK,
I want to change the SQL "Where" statement to what the user entered in the
dialog box. In other words, my SQL Select statement is WHERE (MIP.`Employee
SEID`='DK8FB'), and based on what the user entered into the dialog box, I
want to change it to WHERE (MIP.`Employee SEID`='HF5JK'), if HF5JK was what
the user entered. Is it possible to change SQL statements based on user input
from a dialog box? If so, how would I do that all in the same macro? Any
assistance would be appreciated.

hi
forget your form or dialog box where the user enter a 5 charcter string. if
i understand you correctly, you have run the MSQ(microsoft query) and now you
want to filter it by running it again?!?!?! based on user input after the
fact?!?!?
do a parameter query. how?
right click in the MSQ. click edit query. click through the wizard to the
last screen. click "view data or edit query in Microsoft query".
when the MSQ dialog box comes up... on the menu bar, click criteriaadd
criteria.
this is where you "change your "where" clause". each time the MSQ is run,
the user will be prompted for the query parameters and will return data based
on the parametes(criteria) the user input.

or did i totally misunderstand what your are trying to do?!?!?
regards
FSt1

"Pat" wrote:

I have an Access database (MIP_MCC_Review.mdb) with two tables (MCC and MIP)
that contain data submitted by users using forms of the same names. I have a
query that I built in Excel Microsoft Query that will get specific data
(rows) from the tables in Access and import that data into Excel in a new
worksheet of a workbook that I use (MCC_MIP_Review.xls) for calculations on
the data. I wrote a macro that will create the new worksheet (MIP_Data) and
that works fine. What I want to do now is to modify the same macro to launch
a form I created (dialog box) with one text box and an OK button so the user
can enter a five character string that will determine what specific rows to
get from the database. Once the user enters the search string and clicks OK,
I want to change the SQL "Where" statement to what the user entered in the
dialog box. In other words, my SQL Select statement is WHERE (MIP.`Employee
SEID`='DK8FB'), and based on what the user entered into the dialog box, I
want to change it to WHERE (MIP.`Employee SEID`='HF5JK'), if HF5JK was what
the user entered. Is it possible to change SQL statements based on user input
from a dialog box? If so, how would I do that all in the same macro? Any
assistance would be appreciated.

Thanks for the response. I have already explored what you detailed. Yes, I
can change the queries, I want other people to be able to change the queries
based on their input into a dialog box. I have no problems creating queries,
but other users in the organization do not have the same skill level, and it
would be asking too much to ask them to create their own queries to get the
necessary data. It's possible that I myself don't understand queries enough
to know if I can distribute them to others for use. I am looking for a way to
minimize data extracted from the database to specific data that the user
requests, based on the input of a text box that automatically opens in the
Excel spreadsheet. I hate to automatically refresh the entire database table
into Excel because of the 66,000 limit on rows in Excel worksheets, only to
have to delete the rows that the user will not need. ANyway, thanks again.

"FSt1" wrote:

hi
forget your form or dialog box where the user enter a 5 charcter string. if
i understand you correctly, you have run the MSQ(microsoft query) and now you
want to filter it by running it again?!?!?! based on user input after the
fact?!?!?
do a parameter query. how?
right click in the MSQ. click edit query. click through the wizard to the
last screen. click "view data or edit query in Microsoft query".
when the MSQ dialog box comes up... on the menu bar, click criteriaadd
criteria.
this is where you "change your "where" clause". each time the MSQ is run,
the user will be prompted for the query parameters and will return data based
on the parametes(criteria) the user input.

or did i totally misunderstand what your are trying to do?!?!?
regards
FSt1

"Pat" wrote:

I have an Access database (MIP_MCC_Review.mdb) with two tables (MCC and MIP)
that contain data submitted by users using forms of the same names. I have a
query that I built in Excel Microsoft Query that will get specific data
(rows) from the tables in Access and import that data into Excel in a new
worksheet of a workbook that I use (MCC_MIP_Review.xls) for calculations on
the data. I wrote a macro that will create the new worksheet (MIP_Data) and
that works fine. What I want to do now is to modify the same macro to launch
a form I created (dialog box) with one text box and an OK button so the user
can enter a five character string that will determine what specific rows to
get from the database. Once the user enters the search string and clicks OK,
I want to change the SQL "Where" statement to what the user entered in the
dialog box. In other words, my SQL Select statement is WHERE (MIP.`Employee
SEID`='DK8FB'), and based on what the user entered into the dialog box, I
want to change it to WHERE (MIP.`Employee SEID`='HF5JK'), if HF5JK was what
the user entered. Is it possible to change SQL statements based on user input
from a dialog box? If so, how would I do that all in the same macro? Any
assistance would be appreciated.