Improve company productivity with a Business Account.Sign Up

x
?
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
Medium Priority
?
287 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 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 1000 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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
LVL 86
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 86
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 86

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
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 start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

585 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