Displaying a table within a table

[cs_vision]

I have a table that I have added fields that contain lookups to other tables.
How can I display the full table from that field within the main table?

 

[kingston via AccessMonster.com]

Are you talking about sub-datasheets? You can add another table's related
records to a main table, but at least in AC2000, this only works one table at
a time. To do this, open the main table and select menu option Insert ->
Subdatasheet... Choose a related table and the related fields between the
two tables. A column of + signs will appear to the left of the records when
you're done. Clicking on this + will reveal the sub-datasheet.

 

[Jeff Boyce]

I'm not completely clear from your description, but it sounds like you are
trying to do this in a table, not in a form.

A check through the tablesdbdesign newsgroup will show a VERY strong
consensus against using lookup data type fields in tables. One major reason
is that the field stores one value, but displays something else. This
causes considerable confusion!

If you have a table that contains "lookup" items, the place to use (and
show) that is in forms.

Or have I misinterpreted?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[cs_vision]

Thanks for your help, I didn't realize this is not what I needed, how do you
remove the subdatasheet?

To explain better, I am trying to create a form from 8 tables. The main
table called documents has client name, attorney, documents, document date
and classification fields. The remaining 7 tables are child tables bound by
the classification field (or so I want them to be). Based on the
classification choosen for each client name, I would like the form I created
for the remaining 7 tables to popup. Each table/form represents a certain
classification.

 

[cs_vision]

I did figure out how to remove the subdata sheet.

 

[kingston via AccessMonster.com]

Have you considered creating a tab control in your main form? It could have
7 tabs and a subform in each one corresponding to the data in the main table.
It's clean and easy to implement but might be slow.

 

[cs_vision]

Great, can the existing field "classification" be used as a control for the
tab relating to the classification chosen?

 

[kingston via AccessMonster.com]

Maybe I missed something. Does each client get only one classification value
and hence one subform? If so, I'm not sure you should use a tab control; you
can simply stack the subforms on top of each other and turn the appropriate
ones on or off.

Also, I'm not clear on how you wish to use the existing field [classification]
. Do you mean that once it is populated, the corresponding subform will
appear? You can make this happen by using the control's AfterUpdate event to
control individual subform properties such as: .Visible, .Enabled, .Locked.
For example:

Select Case Me.Classification
Case "Class1"
Me.Subform1.Visible = True
Me.Subform2.Visible = False
...
Me.Subform7.Visible = False
Case "Class2"
Me.Subform1.Visible = False
Me.Subform2.Visible = True
...
Me.Subform7.Visible = False
...
End Select

Great, can the existing field "classification" be used as a control for the
tab relating to the classification chosen?

Have you considered creating a tab control in your main form? It could have
7 tabs and a subform in each one corresponding to the data in the main table.

[quoted text clipped - 13 lines]

 

[cs_vision]

Yes, each client will receive a classification only once and the subform
chosen shows specific information (really a list of questions with answers,
typed in data entry) per classification. I had the classification field from
the main table and thought once I chose the classification for the client,
then a form would popup or be populated on the main form for data entry of
answers from each client.

Maybe I missed something. Does each client get only one classification value
and hence one subform? If so, I'm not sure you should use a tab control; you
can simply stack the subforms on top of each other and turn the appropriate
ones on or off.

Also, I'm not clear on how you wish to use the existing field [classification]
. Do you mean that once it is populated, the corresponding subform will
appear? You can make this happen by using the control's AfterUpdate event to
control individual subform properties such as: .Visible, .Enabled, .Locked.
For example:

Select Case Me.Classification
Case "Class1"
Me.Subform1.Visible = True
Me.Subform2.Visible = False
...
Me.Subform7.Visible = False
Case "Class2"
Me.Subform1.Visible = False
Me.Subform2.Visible = True
...
Me.Subform7.Visible = False
...
End Select

Great, can the existing field "classification" be used as a control for the
tab relating to the classification chosen?

Have you considered creating a tab control in your main form? It could have
7 tabs and a subform in each one corresponding to the data in the main table.

[quoted text clipped - 13 lines]

I have a table that I have added fields that contain lookups to other tables.
How can I display the full table from that field within the main table?

 

[kingston via AccessMonster.com]

OK, I understand now. Which method do you want to pursue? Do you want to
use a subform, a tab control with multiple subforms, or a separate form
entirely? In any case, you will use the After Update event of the
classification control of the main form if you are classifying the client for
the first time or reclassifying the client. If the client is already
classified and you simply want to bring up the correct subform, you'll
probably use the main form's OnCurrent event.

Does the code I provided earlier make sense?

Yes, each client will receive a classification only once and the subform
chosen shows specific information (really a list of questions with answers,
typed in data entry) per classification. I had the classification field from
the main table and thought once I chose the classification for the client,
then a form would popup or be populated on the main form for data entry of
answers from each client.

Maybe I missed something. Does each client get only one classification value
and hence one subform? If so, I'm not sure you should use a tab control; you

[quoted text clipped - 29 lines]

 

[cs_vision]

Yes the information provided was very helpful. I am just learning code, what
does the "Me" stand for in the statement?

Maybe I missed something. Does each client get only one classification value
and hence one subform? If so, I'm not sure you should use a tab control; you
can simply stack the subforms on top of each other and turn the appropriate
ones on or off.

Also, I'm not clear on how you wish to use the existing field [classification]
. Do you mean that once it is populated, the corresponding subform will
appear? You can make this happen by using the control's AfterUpdate event to
control individual subform properties such as: .Visible, .Enabled, .Locked.
For example:

Select Case Me.Classification
Case "Class1"
Me.Subform1.Visible = True
Me.Subform2.Visible = False
...
Me.Subform7.Visible = False
Case "Class2"
Me.Subform1.Visible = False
Me.Subform2.Visible = True
...
Me.Subform7.Visible = False
...
End Select

Great, can the existing field "classification" be used as a control for the
tab relating to the classification chosen?

Have you considered creating a tab control in your main form? It could have
7 tabs and a subform in each one corresponding to the data in the main table.

[quoted text clipped - 13 lines]

I have a table that I have added fields that contain lookups to other tables.
How can I display the full table from that field within the main table?

 

[kingston via AccessMonster.com]

Me. is a reference to the form itself where the code resides. It is a
shortcut for writing [Forms]![ThisFormName]. It is also very helpful because
it automatically brings up a selection of properties and controls.

Yes the information provided was very helpful. I am just learning code, what
does the "Me" stand for in the statement?

Maybe I missed something. Does each client get only one classification value
and hence one subform? If so, I'm not sure you should use a tab control; you

[quoted text clipped - 29 lines]

 

[cs_vision]

Thanks so much! HAPPY FRIDAY!!!!

Me. is a reference to the form itself where the code resides. It is a
shortcut for writing [Forms]![ThisFormName]. It is also very helpful because
it automatically brings up a selection of properties and controls.

Yes the information provided was very helpful. I am just learning code, what
does the "Me" stand for in the statement?

Maybe I missed something. Does each client get only one classification value
and hence one subform? If so, I'm not sure you should use a tab control; you

[quoted text clipped - 29 lines]

I have a table that I have added fields that contain lookups to other tables.
How can I display the full table from that field within the main table?