If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
combo box to select a table
I have written some VBA code in a module which works without fault. However,
I want to create a combo box on a form to select a table (from the available tables) so that the name of the table can be passed to this VBA code, but I cannot remember how to create this combo box. Any ideas anyone? -- Life is like a toilet roll - the closer you get to the end, the faster it gets! |
#2
|
|||
|
|||
combo box to select a table
On Fri, 12 Feb 2010 05:10:04 -0800, Simka
wrote: You can either select from the system table MSysObjects, or write some VBA code to populate the listbox from the Tabledefs collection. -Tom. Microsoft Access MVP I have written some VBA code in a module which works without fault. However, I want to create a combo box on a form to select a table (from the available tables) so that the name of the table can be passed to this VBA code, but I cannot remember how to create this combo box. Any ideas anyone? |
#3
|
|||
|
|||
combo box to select a table
I do believe that the fllowing will work
SELECT MsysObjects.Name AS[List Of Tables]FROM MsysObjectsWHERE (((MsysObjects.Name) Not Like "~*" And (MsysObjects.Name) Not Like "MSys*") AND ((MsysObjects.Type)=1)) ORDER BY MsysObjects.Name; You need only change the value of the (MsysObjects.Type)=1 part of the query expression to change what listing is returned. Below are the various value that can be used to return the various objects available in Access Object Type Value Tables 1 Queries 5 Forms -32768 Reports -32764 Macros -32766 Modules -32761 -- Hope this helps, Daniel Pineault http://www.cardaconsultants.com/ For Access Tips and Examples: http://www.devhut.net Please rate this post using the vote buttons if it was helpful. "Simka" wrote: I have written some VBA code in a module which works without fault. However, I want to create a combo box on a form to select a table (from the available tables) so that the name of the table can be passed to this VBA code, but I cannot remember how to create this combo box. Any ideas anyone? -- Life is like a toilet roll - the closer you get to the end, the faster it gets! |
#4
|
|||
|
|||
combo box to select a table
Out of curiousity, what will you (your application) do with it, once you've
selected a table? I ask because there may be other ways to accomplish your end-goal, but I can't tell what that is... Regards Jeff Boyce Micrsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "alex" wrote in message ... Recordset too large? "Simka" wrote in message ... I have written some VBA code in a module which works without fault. However, I want to create a combo box on a form to select a table (from the available tables) so that the name of the table can be passed to this VBA code, but I cannot remember how to create this combo box. Any ideas anyone? -- Life is like a toilet roll - the closer you get to the end, the faster it gets! |
#5
|
|||
|
|||
combo box to select a table
Actually, type 4 and 6 are also tables. 6 is a linked table, and 4 is a
linked table that uses ODBC. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Daniel Pineault" wrote in message ... I do believe that the fllowing will work SELECT MsysObjects.Name AS[List Of Tables]FROM MsysObjectsWHERE (((MsysObjects.Name) Not Like "~*" And (MsysObjects.Name) Not Like "MSys*") AND ((MsysObjects.Type)=1)) ORDER BY MsysObjects.Name; You need only change the value of the (MsysObjects.Type)=1 part of the query expression to change what listing is returned. Below are the various value that can be used to return the various objects available in Access Object Type Value Tables 1 Queries 5 Forms -32768 Reports -32764 Macros -32766 Modules -32761 -- Hope this helps, Daniel Pineault http://www.cardaconsultants.com/ For Access Tips and Examples: http://www.devhut.net Please rate this post using the vote buttons if it was helpful. "Simka" wrote: I have written some VBA code in a module which works without fault. However, I want to create a combo box on a form to select a table (from the available tables) so that the name of the table can be passed to this VBA code, but I cannot remember how to create this combo box. Any ideas anyone? -- Life is like a toilet roll - the closer you get to the end, the faster it gets! |
#6
|
|||
|
|||
combo box to select a table
Hello Jeff,
The VBA code that I have written are several lines of SQL that change many thousands of lines of records which are based on certain criteria within other fields within the tables (I keep a table for each month data+delete them when no longer needed) and works similar to several update queries. Each month I receive the data and hold it in tables. Now that I recall how to select tables from a combo box (with the reminder from Danial Pineault) I have just created a simple form with a this combo box and button to run the VBA code and change/update the data. Glad to say that now it changes the data within a few seconds! -- Life is like a toilet roll - the closer you get to the end, the faster it gets! "Jeff Boyce" wrote: Out of curiousity, what will you (your application) do with it, once you've selected a table? I ask because there may be other ways to accomplish your end-goal, but I can't tell what that is... Regards Jeff Boyce Micrsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "alex" wrote in message ... Recordset too large? "Simka" wrote in message ... I have written some VBA code in a module which works without fault. However, I want to create a combo box on a form to select a table (from the available tables) so that the name of the table can be passed to this VBA code, but I cannot remember how to create this combo box. Any ideas anyone? -- Life is like a toilet roll - the closer you get to the end, the faster it gets! . |
#7
|
|||
|
|||
combo box to select a table
If your database has a separate table for each month, you don't have a
relational database, you have a ... spreadsheet! If you use that approach, and try feeding Access 'sheet data, you and Access will have to work very hard to overcome the fact that Access is optimized for relational data, not 'sheet data... (oh wait, that's what you're trying to do!) If you have, say, a set of 10 fields that you receive data for each month, you can create a table in Access that has 11 fields ... 10 for your data and one for the DateReceived. That way, you can easily use Access' relationally-oriented features & functions to total by time period, compare previous and current time periods, etc. ... and all from a single table ... .... and all without having to check for multiple tablenames! Or have I misunderstood your situation...? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Simka" wrote in message ... Hello Jeff, The VBA code that I have written are several lines of SQL that change many thousands of lines of records which are based on certain criteria within other fields within the tables (I keep a table for each month data+delete them when no longer needed) and works similar to several update queries. Each month I receive the data and hold it in tables. Now that I recall how to select tables from a combo box (with the reminder from Danial Pineault) I have just created a simple form with a this combo box and button to run the VBA code and change/update the data. Glad to say that now it changes the data within a few seconds! -- Life is like a toilet roll - the closer you get to the end, the faster it gets! "Jeff Boyce" wrote: Out of curiousity, what will you (your application) do with it, once you've selected a table? I ask because there may be other ways to accomplish your end-goal, but I can't tell what that is... Regards Jeff Boyce Micrsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "alex" wrote in message ... Recordset too large? "Simka" wrote in message ... I have written some VBA code in a module which works without fault. However, I want to create a combo box on a form to select a table (from the available tables) so that the name of the table can be passed to this VBA code, but I cannot remember how to create this combo box. Any ideas anyone? -- Life is like a toilet roll - the closer you get to the end, the faster it gets! . |
#8
|
|||
|
|||
combo box to select a table
Yes, I think you have misunderstood the situation, and is more complicated
than what I have written below, but I greatly appreciate your input. :-) If you have any other/alternate suggestions I would be pleased to hear them. It's a little more complicated than what I have previously written. The table consist of about 25-30 fields and thousands of records. People used to alter the data using Excel, but that took forever. I suggested importing the data into Access and then use update queries which effectively did the same thing but much quicker. Then I wrote the code and now the data is changed by purely selecting the relevant table (using the new combobox option) and pressing the GO button. The data produced is exactly how it is wanted and saves a lot of time. -- Life is like a toilet roll - the closer you get to the end, the faster it gets! "Jeff Boyce" wrote: If your database has a separate table for each month, you don't have a relational database, you have a ... spreadsheet! If you use that approach, and try feeding Access 'sheet data, you and Access will have to work very hard to overcome the fact that Access is optimized for relational data, not 'sheet data... (oh wait, that's what you're trying to do!) If you have, say, a set of 10 fields that you receive data for each month, you can create a table in Access that has 11 fields ... 10 for your data and one for the DateReceived. That way, you can easily use Access' relationally-oriented features & functions to total by time period, compare previous and current time periods, etc. ... and all from a single table ... .... and all without having to check for multiple tablenames! Or have I misunderstood your situation...? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Simka" wrote in message ... Hello Jeff, The VBA code that I have written are several lines of SQL that change many thousands of lines of records which are based on certain criteria within other fields within the tables (I keep a table for each month data+delete them when no longer needed) and works similar to several update queries. Each month I receive the data and hold it in tables. Now that I recall how to select tables from a combo box (with the reminder from Danial Pineault) I have just created a simple form with a this combo box and button to run the VBA code and change/update the data. Glad to say that now it changes the data within a few seconds! -- Life is like a toilet roll - the closer you get to the end, the faster it gets! "Jeff Boyce" wrote: Out of curiousity, what will you (your application) do with it, once you've selected a table? I ask because there may be other ways to accomplish your end-goal, but I can't tell what that is... Regards Jeff Boyce Micrsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "alex" wrote in message ... Recordset too large? "Simka" wrote in message ... I have written some VBA code in a module which works without fault. However, I want to create a combo box on a form to select a table (from the available tables) so that the name of the table can be passed to this VBA code, but I cannot remember how to create this combo box. Any ideas anyone? -- Life is like a toilet roll - the closer you get to the end, the faster it gets! . . |
#9
|
|||
|
|||
combo box to select a table
Douglas,
Thank you for that info, I was not aware of that. -- Daniel Pineault http://www.cardaconsultants.com/ For Access Tips and Examples: http://www.devhut.net Please rate this post using the vote buttons if it was helpful. "Douglas J. Steele" wrote: Actually, type 4 and 6 are also tables. 6 is a linked table, and 4 is a linked table that uses ODBC. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Daniel Pineault" wrote in message ... I do believe that the fllowing will work SELECT MsysObjects.Name AS[List Of Tables]FROM MsysObjectsWHERE (((MsysObjects.Name) Not Like "~*" And (MsysObjects.Name) Not Like "MSys*") AND ((MsysObjects.Type)=1)) ORDER BY MsysObjects.Name; You need only change the value of the (MsysObjects.Type)=1 part of the query expression to change what listing is returned. Below are the various value that can be used to return the various objects available in Access Object Type Value Tables 1 Queries 5 Forms -32768 Reports -32764 Macros -32766 Modules -32761 -- Hope this helps, Daniel Pineault http://www.cardaconsultants.com/ For Access Tips and Examples: http://www.devhut.net Please rate this post using the vote buttons if it was helpful. "Simka" wrote: I have written some VBA code in a module which works without fault. However, I want to create a combo box on a form to select a table (from the available tables) so that the name of the table can be passed to this VBA code, but I cannot remember how to create this combo box. Any ideas anyone? -- Life is like a toilet roll - the closer you get to the end, the faster it gets! . |
#10
|
|||
|
|||
combo box to select a table
I only mentioned that possibility because a lot of folks who start with
Excel and migrate their data to Access never realize that they are hamstringing Access. If you feed Access 'sheet data (for example, data imported directly from Excel), both you and Access have to work overtime. To get the best use of Access, your data needs to be well-normalized. Consider posting a description of your current table (with 25-30 fields) to get more feedback from folks here in the newsgroups. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Simka" wrote in message ... Yes, I think you have misunderstood the situation, and is more complicated than what I have written below, but I greatly appreciate your input. :-) If you have any other/alternate suggestions I would be pleased to hear them. It's a little more complicated than what I have previously written. The table consist of about 25-30 fields and thousands of records. People used to alter the data using Excel, but that took forever. I suggested importing the data into Access and then use update queries which effectively did the same thing but much quicker. Then I wrote the code and now the data is changed by purely selecting the relevant table (using the new combobox option) and pressing the GO button. The data produced is exactly how it is wanted and saves a lot of time. -- Life is like a toilet roll - the closer you get to the end, the faster it gets! "Jeff Boyce" wrote: If your database has a separate table for each month, you don't have a relational database, you have a ... spreadsheet! If you use that approach, and try feeding Access 'sheet data, you and Access will have to work very hard to overcome the fact that Access is optimized for relational data, not 'sheet data... (oh wait, that's what you're trying to do!) If you have, say, a set of 10 fields that you receive data for each month, you can create a table in Access that has 11 fields ... 10 for your data and one for the DateReceived. That way, you can easily use Access' relationally-oriented features & functions to total by time period, compare previous and current time periods, etc. ... and all from a single table ... .... and all without having to check for multiple tablenames! Or have I misunderstood your situation...? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Simka" wrote in message ... Hello Jeff, The VBA code that I have written are several lines of SQL that change many thousands of lines of records which are based on certain criteria within other fields within the tables (I keep a table for each month data+delete them when no longer needed) and works similar to several update queries. Each month I receive the data and hold it in tables. Now that I recall how to select tables from a combo box (with the reminder from Danial Pineault) I have just created a simple form with a this combo box and button to run the VBA code and change/update the data. Glad to say that now it changes the data within a few seconds! -- Life is like a toilet roll - the closer you get to the end, the faster it gets! "Jeff Boyce" wrote: Out of curiousity, what will you (your application) do with it, once you've selected a table? I ask because there may be other ways to accomplish your end-goal, but I can't tell what that is... Regards Jeff Boyce Micrsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "alex" wrote in message ... Recordset too large? "Simka" wrote in message ... I have written some VBA code in a module which works without fault. However, I want to create a combo box on a form to select a table (from the available tables) so that the name of the table can be passed to this VBA code, but I cannot remember how to create this combo box. Any ideas anyone? -- Life is like a toilet roll - the closer you get to the end, the faster it gets! . . |
Thread Tools | |
Display Modes | |
|
|