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!

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?

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!

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!

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!

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!


.

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!


.

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!


.



.

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!

.

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!


.



.