Hi,
Currently I'm developing an Access app, but I'm running into a problem.
Our database contains a lot of tables with the same format:
|=====| |=====| |=====|
|tbl1 | |tbl2 | |tbl3 |
|-----| |-----| |-----| etc
|name1| |name2| |name3|
|descr| |descr| |descr|
|=====| |=====| |=====|
Now I need to create an application that shows the contents of the
different tables, based on the table-name that can be selected in a
combo box. Currently I'm trying to do this by changing the recordsource
of the (sub)form I placed on the main form. This works fine for the
field "descr", because the name is the same in all tables. However, the
name of the first field is different in every database, and therefore
this does not work when changing the recordsource...
How can this problem be solved? Any help is greatly appreciated!
Cheers,
E.T. 8 7039
Erik, if you have several tables with the same structure, that *is* the
problem.
Create one table, with an extra field to distinguish whatever is the
difference between your existing tables. It is now *very* easy and efficient
to set criteria to draw just one set of records, or to fetch them all.
There is almost never a good reason to have two more more tables with almost
the same data structure.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Erik Thijs" <er*******************@khk.be> wrote in message
news:cm**********@ikaria.belnet.be... Currently I'm developing an Access app, but I'm running into a problem. Our database contains a lot of tables with the same format:
|=====| |=====| |=====| |tbl1 | |tbl2 | |tbl3 | |-----| |-----| |-----| etc |name1| |name2| |name3| |descr| |descr| |descr| |=====| |=====| |=====|
Now I need to create an application that shows the contents of the different tables, based on the table-name that can be selected in a combo box. Currently I'm trying to do this by changing the recordsource of the (sub)form I placed on the main form. This works fine for the field "descr", because the name is the same in all tables. However, the name of the first field is different in every database, and therefore this does not work when changing the recordsource...
How can this problem be solved? Any help is greatly appreciated!
Cheers, E.T.
Allen Browne wrote: Erik, if you have several tables with the same structure, that *is* the problem.
Create one table, with an extra field to distinguish whatever is the difference between your existing tables. It is now *very* easy and efficient to set criteria to draw just one set of records, or to fetch them all.
There is almost never a good reason to have two more more tables with almost the same data structure.
You might be right about that, but nevertheless I'd like to see a
solution to the problem I posted, if there is one...
tnx,
E.T.
Chuck Grimsby wrote: As Allen said, if you have any intelligence whatsoever, you'll use the Union query to create a new table and get rid of the (now useless) extra tables.
<sarcastic>Thank you for questioning my intelligence...</sarcastic>
I am merely the person that has to work with a database design that has
been setup by our database experts...
E.T.
Chuck Grimsby wrote: As Allen said, if you have any intelligence whatsoever, you'll use the Union query to create a new table and get rid of the (now useless) extra tables.
Aw, intelligence. Many problems like these take their origin in lacking
intelligence with management, not the actual designers or users of the
database.
Is it your own design, Erik? Then take the advice at heart, of course.
But if you have to live with MS-SQL included tables, for example, I can
understand the question completely.
Hmm. The field names vary in the different tables, so what you are asking to
do is to discover the field names appropriate to the table, and dynamically
assign the RecordSource of the form and the Control Source of the text
boxes.
That's doable. Save the form without any RecordSource. Access can crash if a
field suddenly disappears from the RecordSource or if it changes data type,
so it's best to leave the form unbound when its saved.
In the Open event procedure of the Form, assign the RecordSource to the
table of your choice. Then loop through the Fields collection of this
TableDef to discover the Name of the fields, and assign them to the
ControlSource of the various text boxes.
This code illustrates how to loop through the Fields of the TableDef: http://members.iinet.net.au/~allenbrowne/func-06.html
Hope that gives you what you need to do to get your result.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Erik Thijs" <er*******************@khk.be> wrote in message
news:cm**********@ikaria.belnet.be... Allen Browne wrote: Erik, if you have several tables with the same structure, that *is* the problem.
Create one table, with an extra field to distinguish whatever is the difference between your existing tables. It is now *very* easy and efficient to set criteria to draw just one set of records, or to fetch them all.
There is almost never a good reason to have two more more tables with almost the same data structure.
You might be right about that, but nevertheless I'd like to see a solution to the problem I posted, if there is one...
tnx, E.T.
Allen Browne wrote: Hmm. The field names vary in the different tables, so what you are asking to do is to discover the field names appropriate to the table, and dynamically assign the RecordSource of the form and the Control Source of the text boxes.
idd
That's doable. Save the form without any RecordSource. Access can crash if a field suddenly disappears from the RecordSource or if it changes data type, so it's best to leave the form unbound when its saved.
In the Open event procedure of the Form, assign the RecordSource to the table of your choice. Then loop through the Fields collection of this TableDef to discover the Name of the fields, and assign them to the ControlSource of the various text boxes.
The form showing the database info is actually a subform. The main form
contains a combobox where the user can select the database to be shown in
the subform.
Changing the RecordSource of the subform is not a problem (done that), but
I'm having troubles accessing the text boxes of the subform (this is
actually my first access vba project, and this might be a 'dumb' problem).
Is this possible or can it be done only in design time maybe? I've read
some posts saying you can access the text boxes of a subform like this:
Forms![mainform]![subform].Form!textbox1.property, but somehow this doesn't
seem to work for me...
Btw I'm using office 2003 with all service packs and patches applied (via
officeupdate).
This code illustrates how to loop through the Fields of the TableDef: http://members.iinet.net.au/~allenbrowne/func-06.html
Hope that gives you what you need to do to get your result.
This is definitely part of the solution, but now I'm still stuck at
accessing the text boxes...
tnx!
E.T.
The reference:
Forms![MyMainForm].[MySubformControl].Form.[MyTextbox]
is correct. If it is not working for you:
1. Open the main form in design view.
2. Right-click the edge of the subform control, and choose Properties.
3. Check the Name property of the subfrom control.
The Name of the subform control can be different from the name of the form
it contains (its SourceObject property).
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Erik Thijs" <er********@donotmail.com> wrote in message
news:cm**********@ikaria.belnet.be... Allen Browne wrote:
Hmm. The field names vary in the different tables, so what you are asking to do is to discover the field names appropriate to the table, and dynamically assign the RecordSource of the form and the Control Source of the text boxes.
idd
That's doable. Save the form without any RecordSource. Access can crash if a field suddenly disappears from the RecordSource or if it changes data type, so it's best to leave the form unbound when its saved.
In the Open event procedure of the Form, assign the RecordSource to the table of your choice. Then loop through the Fields collection of this TableDef to discover the Name of the fields, and assign them to the ControlSource of the various text boxes.
The form showing the database info is actually a subform. The main form contains a combobox where the user can select the database to be shown in the subform. Changing the RecordSource of the subform is not a problem (done that), but I'm having troubles accessing the text boxes of the subform (this is actually my first access vba project, and this might be a 'dumb' problem). Is this possible or can it be done only in design time maybe? I've read some posts saying you can access the text boxes of a subform like this: Forms![mainform]![subform].Form!textbox1.property, but somehow this doesn't seem to work for me...
Btw I'm using office 2003 with all service packs and patches applied (via officeupdate).
This code illustrates how to loop through the Fields of the TableDef: http://members.iinet.net.au/~allenbrowne/func-06.html
Hope that gives you what you need to do to get your result.
This is definitely part of the solution, but now I'm still stuck at accessing the text boxes...
tnx! E.T.
Allen Browne wrote: The reference: Forms![MyMainForm].[MySubformControl].Form.[MyTextbox] is correct. If it is not working for you: 1. Open the main form in design view. 2. Right-click the edge of the subform control, and choose Properties. 3. Check the Name property of the subfrom control. The Name of the subform control can be different from the name of the form it contains (its SourceObject property).
Thank you very much! I will give this a try tomorrow.
cheers,
E.T. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: JJMM |
last post by:
Hi,
I have a form (form1) with a large number of subforms inside it (around 20
subforms), There is the possibility of filtering the data using a pop-up
form that create/change a query (all the...
|
by: Evil |
last post by:
Hi,
i have a problem with a treeview and some subforms in MS Access97.
I have a form with a treeview on the left side which lets me navigate
thru some projects. Then on the right side, i have...
|
by: M Wells |
last post by:
Hi All,
I am developing an Access 2003 project application with the back end
in SQL Server 2003.
I have a master form that tracks projects, and several subforms on it
that track various...
|
by: Jack |
last post by:
Hi all,
I searched the archives and found everyone happy with Stephen's
MouseWheel On/Off code except for those with subforms. Stephen's page
indicates that he has added code to handle subforms...
|
by: Jack |
last post by:
Gday everyone,
I'm dearly hoping Stephen Lebans is going to update his masterpeice to
stop the mouse wheel scrolling to work on subforms *he has indicated
this to me but of course beggers can't...
|
by: ApexData |
last post by:
I am using the following code in my TabControl to manage subform
loads. The code assigns the subForms SourceObject.
- Do I also need code to DeAssign the SourceObject when leaving the
Tab, I'm...
|
by: jedraw |
last post by:
I am trying to use the information provided in Allen Brownes response see
Response.
I have a mainform, a tabctl with 5 pages , 5 subforms and 1 subfrom control. I placed the subformname in the...
|
by: manssi |
last post by:
how to make dynamic editor menu in asp.net
i have made application in asp.net but i have no coding for editor menu
i want the menu in following order--
forms
..subforms
.....subforms
|
by: Danny Makus |
last post by:
i want to use a single form(parent) to dynamically loard different subforms within the same location/space on the mother/parent form using either access2000 or access2003.
the mother form is created...
|
by: Harlequin |
last post by:
I have a question concerning the need to trigger events within a "child" subform which is itself enbedded within a master "parent" form and which is accessible via a tab in the parent form. Becuase...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |