How to list all tables of a database in a combobox.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to make a database of all articles in a welding journal. The
differant tables are each a differant catagory (Stainless Steel, Mig welding,
Welding Safety, etc..). The fields of each table is Date/Page, Title,
Synopsis, KeyWord 1, KeyWord 2. I want to make an article entry form that
will have a combobox that will list all the catagory tables so that you may
select one catagory and the article entry form will show the differant fields
(Date/Page, Title...etc.) as blank text boxes so that you may enter the data
for each welding article for a later key word search to find differant
articles that has the info you are looking for. How may you compile a
combobox that will list all the differant catagories to accoplish the article
entry.
 
If I understand what you are saying, when a new catagory is added, you
will add a new table? If this is true, why don't you just have one
table with the catagory as a field in that table.

You could have one table for articles and another for catagories. The
catagory table could be put into a combo box on the form that is used to
enter article rcords. By using the not in list event of the combo box,
you can add new catagories to the catagory table wheneven a new one is
needed.

This aproach will cut down on the number or queries and reports required.

Ron
 
Graham R Seach said:
Set the combo's RowSource as follows:
SELECT [Name] FROM MSysObjects WHERE [Type] = -32768

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


SkyWelder said:
I am trying to make a database of all articles in a welding journal. The
differant tables are each a differant catagory (Stainless Steel, Mig
welding,
Welding Safety, etc..). The fields of each table is Date/Page, Title,
Synopsis, KeyWord 1, KeyWord 2. I want to make an article entry form that
will have a combobox that will list all the catagory tables so that you
may
select one catagory and the article entry form will show the differant
fields
(Date/Page, Title...etc.) as blank text boxes so that you may enter the
data
for each welding article for a later key word search to find differant
articles that has the info you are looking for. How may you compile a
combobox that will list all the differant catagories to accoplish the
article
entry.
Click to expand...
Click to expand...
 
Thank you for your post. I noticed that your link pointed to the Microsoft
Access 2003 VBA programmer’s reference book, which you shared in writing,
which aims at Access 2003. I neglected to say that my copy is Access 2002
(XP). Does this make a difference or not? Also, the line of code you
proscribe for the combo’s Row Source. Where is this to be written? In the
Row Source text box under the combo box properties popup menu? What do you
think of this other idea of having a single table with one of the columns
being for categories? Any help you give is appreciated.

Graham R Seach said:
Set the combo's RowSource as follows:
SELECT [Name] FROM MSysObjects WHERE [Type] = -32768

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


SkyWelder said:
I am trying to make a database of all articles in a welding journal. The
differant tables are each a differant catagory (Stainless Steel, Mig
welding,
Welding Safety, etc..). The fields of each table is Date/Page, Title,
Synopsis, KeyWord 1, KeyWord 2. I want to make an article entry form that
will have a combobox that will list all the catagory tables so that you
may
select one catagory and the article entry form will show the differant
fields
(Date/Page, Title...etc.) as blank text boxes so that you may enter the
data
for each welding article for a later key word search to find differant
articles that has the info you are looking for. How may you compile a
combobox that will list all the differant catagories to accoplish the
article
entry.
Click to expand...
Click to expand...
 
Thank you for your response. I neglected to say that my copy is Access 2002
(XP). I could have 2 tables, one for categories and the other for articles
data, but the idea of one table with a column for categories is attractive.
It sounds simpler. Would the “not in list event†setting work for a single
table that has a column for categories and where would you set that
parameter? Thank you very much for your support.
 
Graham's given you an SQL statement that would be typed into the Row Source
property.

As to your idea of having a separate table for each category, I don't think
it's a particular good one.

In essence, you're storing data in the name of the field. Instead, I think
you should have all of the articles in one table, and have a table of
categories. The relationship between Article and Category is many-to-many:
one article may have several categories that apply to it, and each category
will have several articles associated with it. That means you need to add a
3rd "intersection table" to resolve that many-to-many.

You should do the same with keywords too. Having fields named KeyWord1,
KeyWord2 etc. is a sure sign that your database hasn't been properly
normalized.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



SkyWelder said:
Thank you for your post. I noticed that your link pointed to the
Microsoft
Access 2003 VBA programmer's reference book, which you shared in writing,
which aims at Access 2003. I neglected to say that my copy is Access 2002
(XP). Does this make a difference or not? Also, the line of code you
proscribe for the combo's Row Source. Where is this to be written? In
the
Row Source text box under the combo box properties popup menu? What do
you
think of this other idea of having a single table with one of the columns
being for categories? Any help you give is appreciated.

Graham R Seach said:
Set the combo's RowSource as follows:
SELECT [Name] FROM MSysObjects WHERE [Type] = -32768

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


SkyWelder said:
I am trying to make a database of all articles in a welding journal. The
differant tables are each a differant catagory (Stainless Steel, Mig
welding,
Welding Safety, etc..). The fields of each table is Date/Page, Title,
Synopsis, KeyWord 1, KeyWord 2. I want to make an article entry form
that
will have a combobox that will list all the catagory tables so that you
may
select one catagory and the article entry form will show the differant
fields
(Date/Page, Title...etc.) as blank text boxes so that you may enter the
data
for each welding article for a later key word search to find differant
articles that has the info you are looking for. How may you compile a
combobox that will list all the differant catagories to accoplish the
article
entry.
Click to expand...
Click to expand...
Click to expand...
 
SkyWelder said:
Thank you for your response. I neglected to say that my copy is Access 2002
(XP). I could have 2 tables, one for categories and the other for articles
data, but the idea of one table with a column for categories is attractive.
It sounds simpler. Would the “not in list event†setting work for a single
table that has a column for categories and where would you set that
parameter? Thank you very much for your support.
Yes


:
Click to expand...
 
Back
Top