Problem with Tabbed Forms

[Guest]

I am trying to create a data entry form (see earlier post) containing
multiple tabs. One tab will write information to one table, whereas the
other tabs will write data to another table. I thought this would be fairly
straightforward, since you can add fields to forms from multiple tables using
the form wizard. However, I am only able to select one data table when
creating the table in design view I added unbound controls and tried to bind
them to fields in another table, using the expression builder that pops up
under the "Control Source" on the properties box for the unbound control. In
form view, however, I get a "#Name?" error. I am using Access 2000. The
expression I used to try to bind the control to the field in a different
table looks like this: =[t/route_level_data]![RNPM0-5] Am I doing something
wrong? Can I create a tabbed form in which different tabs are linked to
different tables?

 

[Rick Brandt]

I am trying to create a data entry form (see earlier post) containing
multiple tabs. One tab will write information to one table, whereas
the other tabs will write data to another table. I thought this
would be fairly straightforward, since you can add fields to forms
from multiple tables using the form wizard. However, I am only able
to select one data table when creating the table in design view I
added unbound controls and tried to bind them to fields in another
table, using the expression builder that pops up under the "Control
Source" on the properties box for the unbound control. In form view,
however, I get a "#Name?" error. I am using Access 2000. The
expression I used to try to bind the control to the field in a
different table looks like this: =[t/route_level_data]![RNPM0-5] Am
I doing something wrong? Can I create a tabbed form in which
different tabs are linked to different tables?

One form, one RecordSource. Tabs make no difference in this.

You need to use a subform.

 

[Carl Rapson]

I am trying to create a data entry form (see earlier post) containing
multiple tabs. One tab will write information to one table, whereas the
other tabs will write data to another table. I thought this would be
fairly
straightforward, since you can add fields to forms from multiple tables
using
the form wizard. However, I am only able to select one data table when
creating the table in design view I added unbound controls and tried to
bind
them to fields in another table, using the expression builder that pops up
under the "Control Source" on the properties box for the unbound control.
In
form view, however, I get a "#Name?" error. I am using Access 2000. The
expression I used to try to bind the control to the field in a different
table looks like this: =[t/route_level_data]![RNPM0-5] Am I doing
something
wrong? Can I create a tabbed form in which different tabs are linked to
different tables?

When a form is bound to a particular table, you can't refer to fields in
other tables like you're trying. A form can only be bound to a single table
or query. There are several things you can try:

- Create a query that joins the tables and base the form on that query
- Create subforms that are bound to the other tables and place the subforms
on the tabs
- Use the DLookUp() method to fetch values from other tables and place them
into unbound controls on the form

Carl Rapson

 

[robert.waters]

I am trying to create a data entry form (see earlier post) containing
multiple tabs. One tab will write information to one table, whereas the
other tabs will write data to another table. I thought this would be
fairly
straightforward, since you can add fields to forms from multiple tables
using
the form wizard. However, I am only able to select one data table when
creating the table in design view I added unbound controls and tried to
bind
them to fields in another table, using the expression builder that pops up
under the "Control Source" on the properties box for the unbound control.
In
form view, however, I get a "#Name?" error. I am using Access 2000. The
expression I used to try to bind the control to the field in a different
table looks like this: =[t/route_level_data]![RNPM0-5] Am I doing
something
wrong? Can I create a tabbed form in which different tabs are linked to
different tables?

When a form is bound to a particular table, you can't refer to fields in
other tables like you're trying. A form can only be bound to a single table
or query. There are several things you can try:

- Create a query that joins the tables and base the form on that query
- Create subforms that are bound to the other tables and place the subforms
on the tabs
- Use the DLookUp() method to fetch values from other tables and place them
into unbound controls on the form

Carl Rapson

I've done somethng similar; I set the tabctl's height to the minimum
possible, and then placed a subform control below it, called
'sfrmPlaceholder'. In the TabCtl_Click() event, I dynamically set
that subforms sourceobject based on which 'Page" was selected. I
create one form for each 'function' - in your case, you might have 3
different forms, each tied to a different table/data source, each
loaded into the placeholder subform when needed.

 

[Guest]

Thanks for your response and the ideas. Unfortunately, I'm primarily an
scientist whose been put in charge of managing various databases (most of
which were scattered Excel files dotted around the server). So some of the
VBA solutions people have suggested are a bit over my head...for now (two
weeks ago, I didn't know a form from a table). The database I am currently
working with has well over 100 fields, which is why I split it up. If I use
a query to join the tables and base the form on the query, will data added to
the form be added only to the query, or will the query update the tables on
which it is based?

Thanks again for the information!

Jeff--

I am trying to create a data entry form (see earlier post) containing
multiple tabs. One tab will write information to one table, whereas the
other tabs will write data to another table. I thought this would be
fairly
straightforward, since you can add fields to forms from multiple tables
using
the form wizard. However, I am only able to select one data table when
creating the table in design view I added unbound controls and tried to
bind
them to fields in another table, using the expression builder that pops up
under the "Control Source" on the properties box for the unbound control.
In
form view, however, I get a "#Name?" error. I am using Access 2000. The
expression I used to try to bind the control to the field in a different
table looks like this: =[t/route_level_data]![RNPM0-5] Am I doing
something
wrong? Can I create a tabbed form in which different tabs are linked to
different tables?

When a form is bound to a particular table, you can't refer to fields in
other tables like you're trying. A form can only be bound to a single table
or query. There are several things you can try:

- Create a query that joins the tables and base the form on that query
- Create subforms that are bound to the other tables and place the subforms
on the tabs
- Use the DLookUp() method to fetch values from other tables and place them
into unbound controls on the form

Carl Rapson

 

[Guest]

But I've used a form wizard in the past to create a form with fields from
different tables (although, I haven't had a chance to test the form yet to
see if it works). I would have used the form wizard in this case, but given
that there are well over 100 fields in both tables (which is why I broke it
up into two), the wizard crashed and said it couldn't create the form. It
would seem that this functionality would be useful, which is why I am
surprised you can't do it. Is there a reason why there is only one
RecordSource allowed per form?

Thanks for the information.

Jeff--

I am trying to create a data entry form (see earlier post) containing
multiple tabs. One tab will write information to one table, whereas
the other tabs will write data to another table. I thought this
would be fairly straightforward, since you can add fields to forms
from multiple tables using the form wizard. However, I am only able
to select one data table when creating the table in design view I
added unbound controls and tried to bind them to fields in another
table, using the expression builder that pops up under the "Control
Source" on the properties box for the unbound control. In form view,
however, I get a "#Name?" error. I am using Access 2000. The
expression I used to try to bind the control to the field in a
different table looks like this: =[t/route_level_data]![RNPM0-5] Am
I doing something wrong? Can I create a tabbed form in which
different tabs are linked to different tables?

One form, one RecordSource. Tabs make no difference in this.

You need to use a subform.

 

[Carl Rapson]

The simple answer is: yes, if you base the form on a query, then updating
the form will update the tables underlying the query. The caveat is, when
joining multiple tables it is easy for a query to become "nonupdatable",
which means basically it is read-only. A simple join between two tables on a
single key field should be updatable, but it won't necessarily be. The
easiest way to check it is to create the query and then run it. In the
resulting window, see if you can update the fields. If you can, the query is
updatable and you could use it for the RecordSource of the form, no VBA
required (unless you want to do things like error checking before
inserting/updating records, for example).

If it isn't updatable, what I would recommend is that you create two
queries, one for each table. Base the form on one of the queries, and use
the other query to create a subform on the first form (you can easily do
this by simply dragging and dropping the query onto the form). I would
assume that the two tables are related somehow through some key field, so
you would specify that field as the link between the parent form and the
child form (subform). Access will take care of updating the underlying
tables for you, no VBA required (same exceptions as noted above).

Carl Rapson

Thanks for your response and the ideas. Unfortunately, I'm primarily an
scientist whose been put in charge of managing various databases (most of
which were scattered Excel files dotted around the server). So some of
the
VBA solutions people have suggested are a bit over my head...for now (two
weeks ago, I didn't know a form from a table). The database I am
currently
working with has well over 100 fields, which is why I split it up. If I
use
a query to join the tables and base the form on the query, will data added
to
the form be added only to the query, or will the query update the tables
on
which it is based?

Thanks again for the information!

Jeff--

I am trying to create a data entry form (see earlier post) containing
multiple tabs. One tab will write information to one table, whereas
the
other tabs will write data to another table. I thought this would be
fairly
straightforward, since you can add fields to forms from multiple tables
using
the form wizard. However, I am only able to select one data table when
creating the table in design view I added unbound controls and tried
to
bind
them to fields in another table, using the expression builder that pops
up
under the "Control Source" on the properties box for the unbound
control.
In
form view, however, I get a "#Name?" error. I am using Access 2000.
The
expression I used to try to bind the control to the field in a
different
table looks like this: =[t/route_level_data]![RNPM0-5] Am I doing
something
wrong? Can I create a tabbed form in which different tabs are linked
to
different tables?

When a form is bound to a particular table, you can't refer to fields in
other tables like you're trying. A form can only be bound to a single
table
or query. There are several things you can try:

- Create a query that joins the tables and base the form on that query
- Create subforms that are bound to the other tables and place the
subforms
on the tabs
- Use the DLookUp() method to fetch values from other tables and place
them
into unbound controls on the form

Carl Rapson

 

[missinglinq via AccessMonster.com]

"The database I am currently working with has well over 100 fields, which is
why I split it up."

Having over 100 fields, in and of itself, is not a resaon to split up your
database into multiple tables. It sounds like your problem is screen real
estate! If that's so, then the easiest solution is to use a tabbed form. That
way you can divide your fields/controls up into manageable groups, arranged
logically, and have the space you need to work with them in a non-crowded
environment. Even though they appear on different tabbed pages, they're still
on a single form; references to them, as well as data manipulation, is done
exactly as if they were all on a single screen.

 

[missinglinq via AccessMonster.com]

Meant to say to use a tabbed form with a single table.