I've gotta present an update on my database this week--possibly within the next 24 hours. A solution or guidance to this inquiry would really, really help! Any immediate assistance would be appreciated!
I have a subform (sfrmProjects) in datasheet view. I'd like to double-click my datasheet records to open up a form (frmViewAllResources) in data entry mode. Every time I double-click on the project and open up this data entry form, it should generate a new subrecord for the double-clicked project.
Then, once a resource is associated to a project, I would like the datasheet to show the "+" sign that means there's an associated record. Then, for each subdatasheet record, I'd like to be able to view (as read only) by double-clicking on it and edit by maybe shift+double-click.
I found the following code, but it only provides a data entry copy of the childform if the parent record is completely empty. I would like it to do what I described above.
Private Sub FilterChildForm()
If Me.NewRecord Then
Forms![frmViewAllResources].DataEntry = True
Forms![frmViewAllResources].Filter = "[FiscalYear] = " & """" & Me.[FiscalYear] & """" & " AND [BulkObligation] = " & """" & Me.[BulkObligation] & """" & " AND [Project] = " & """" & Me.[Project] & """"
Forms![frmViewAllResources].FilterOn = True
Someone in a thread on TheScripts (http://www.thescripts.com/forum/thread691533.html) was convinced to change the datasheet to a continuous form and use command buttons. However, the "+" sign feature on datasheets for associated records would be extremely useful for my clients, so, if this can be done with datasheets, that would be fantastic.
So, you want the plus sign next to a record with more data, is that correct?
It looks to me like the trick is the make a query (or table) of the records you want to have in your subform, use that as the control source of your subform/subreport control then go to you source object and insert a Subdatasheet. That will give you plus signs that you can click on the expand and see the data associated.
Thank you so much for your reply. Yes, I want a subdatasheet within the datasheet. My problem is that I want the subdatasheet activated by a separate form altogether. After I double-click a record on the datasheet, I want a form (frmViewAllResources) to pop up for data entry. Then, once I fill in the frmViewAllResources, I want that new record to be reflected in the datasheet through the "+" sign feature.
This is the setup:
sfrmProjects > frmViewAllResources
To elaborate, this is the problem:
1) Double-click on a project in sfrmProjects (datasheet) and have frmViewAllResources not only open in data entry mode, but also copy the related fields (FiscalYear, BulkObligation, Project; these are already related in relationships). Ideally, this would be the key to relating the records and having the "+" sign show up in sfrmProjects. Only after the "+" sign shows up would I like to be able to double-click on the subdatasheet records to open up frmViewAllResources again, but this time as an edit or view window.
Looking at the following linked images: When you open 1.jpg, look at the Project "Vehicles." I want to double-click on it to open 2.jpg. I want to fill in 2.jpg, close it, and then have the "+" appear next to Vehicles that represents that the datasheet has a subdatasheet record. It would be ideal if the datasheet in 1.jpg looked like 3.jpg, which are the underlying tables with the collapsible " +/- " signs indicated by the red circle.
I got the forms linked successfully so that the concatenated PKs in sfrmProjects project into frmViewAllResources. As a subform, my datasheet (sfrmProjects) doesn't show the "+" sign to indicate subrecords. (There ARE indeed subrecords.)
Someone suggested a workaround which manually inputs a "+" sign (not the built-in [+] sign box) in a textbox named txtPlus. However, it shows the "+" sign even if the subrecords are blank. Here's that person's explanation of the behavior both for their own workaround and the built-in [+] sign feature that Access automatically employs:
To the best of my knowledge both table datasheets and form datasheets show [+] box when subdatasheet is present, no matter whether it contains records or not, only NewRecord line doesn't show [+] box.
This is their code that helped me to link the forms successfully and utilize the "+" sign workaround:
SELECT tblProjects.*, IIf(IsNull(DLookUp("AutoNumber","tblViewAllResources","FiscalYear=" & nz(tblProjects.FiscalYear,0) & "AND BulkObligation='" & nz(tblProjects.BulkObligation,0) & "' AND Project='" & nz(tblProjects.Project,0) & "'")),NULL,"+") AS txtPlus FROM tblProjects;
[txtPlus] is bound to the form's [txtPlus] textbox
[frmProject]'s DblClick event handler
Private Sub Form_DblClick(Cancel As Integer)
Dim strFilter As String
If .NewRecord Then Exit Sub
strFilter = "FiscalYear=" & Nz(.FiscalYear, 0) & _
" AND BulkObligation='" & Nz(.BulkObligation, 0) & _
"' AND Project='" & Nz(.Project, 0) & "'"
DoCmd.OpenForm "frmViewAllResources", acFormDS, , strFilter
!FiscalYear.DefaultValue = Me.FiscalYear
!BulkObligation.DefaultValue = Me.BulkObligation
!Project.DefaultValue = Me.Project
.Modal = True
Question: Is there a way to use the "automatic" [+] sign feature (expandable/collapsible box) that Access automatically employs (as opposed to this manual workaround), and, if so, can it be restricted to display only when the target subform (frmViewAllResources) is dirty?
I'm sorry: I've spent most of this thread talking to myself. I figured it out. All it took to make a subdatasheet and get the "+" sign feature to activate next to each record when there's a subrecord was to actually create a subform in datasheet mode, enable the subdatasheet (subdatasheet=yes), and link it to the primary datasheet. I tried this so many times at first, but apparently I was doing it incorrectly.
At least I found out how to correctly link separate forms through a similar thread on another forum.
Thanks to all for your attention and to starkmann for your input.