Need subform->datasheet to show all records

  • Thread starter Thread starter WHardy7
  • Start date Start date
W

WHardy7

I cannot seem to find anyone to understand what I am trying to do (whic
means it might not be a good idea!), so I am explaining it another way

I need two subform->datasheets on a form that are one-many (Subjects t
Books). The left subform needs to show ALL Subjects and the righ
subform needs to show only related Books, if any. My relation work
okay, but I simply cannot figure out how to show ALL Subjects, instea
of one at a time.

I have made this work with lists, but I need to be able to go to th
last row and enter a new record on each subform, which a datashee
makes very intuitive. Also, these will each hold several thousands o
records, which seems more feasible on a datasheet than a list.

Any help?
Thanks in advance
Wayn
 
Wayne, for an example of how to do this:
- Open the Northwind sample database.
- Open the Customer Orders form.

The lower subform ("Customer Orders Subform2") shows the details for the
record selected in the upper subform ("Customer Orders Subform1"). It does
this because:

1) its Link Master Fields property is set to:
[Customer Orders Subform1].Form![OrderID]

2) the Current event of the first subform requeries the 2nd subform.

I think that's what you are wanting to do? Personally, I don't like the
approach, as it has some inherent instabilities. It's not too difficult to
get an endless loop going here with unintentional dependencies, so test it
thoroughly - especially if there are other calculated fields or you are
using conditional formatting anywhere.
 
Wayne,

I've been working through a nearly identical problem as yours (just a
different business context and slightly different layout).

Using Allen's suggestion below, I checked out the Northwind DB. The
solution there wasn't effective for me and might not be for you. But
it DID give me the idea to try something that did work:

1. Create a blank form, not bound to any data source.
2. Insert your two subforms. Do not set Master/Child links.
3. Create a text box on your blank parent form. Set its properties to
hidden for when the form is in Form View. (Let's call it "txtPK")
4. On your primary form's On Current event, set the value of the
"txtPK" text box to the value of the current record's primary key.
Mine looks something like this: "Me.Parent.txtPK = Me.txtActivityNo."
5. On your secondary subform's Data tab:
Link Child = the name of your foreign key
Link Master = the name of the text box on the parent form.

This results in:
1. You click on a different record in your main subform.
2. The primary key changes in the hidden text box
3. The subform changes data due to its source being the text box.

This should solve your problem. You can also keep each subform in
Datasheet or Continuous mode.

Now, if someone out there can tell me how to have a sub datasheet in a
form that works so that I can actually add subrecords for a main
record, I'd appreciate it! I'm using the solution above, but this is
really what I want.
 
That's good, Craig

That's actually what I do when I need to do this, and I suspect an earlier
version of Northwind did it that way as well.
 
Back
Top