combo box to select a table

  • Thread starter Thread starter Simka
  • Start date Start date
S

Simka

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?
 
On Fri, 12 Feb 2010 05:10:04 -0800, Simka

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 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.
 
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.

Recordset too large?
 
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

(no e-mails, please!)



Daniel Pineault said:
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 said:
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?
Click to expand...
Click to expand...
 
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!
 
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.
 
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.
 
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 said:
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

(no e-mails, please!)



Daniel Pineault said:
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 said:
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?
Click to expand...
Click to expand...

.
Click to expand...
 
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.
 
Back
Top