One form tro review many tables

[Guest]

Hi!
I have a DB with bck tables with same fields.
How is possible, through one combobox, to choose where table if must base
one form to review the data in this form?
Thanks in advance.
an

 

[Graham Mandeno]

Hi an

The data displayed in a form is defined by the RecordSource property of the
form. You can change the RecordSource in code whenever you like and new
data will be displayed (provided the new recordsource has the same field
names.

I suggest you make a table that lists the candidate tables and a description
of the data (two fields). Use this table as the RowSource of your combo
box. Then, in the AfterUpdate event of the combo box, set the form's
RecordSource, like this:

Me.RecordSource = Me!cboSelectTable

Or, perhaps you might like to specify some more options in a SQL statement:

Me.RecordSource = "Select * from [" & Me!cboSelectTable _
& "] order by CustomerName;"

 

[Guest]

Hi An,

I was just getting ready to post a suggestion similar to Graham's
suggestion, along with an alternative. If you want to easily compare the data
from one table to another, you could also create a Union query with an
aliased field that indicates which table the data comes from. Something like
this:

Select *, "Table 1" AS [SourceTable]
FROM [Table 1]
UNION
Select *, "Table 2" AS [SourceTable]
FROM [Table 2]
UNION
Select *, "Table 3" AS [SourceTable]
FROM [Table 3]
ORDER BY [Field 1], [Field 2]

where [Table 1], [Table 2], [Table 3], [Field 1] and [Field 2] are valid
names of tables and fields. You could then set the default view of the form
to Datasheet. This would aid in the ability to do a side-by-side comparison
of the data.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi an

The data displayed in a form is defined by the RecordSource property of the
form. You can change the RecordSource in code whenever you like and new
data will be displayed (provided the new recordsource has the same field
names.

I suggest you make a table that lists the candidate tables and a description
of the data (two fields). Use this table as the RowSource of your combo
box. Then, in the AfterUpdate event of the combo box, set the form's
RecordSource, like this:

Me.RecordSource = Me!cboSelectTable

Or, perhaps you might like to specify some more options in a SQL statement:

Me.RecordSource = "Select * from [" & Me!cboSelectTable _
& "] order by CustomerName;"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi!
I have a DB with bck tables with same fields.
How is possible, through one combobox, to choose where table if must base
one form to review the data in this form?
Thanks in advance.
an

 

[Guest]

GM,

Thank you for reply.
Work fine with first solution.

Now, I have another difficult:
How to write automatically the tables names inside new created table
T_BckListTables
(The array names are generated automatically by another database and are of
the type BckDay 19-11-2006 13:56:33)

Thanks more one time.
an

Hi an

The data displayed in a form is defined by the RecordSource property of the
form. You can change the RecordSource in code whenever you like and new
data will be displayed (provided the new recordsource has the same field
names.

I suggest you make a table that lists the candidate tables and a description
of the data (two fields). Use this table as the RowSource of your combo
box. Then, in the AfterUpdate event of the combo box, set the form's
RecordSource, like this:

Me.RecordSource = Me!cboSelectTable

Or, perhaps you might like to specify some more options in a SQL statement:

Me.RecordSource = "Select * from [" & Me!cboSelectTable _
& "] order by CustomerName;"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi!
I have a DB with bck tables with same fields.
How is possible, through one combobox, to choose where table if must base
one form to review the data in this form?
Thanks in advance.
an

 

[Graham Mandeno]

I didn't realise your list of tables was so dynamic. Forget about the table
of tables idea and use the following SQL statement as the RowSource for your
combobox:

Select [Name] from MSysObjects where [Type]=1 and [Name] like "BckDay *";

If the BckDay tables are linked from another database, change [Type]=1 to
[Type]=6.

Note that this uses an undocumented system table, and so it is, strictly
speaking, unsupported. However, it will work in any version of Access that
has been released so far
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

GM,

Thank you for reply.
Work fine with first solution.

Now, I have another difficult:
How to write automatically the tables names inside new created table
T_BckListTables
(The array names are generated automatically by another database and are
of
the type BckDay 19-11-2006 13:56:33)

Thanks more one time.
an

Hi an

The data displayed in a form is defined by the RecordSource property of
the
form. You can change the RecordSource in code whenever you like and new
data will be displayed (provided the new recordsource has the same field
names.

I suggest you make a table that lists the candidate tables and a
description
of the data (two fields). Use this table as the RowSource of your combo
box. Then, in the AfterUpdate event of the combo box, set the form's
RecordSource, like this:

Me.RecordSource = Me!cboSelectTable

Or, perhaps you might like to specify some more options in a SQL
statement:

Me.RecordSource = "Select * from [" & Me!cboSelectTable _
& "] order by CustomerName;"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi!
I have a DB with bck tables with same fields.
How is possible, through one combobox, to choose where table if must
base
one form to review the data in this form?
Thanks in advance.
an

 

[Guest]

TW,
Thanks too.

For my problem, the 1st GM solution, work fine.
an

Hi An,

I was just getting ready to post a suggestion similar to Graham's
suggestion, along with an alternative. If you want to easily compare the data
from one table to another, you could also create a Union query with an
aliased field that indicates which table the data comes from. Something like
this:

Select *, "Table 1" AS [SourceTable]
FROM [Table 1]
UNION
Select *, "Table 2" AS [SourceTable]
FROM [Table 2]
UNION
Select *, "Table 3" AS [SourceTable]
FROM [Table 3]
ORDER BY [Field 1], [Field 2]

where [Table 1], [Table 2], [Table 3], [Field 1] and [Field 2] are valid
names of tables and fields. You could then set the default view of the form
to Datasheet. This would aid in the ability to do a side-by-side comparison
of the data.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi an

The data displayed in a form is defined by the RecordSource property of the
form. You can change the RecordSource in code whenever you like and new
data will be displayed (provided the new recordsource has the same field
names.

I suggest you make a table that lists the candidate tables and a description
of the data (two fields). Use this table as the RowSource of your combo
box. Then, in the AfterUpdate event of the combo box, set the form's
RecordSource, like this:

Me.RecordSource = Me!cboSelectTable

Or, perhaps you might like to specify some more options in a SQL statement:

Me.RecordSource = "Select * from [" & Me!cboSelectTable _
& "] order by CustomerName;"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi!
I have a DB with bck tables with same fields.
How is possible, through one combobox, to choose where table if must base
one form to review the data in this form?
Thanks in advance.
an

 

[Guest]

Ooops!

With this solution:
1 - The query work fine
2 - The combo show all tables too
3 - When we click in combo to choose table, show us:
....error 94
Invalid use for Null

:-(
an

I didn't realise your list of tables was so dynamic. Forget about the table
of tables idea and use the following SQL statement as the RowSource for your
combobox:

Select [Name] from MSysObjects where [Type]=1 and [Name] like "BckDay *";

If the BckDay tables are linked from another database, change [Type]=1 to
[Type]=6.

Note that this uses an undocumented system table, and so it is, strictly
speaking, unsupported. However, it will work in any version of Access that
has been released so far
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

GM,

Thank you for reply.
Work fine with first solution.

Now, I have another difficult:
How to write automatically the tables names inside new created table
T_BckListTables
(The array names are generated automatically by another database and are
of
the type BckDay 19-11-2006 13:56:33)

Thanks more one time.
an

Hi an

The data displayed in a form is defined by the RecordSource property of
the
form. You can change the RecordSource in code whenever you like and new
data will be displayed (provided the new recordsource has the same field
names.

I suggest you make a table that lists the candidate tables and a
description
of the data (two fields). Use this table as the RowSource of your combo
box. Then, in the AfterUpdate event of the combo box, set the form's
RecordSource, like this:

Me.RecordSource = Me!cboSelectTable

Or, perhaps you might like to specify some more options in a SQL
statement:

Me.RecordSource = "Select * from [" & Me!cboSelectTable _
& "] order by CustomerName;"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi!
I have a DB with bck tables with same fields.
How is possible, through one combobox, to choose where table if must
base
one form to review the data in this form?
Thanks in advance.
an

 

[Guest]

Sorry!!!

Changed Bound Column to 1, in combo, work fine.

Thank you very much for your help!
an

I didn't realise your list of tables was so dynamic. Forget about the table
of tables idea and use the following SQL statement as the RowSource for your
combobox:

Select [Name] from MSysObjects where [Type]=1 and [Name] like "BckDay *";

If the BckDay tables are linked from another database, change [Type]=1 to
[Type]=6.

Note that this uses an undocumented system table, and so it is, strictly
speaking, unsupported. However, it will work in any version of Access that
has been released so far
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

GM,

Thank you for reply.
Work fine with first solution.

Now, I have another difficult:
How to write automatically the tables names inside new created table
T_BckListTables
(The array names are generated automatically by another database and are
of
the type BckDay 19-11-2006 13:56:33)

Thanks more one time.
an

Hi an

The data displayed in a form is defined by the RecordSource property of
the
form. You can change the RecordSource in code whenever you like and new
data will be displayed (provided the new recordsource has the same field
names.

I suggest you make a table that lists the candidate tables and a
description
of the data (two fields). Use this table as the RowSource of your combo
box. Then, in the AfterUpdate event of the combo box, set the form's
RecordSource, like this:

Me.RecordSource = Me!cboSelectTable

Or, perhaps you might like to specify some more options in a SQL
statement:

Me.RecordSource = "Select * from [" & Me!cboSelectTable _
& "] order by CustomerName;"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi!
I have a DB with bck tables with same fields.
How is possible, through one combobox, to choose where table if must
base
one form to review the data in this form?
Thanks in advance.
an

 

[Graham Mandeno]

Hi An

I'm glad you got it working. You should also change ColumnCount from 2 to
1.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Sorry!!!

Changed Bound Column to 1, in combo, work fine.

Thank you very much for your help!
an

I didn't realise your list of tables was so dynamic. Forget about the
table
of tables idea and use the following SQL statement as the RowSource for
your
combobox:

Select [Name] from MSysObjects where [Type]=1 and [Name] like "BckDay *";

If the BckDay tables are linked from another database, change [Type]=1 to
[Type]=6.

Note that this uses an undocumented system table, and so it is, strictly
speaking, unsupported. However, it will work in any version of Access
that
has been released so far
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

GM,

Thank you for reply.
Work fine with first solution.

Now, I have another difficult:
How to write automatically the tables names inside new created table
T_BckListTables
(The array names are generated automatically by another database and
are
of
the type BckDay 19-11-2006 13:56:33)

Thanks more one time.
an

:

Hi an

The data displayed in a form is defined by the RecordSource property
of
the
form. You can change the RecordSource in code whenever you like and
new
data will be displayed (provided the new recordsource has the same
field
names.

I suggest you make a table that lists the candidate tables and a
description
of the data (two fields). Use this table as the RowSource of your
combo
box. Then, in the AfterUpdate event of the combo box, set the form's
RecordSource, like this:

Me.RecordSource = Me!cboSelectTable

Or, perhaps you might like to specify some more options in a SQL
statement:

Me.RecordSource = "Select * from [" & Me!cboSelectTable _
& "] order by CustomerName;"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi!
I have a DB with bck tables with same fields.
How is possible, through one combobox, to choose where table if must
base
one form to review the data in this form?
Thanks in advance.
an

 

[Guest]

Ok.

Good Luck too,
and thank you very much more one time!
an

Hi An

I'm glad you got it working. You should also change ColumnCount from 2 to
1.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Sorry!!!

Changed Bound Column to 1, in combo, work fine.

Thank you very much for your help!
an

I didn't realise your list of tables was so dynamic. Forget about the
table
of tables idea and use the following SQL statement as the RowSource for
your
combobox:

Select [Name] from MSysObjects where [Type]=1 and [Name] like "BckDay *";

If the BckDay tables are linked from another database, change [Type]=1 to
[Type]=6.

Note that this uses an undocumented system table, and so it is, strictly
speaking, unsupported. However, it will work in any version of Access
that
has been released so far
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

GM,

Thank you for reply.
Work fine with first solution.

Now, I have another difficult:
How to write automatically the tables names inside new created table
T_BckListTables
(The array names are generated automatically by another database and
are
of
the type BckDay 19-11-2006 13:56:33)

Thanks more one time.
an

:

Hi an

The data displayed in a form is defined by the RecordSource property
of
the
form. You can change the RecordSource in code whenever you like and
new
data will be displayed (provided the new recordsource has the same
field
names.

I suggest you make a table that lists the candidate tables and a
description
of the data (two fields). Use this table as the RowSource of your
combo
box. Then, in the AfterUpdate event of the combo box, set the form's
RecordSource, like this:

Me.RecordSource = Me!cboSelectTable

Or, perhaps you might like to specify some more options in a SQL
statement:

Me.RecordSource = "Select * from [" & Me!cboSelectTable _
& "] order by CustomerName;"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi!
I have a DB with bck tables with same fields.
How is possible, through one combobox, to choose where table if must
base
one form to review the data in this form?
Thanks in advance.
an