is it possible to use a query based on linked tables as a basis for a form in Datasheet view with subdatasheets
I would like to develop a form that displays like a datasheet with subdatasheets based on a query that calls on linked tables. Is this possible? Or do I need to use linked forms?
I have tried several approaches but I can only seem to get Subdatasheets to work tables, not queries or forms but I only want to expose some of the fields in the table on the form.
I have tried several approaches but I can only seem to get Subdatasheets to work tables, not queries or forms but I only want to expose some of the fields in the table on the form.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I tested. It worked the first time. If you get some error, read the posts again and try it again:
The only change I will make is:
The only change I will make is:
Private Sub Form_Current() '(SubForm1_MainForm)
On Error Resume Next ' Do not remove this
Parent!subForm2.Form.Requery
End Sub
You can embed a Datasheet into another Datasheet if you'd prefer. To do that, create your forms as Datasheet forms, and drop the "child" form into the "parent" form, and set the Master/Child links to the field(s) that relate those two datasets. When you open the Parent form, you'll see the + sign. When you click that, you'll show the values associated with the related fields.
You can also do sub-subdatasheets, so you could have data related to the "child" that would be shown as a subdatasheet when that child is shown. Same concept applies - create a form in the Datasheet view, drop it on the "child" form, and set the Master/Child links to the field(s) that relate those two datasets. If you do that, now when you click on the + sign to show the "child" records, you can further click on the + sign in those Child records to show the "grandchild" records.
You can also do do this with queries if you create a Form and base your Form on that query.
You can also do sub-subdatasheets, so you could have data related to the "child" that would be shown as a subdatasheet when that child is shown. Same concept applies - create a form in the Datasheet view, drop it on the "child" form, and set the Master/Child links to the field(s) that relate those two datasets. If you do that, now when you click on the + sign to show the "child" records, you can further click on the + sign in those Child records to show the "grandchild" records.
You can also do do this with queries if you create a Form and base your Form on that query.
Rob4077,
Using the solution I have provided, you no longer are limited to three levels of subforms. You can have as many subform level you wish to have. Just situate the subdorms where you want on a form and then wire them using this sample code.
Using the solution I have provided, you no longer are limited to three levels of subforms. You can have as many subform level you wish to have. Just situate the subdorms where you want on a form and then wire them using this sample code.
True, but those aren't subdatasheets, which is what the question asks for.
Hi Scott ,
re:> subdatasheets
I took subdatasheets as being subform for dasheet form. I didn't have much use in the past for subdatasheets but it could be very useful.
This said, the solution provided gives more freedom to have subform for subform in a mainform and more (as many level one desires). Rob doesn't ask for it but he possibly wouldn't mind knowing about it.
I appreciate reminding me about subdatasheets I need to make use of it.
Regards,
Mike
re:> subdatasheets
I took subdatasheets as being subform for dasheet form. I didn't have much use in the past for subdatasheets but it could be very useful.
This said, the solution provided gives more freedom to have subform for subform in a mainform and more (as many level one desires). Rob doesn't ask for it but he possibly wouldn't mind knowing about it.
I appreciate reminding me about subdatasheets I need to make use of it.
Regards,
Mike
ASKER
Hi Mike and Scott. First of all apologies for the delay in responding.
Mike, just confirming that what you're suggesting is basically two linked forms, both in datasheet view. When I click on a record in the parent form, the child form displays the child record(s) also in datasheet view. Is that the end result? If it is then the main limitation I see is that I can only have one record expanded at a time. If I use subdatasheets then I could have multiple children visible simultaneously.
Scott, you said "You can embed a Datasheet into another Datasheet if you'd prefer. To do that, create your forms as Datasheet forms, and drop the "child" form into the "parent" form, and set the Master/Child links to the field(s) that relate those two datasets. When you open the Parent form, you'll see the + sign. When you click that, you'll show the values associated with the related fields." This is what I wanted to achieve as it will enable multiple parent records to be opened at a time. Untidy look I know, but for this application it may be the better option. Can you elaborate on how I do that? If I drop a child form into the parent form, I thought Access converts the parent form to SINGLE instead of CONTINUOUS or DATASHEET. Or am I doing something wrong?
Mike, just confirming that what you're suggesting is basically two linked forms, both in datasheet view. When I click on a record in the parent form, the child form displays the child record(s) also in datasheet view. Is that the end result? If it is then the main limitation I see is that I can only have one record expanded at a time. If I use subdatasheets then I could have multiple children visible simultaneously.
Scott, you said "You can embed a Datasheet into another Datasheet if you'd prefer. To do that, create your forms as Datasheet forms, and drop the "child" form into the "parent" form, and set the Master/Child links to the field(s) that relate those two datasets. When you open the Parent form, you'll see the + sign. When you click that, you'll show the values associated with the related fields." This is what I wanted to achieve as it will enable multiple parent records to be opened at a time. Untidy look I know, but for this application it may be the better option. Can you elaborate on how I do that? If I drop a child form into the parent form, I thought Access converts the parent form to SINGLE instead of CONTINUOUS or DATASHEET. Or am I doing something wrong?
Rob,
At this point, yes there is such a limitation. But, the approach is such that it could be manipulated to show child records for multiple parent record selected (checked, meaning there is a need to include a check box in the parent table).
This check box could remain invisible in the parent form. If invisible, then the checked parent records could be highlighted each with a separate color (using conditional format). The related child forms below also could maintain the same background colors. This will be the Cadillac version of the project.
But, if the check box remains visible, then we can see:
- What parent records are selected (checkboxes will show them).
- Via PK (in the parent form), and FK (in the child form)
users can see which child belongs to which parent.
Additionally, when a parent record is checked, it moves to the top of list, so do the childs of the matching PKs.
Mike
At this point, yes there is such a limitation. But, the approach is such that it could be manipulated to show child records for multiple parent record selected (checked, meaning there is a need to include a check box in the parent table).
This check box could remain invisible in the parent form. If invisible, then the checked parent records could be highlighted each with a separate color (using conditional format). The related child forms below also could maintain the same background colors. This will be the Cadillac version of the project.
But, if the check box remains visible, then we can see:
- What parent records are selected (checkboxes will show them).
- Via PK (in the parent form), and FK (in the child form)
users can see which child belongs to which parent.
Additionally, when a parent record is checked, it moves to the top of list, so do the childs of the matching PKs.
Mike
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, thanks for the help and for offering me the options. I will split the points because both have offered a viable option with huge amounts of backup information and samples. I really appreciate your very kind assistance. I think I will stick to the Subdatasheet option if I can get the users comfortable with it as I believe it will be best for this particular application but if they don't adapt well then I will revert to the other option. Thanks again
1. Main form in form view (just to hold the two other forms).
2. subfom1 (to mimick as main from in datasheet view)
3. A second subform in datasheet view.
There will be not going to be usual parent/child settings. It will be handled in a different way. Go ahead and populate your subforms. I will add information how the first subform (mimicking your main form) and the 2nd subform will work together.
Mike