By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,661 Members | 1,311 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,661 IT Pros & Developers. It's quick & easy.

Multiple subforms/sub datasheets

P: n/a
I've noticed that you can only have a sub datasheet pointing to one
table... is there anyway to change this? for example I have
Customer
|
---------- Customer/Product
|
----------- Customer Grp/Sales Grp
Now access wants customer/product and customer grp/sales grp to point
to the same table, is there any way to get each sub datasheet to point
do its own table?

I've noticed that forms act the same way when trying to have multiple
subforms within a subform...someone please help :(

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Create each form separately then drag and drop the two subforms onto the
main form. Set the Parent/Child link fields. Each subform should have its
own Record Source.

--
Wayne Morgan
MS Access MVP
"Beacher" <be*****@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
I've noticed that you can only have a sub datasheet pointing to one
table... is there anyway to change this? for example I have
Customer
|
---------- Customer/Product
|
----------- Customer Grp/Sales Grp
Now access wants customer/product and customer grp/sales grp to point
to the same table, is there any way to get each sub datasheet to point
do its own table?

I've noticed that forms act the same way when trying to have multiple
subforms within a subform...someone please help :(

Nov 13 '05 #2

P: n/a
I tried but it didnt seem to work.. ill be a bit more clear as well.. I
have a main form... and then a subform in this form I have 3 fields

field 1
field 2
field 3

now each of these fields have to point to a different table, but
dragging and dropping.. they all point to the same table

Nov 13 '05 #3

P: n/a
Ok, that's a different scenario. To get fields from 3 tables in a form you
have a couple of options. 1) Base the form on a query instead of a table.
Include the needed tables and fields in the query. If the query gets too
complicated and isn't updateable, then you won't be able to edit or add
records in the form. 2) Use DLookup() or other functions and make the
controls calculated controls. These controls won't be editable by the user.

--
Wayne Morgan
MS Access MVP
"Beacher" <be*****@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
I tried but it didnt seem to work.. ill be a bit more clear as well.. I
have a main form... and then a subform in this form I have 3 fields

field 1
field 2
field 3

now each of these fields have to point to a different table, but
dragging and dropping.. they all point to the same table

Nov 13 '05 #4

P: n/a
Thank you very much :)

I never have used DLookup() before...infact I haven't done much in
vba.. but I looked up the use for it.. does it somewhat act like a
query? Would I use the parent table and use the primary key field to
look up the specific number I need? and if so, how would I put the
results of the DLoopup() into a subform/Datasheet

DLookup("FieldName" , "TableName" , "Criteria = n")
Dloopup("CondID","tblProduct_Sales","CondID = 1") Would show me all
results where the CondID field = 1 correct? if so, I need to display
the whole row not just the one field

Nov 13 '05 #5

P: n/a
Yes, DLookup() works somewhat like a query, but it only returns the first
result it finds, not all the matching records as a query does. It is usually
used with a unique value for the criteria so that there is only one value to
be found. To use it, it is probably easiest to make a "calculated control".
To do that, you place an equation in the control's Control Source instead of
a field.

Example:
=DLookup("CondID", "tblProduct_Sales", "CondID = 1")

Usually, you don't use the same field in the condition and the return field,
you know the value of this field already, you don't need to look it up. You
would return another field from the record that matches the condition.

If you are wanting to return multiple fields from the record, it may be
better to use a listbox with multiple columns. You could place a query in
the Row Source of the listbox. The query's criteria would limit the record
returned, but you could display all of the fields from the record.

What you described in your first message as wanting to do IS what a subform
does. The scenario you gave in your second message is slightly different
from this one. I don't fully follow what you're trying to do.

--
Wayne Morgan
MS Access MVP
"Beacher" <be*****@gmail.com> wrote in message
news:11********************@g44g2000cwa.googlegrou ps.com...
Thank you very much :)

I never have used DLookup() before...infact I haven't done much in
vba.. but I looked up the use for it.. does it somewhat act like a
query? Would I use the parent table and use the primary key field to
look up the specific number I need? and if so, how would I put the
results of the DLoopup() into a subform/Datasheet

DLookup("FieldName" , "TableName" , "Criteria = n")
Dloopup("CondID","tblProduct_Sales","CondID = 1") Would show me all
results where the CondID field = 1 correct? if so, I need to display
the whole row not just the one field

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.