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
273 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
11 Comments
 
LVL 34

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 34

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 34

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85
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 34

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
 
LVL 85
ID: 40530732
True, but those aren't subdatasheets, which is what the question asks for.
0
 
LVL 34

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 34

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 85

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

724 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