Solved

is it possible to use a query based on linked tables as a basis for a form in Datasheet view with subdatasheets

Posted on 2015-01-03
11
257 Views
Last Modified: 2015-01-06
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.
0
Comment
Question by:Rob4077
  • 6
  • 3
  • 2
11 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40529942
To do this, you need 3 forms.
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

datasheet
0
 
LVL 33

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 250 total points
ID: 40529949
Here, I have used Orders and Order_Details tables from northwind database.

In the query supplying Order_Details data, under [Order ID] field, in its criteria put:

IIF(fnOrderID()=0, [Order ID], fnOrderID())  

In standard module, have:

Function fnOrderID() As Long
On Error GoTo ErrHandler

Dim OrderId as long

OrderId = Nz(Forms!Form1!SubForm1_MainForm.Form![Order ID],0)

MsgBox "Order Id is: " & OrderId    ' remove this after making sure the value above is read successfully

fnOrderID = OrderId

Exit Function
ErrHandler:
fnOrderID =0
End Function

In the Current even of the first subform mimicking your main form (SubForm1_MainForm) have:

 'remove first two lines below after the MsgBox below gives a number to you.
On Error Resume Next                                         ' to be removed after the test
MsgBox  Parent!subForm2.Form![Order ID]    ' to be removed after the test
       
If Vartype(Parent!subForm2.Form![Order ID])<> 9 then
    Parent!subForm2.Form.Requery
End IF
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40529951
I tested. It worked the first time. If you get some error, read the posts again and try it again:

DatasheetViews1
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

Open in new window

0
 
LVL 84
ID: 40530024
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.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40530392
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.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 84
ID: 40530732
True, but those aren't subdatasheets, which is what the question asks for.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40530793
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
0
 

Author Comment

by:Rob4077
ID: 40531350
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?
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40531382
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
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 40531488
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?
Not sure if Access converts it or not, but when I set up my test environment, I created a Datasheet Parent Form, and a DataSheet Child form, and a Datasheet "GrandChild" form and dropped the Child onto the Parent, and the GrandChild onto the Child. When I did this, I made sure the "Default View" in the Properties - Format tab was set to Datasheet, and I ended up with this:
Multi-level SubdatasheetsIn this case, I've got Subdatasheets opened for the "AGO" and "ARE" Parent records. Each of those Subdatasheets also has a SubDatasheet open for one of the records shown in the Child records.

Of course, I also made sure the Master/Child links were set correctly. This is critical to the forms showing what you intend.
0
 

Author Comment

by:Rob4077
ID: 40533207
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
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now