VBA, Passing multiple parameters to Access query using Form List Box

Passing multiple parameters to Access query using Form List Box

Author	Message
sambay #1 / 6	Passing multiple parameters to Access query using Form List Box Hi, I am new to VBA and have 2 questions. 1)I am trying to run a query from a Form List box selection. The listbox has Company names from the Underlying Table. I need multiple selection capability with "OR" condition as the names differ (eg. XYZ Corp., XYZ Corporation, XYZ Corp, The XYZ Corp, etc. are listed separately for legal reasons, but need to be accessed together for query purposes) I can use the Extended selection property, but can't get the List box values to transfer to the search criteria. 2) Subsequently, I would like to transfer the output to Excel and perform some analysis. I am thinking of using the TransferSpreadsheet method (hope it works). The question is, How do I run code for Excel manipulation from within Access modules? Any help in this regard will be deeply appreciated. Thanks, Sameer
Tue, 22 Mar 2005 09:47:15 GMT

John Vinso #2 / 6	Passing multiple parameters to Access query using Form List Box Quote: >Hi, >I am new to VBA and have 2 questions. >1)I am trying to run a query from a Form List box >selection. >The listbox has Company names from the Underlying Table. I >need multiple selection capability with "OR" condition as >the names differ (eg. XYZ Corp., XYZ Corporation, XYZ >Corp, The XYZ Corp, etc. are listed separately for legal >reasons, but need to be accessed together for query >purposes) >I can use the Extended selection property, but can't get >the List box values to transfer to the search criteria. This won't work directly in a SQL query. A multiselect (whether extended or simple) listbox has no value. You'll need to write some VBA code to loop through the listbox's ItemsSelected collection building up an IN clause in SQL: ... WHERE CompanyName IN ("XYZ Corp.", "XYZ Corporation") ... I believe there's sample code to do this at http://www.lebans.com , search for Multiselect. Quote: >2) Subsequently, I would like to transfer the output to >Excel and perform some analysis. I am thinking of using >the TransferSpreadsheet method (hope it works). The >question is, How do I run code for Excel manipulation from >within Access modules? TransferSpreadsheet works, but you'll have to ask someone more knowledgable than me about Excel for the rest! John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public
Tue, 22 Mar 2005 13:35:03 GMT

CSmit #3 / 6	Passing multiple parameters to Access query using Form List Box Hi, Quote: >>> How do I run code for Excel manipulation from within Access modules? <<< If using an existing spreadsheet file, then you may want to consider writing your "manipulation" VBA code within the auto_open macro of your Excel file and then simply open that file from Access, which will trigger the auto_open macro. However, you would need to use Automation or DAO/ADO to transfer the data from Access to Excel, considering the TransferSpreadsheet method would overwrite an existing Excel file (and obviously it's embedded VBA code too). -- Calvin Smith http://www.CalvinSmithSoftware.com "Real-world Source Code Solutions" Code Newsletter - http://www.CalvinSmithSoftware.com/newsletter.htm VB/VBA Tools - http://www.CalvinSmithSoftware.com/AllComboDeals.htm Quote: > >Hi, > >I am new to VBA and have 2 questions. > >1)I am trying to run a query from a Form List box > >selection. > >The listbox has Company names from the Underlying Table. I > >need multiple selection capability with "OR" condition as > >the names differ (eg. XYZ Corp., XYZ Corporation, XYZ > >Corp, The XYZ Corp, etc. are listed separately for legal > >reasons, but need to be accessed together for query > >purposes) > >I can use the Extended selection property, but can't get > >the List box values to transfer to the search criteria. > This won't work directly in a SQL query. A multiselect (whether > extended or simple) listbox has no value. You'll need to write some > VBA code to loop through the listbox's ItemsSelected collection > building up an IN clause in SQL: > ... WHERE CompanyName IN ("XYZ Corp.", "XYZ Corporation") ... > I believe there's sample code to do this at http://www.lebans.com , > search for Multiselect. > >2) Subsequently, I would like to transfer the output to > >Excel and perform some analysis. I am thinking of using > >the TransferSpreadsheet method (hope it works). The > >question is, How do I run code for Excel manipulation from > >within Access modules? > TransferSpreadsheet works, but you'll have to ask someone more > knowledgable than me about Excel for the rest! > John W. Vinson[MVP] > Come for live chats every Tuesday and Thursday > http://go.compuserve.com/msdevapps?loc=us&access=public
Tue, 22 Mar 2005 15:53:40 GMT

sambay #4 / 6	Passing multiple parameters to Access query using Form List Box Quote: >-----Original Message----- >Hi, >>>> How do I run code for Excel manipulation from within Access modules? <<< >If using an existing spreadsheet file, then you may want to consider writing >your "manipulation" VBA code within the auto_open macro of your Excel file >and then simply open that file from Access, which will trigger the auto_open Quote: >macro. However, you would need to use Automation or DAO/ADO to transfer the Quote: >data from Access to Excel, considering the TransferSpreadsheet method would Quote: >overwrite an existing Excel file (and obviously it's embedded VBA code too). Quote: >-- >Calvin Smith >http://www.CalvinSmithSoftware.com >"Real-world Source Code Solutions" >Code Newsletter - http://www.CalvinSmithSoftware.com/newsletter.htm Quote: >VB/VBA Tools - http://www.CalvinSmithSoftware.com/AllComboDeals.htm Quote: in message >> On Thu, 3 Oct 2002 18:47:15 -0700, "sambaya" Quote: >> >Hi, >> >I am new to VBA and have 2 questions. >> >1)I am trying to run a query from a Form List box >> >selection. >> >The listbox has Company names from the Underlying Table. I >> >need multiple selection capability with "OR" condition as >> >the names differ (eg. XYZ Corp., XYZ Corporation, XYZ >> >Corp, The XYZ Corp, etc. are listed separately for legal >> >reasons, but need to be accessed together for query >> >purposes) >> >I can use the Extended selection property, but can't get >> >the List box values to transfer to the search criteria. >> This won't work directly in a SQL query. A multiselect (whether >> extended or simple) listbox has no value. You'll need to write some >> VBA code to loop through the listbox's ItemsSelected collection >> building up an IN clause in SQL: >> ... WHERE CompanyName IN ("XYZ Corp.", "XYZ Corporation") ... >> I believe there's sample code to do this at http://www.lebans.com , - Hide quoted text - - Show quoted text - Quote: >> search for Multiselect. >> >2) Subsequently, I would like to transfer the output to >> >Excel and perform some analysis. I am thinking of using >> >the TransferSpreadsheet method (hope it works). The >> >question is, How do I run code for Excel manipulation from >> >within Access modules? >> TransferSpreadsheet works, but you'll have to ask someone more >> knowledgable than me about Excel for the rest! >> John W. Vinson[MVP] >> Come for live chats every Tuesday and Thursday >> http://go.compuserve.com/msdevapps?loc=us&access=public >.
Tue, 22 Mar 2005 18:19:03 GMT

CSmit #5 / 6	Passing multiple parameters to Access query using Form List Box You're welcome! :-) -- Calvin Smith http://www.CalvinSmithSoftware.com "Real-world Source Code Solutions" Code Newsletter - http://www.CalvinSmithSoftware.com/newsletter.htm VB/VBA Tools - http://www.CalvinSmithSoftware.com/AllComboDeals.htm Quote: > >-----Original Message----- > >Hi, > >>>> How do I run code for Excel manipulation from within > Access modules? <<< > >If using an existing spreadsheet file, then you may want > to consider writing > >your "manipulation" VBA code within the auto_open macro > of your Excel file > >and then simply open that file from Access, which will > trigger the auto_open > >macro. However, you would need to use Automation or > DAO/ADO to transfer the > >data from Access to Excel, considering the > TransferSpreadsheet method would > >overwrite an existing Excel file (and obviously it's > embedded VBA code too). > >-- > >Calvin Smith > >http://www.CalvinSmithSoftware.com > >"Real-world Source Code Solutions" > >Code Newsletter - > http://www.CalvinSmithSoftware.com/newsletter.htm > >VB/VBA Tools - > http://www.CalvinSmithSoftware.com/AllComboDeals.htm > in message > >> On Thu, 3 Oct 2002 18:47:15 -0700, "sambaya" > >> >Hi, > >> >I am new to VBA and have 2 questions. > >> >1)I am trying to run a query from a Form List box > >> >selection. > >> >The listbox has Company names from the Underlying > Table. I > >> >need multiple selection capability with "OR" > condition as > >> >the names differ (eg. XYZ Corp., XYZ Corporation, XYZ > >> >Corp, The XYZ Corp, etc. are listed separately for > legal > >> >reasons, but need to be accessed together for query > >> >purposes) > >> >I can use the Extended selection property, but can't > get > >> >the List box values to transfer to the search criteria. > >> This won't work directly in a SQL query. A multiselect > (whether > >> extended or simple) listbox has no value. You'll need > to write some > >> VBA code to loop through the listbox's ItemsSelected > collection > >> building up an IN clause in SQL: > >> ... WHERE CompanyName IN ("XYZ Corp.", "XYZ > Corporation") ... > >> I believe there's sample code to do this at > http://www.lebans.com , > >> search for Multiselect. > >> >2) Subsequently, I would like to transfer the output to > >> >Excel and perform some analysis. I am thinking of using > >> >the TransferSpreadsheet method (hope it works). The > >> >question is, How do I run code for Excel manipulation > from > >> >within Access modules? > >> TransferSpreadsheet works, but you'll have to ask > someone more > >> knowledgable than me about Excel for the rest! > >> John W. Vinson[MVP] > >> Come for live chats every Tuesday and Thursday > >> http://go.compuserve.com/msdevapps?loc=us&access=public > >.
Wed, 23 Mar 2005 09:43:08 GMT

Page 1 of 1

[ 6 post ]