Datasheet to Open Child Form

Hi, there.

• concerning "+"
  You may build a query returning text field containing "+" if related records in FK side table exist, otherwise Null. Like the following.
  Assuming keyProject is the field these tables linked on.
  
  Expand|Select|Wrap|Line Numbers

  1. SELECT tblProjects.*, iif(isnull(DLookUp("keyProject", "tblResources", "keyProject=" & tProjects.keyProject)),Null,"+") as txtPlus
  2. FROM tblProjects;
  3.  
  Or returning a number of related resources, like the following.
  
  Expand|Select|Wrap|Line Numbers

  1. SELECT tblProjects.*, DCount("keyProject", "tblResources", "keyProject=" & tProjects.keyProject) as lngProjectResourcesCount
  2. FROM tblProjects;
  3.  
  Then set [sfrmProjects].RecordSource to this query and link an additional Textbox to that calculated field.
• to get some ideas on how to open subform separately you may look at Add Matching Record on Filtered Form

FishVal,

Thanks so much for your prompt reply. Unfortunately, I'm as new to Access as I am to this forum, so, while I can make some sense of your code, I can barely formulate ideas as to how to implement it.

ELABORATING...
The forms' two respective and corresponding tables (tblProjects and (tblResources) are related with a concatenated key composed of FiscalYear, BulkObligation, and Project. Obviously, I'd like these fields to copy from "sfrmProjects" over into "frmViewAllResources." However, since frmViewAllResources is its own little monster of controls and tabs, I don't have it embedded as a subform within sfrmProjects. Usually, when dealing with main forms and embedded subforms, all one has to do is fill in the records and hit TAB to move to the subform and all the related fields will be filled accordingly. Unfortunately, since I want to double-click on the datasheet to bring up another form, it doesn't necessarily fill in the related fields automatically. That's why I'm having the datasheet, "+" feature, and related records issues.

I tried using the form wizard to link the forms. The default button to link the forms is a toggle key. I modified the code to use a double-click event instead. So, when I double-click on a Project like "Biofuels," the resulting form (frmViewAllResources) pops up with "dirty" records. If I click on another Project ("Environmental Clean-Up"), the same frmViewAllResources pops up with the same subrecords. So, really, each Project isn't matching up with its respective subrecords. Then, if I click on a Project that's null (i.e. is completely blank 'cause I haven't filled it in), frmViewAllResources pops up as a data entry form.

The concept is generally fine, but I want the data entry frmViewAllResources to pop up when the Project is actually there (i.e. record is dirty). Only after the Resource has been properly associated (as a subdatasheet "+" record) would I like the frmViewAllResources to pop up as viewable (by double-clicking the subdatasheet record) or editable (by shift-double-clicking the subdatasheet record).

This is part of the code I got for the childform (frmViewAllResources) by linking the forms via the form wizard:

I know:

1) this will only determine how to open the childform when double-clicking. It doesn't even begin to address how to link Projects and Resources so that the datasheet reflects the subrecords with the "+" sign.
2) that the "Me.NewRecord" code is telling the form to open up as data entry. My problem is that I don't know how to tell it how to open up frmViewAllResources as data entry when "Me" is dirty.

I'm gonna owe all contributing parties a beer or their favorite non-alcoholic beverage for helping me!

Rackin' my brain for inklings of enlightenment,
BASSPU03

Hi, BUSSPU.

Let's divide the problem into two parts.

• Objective 1.
  To get on the main form some control indicating the record has child records. This may be textbox with "+" or checkbox. The controlsource for this control will be calculated field in a query - form's recordsource.
  Have you tried my suggestion from post#2 ? If yes, then did you've got it working? If you feel its somewhat complicated I'd be glad to provide you with comprehensive instructions.
• Objective 2.
  To open separate form and make it work as embedded subform. That is normally done by setting Form.Filter and default values for the fields the form is filtered by. The result for user will be exactly subform like behaviour.

I'm quite aware of that it is somewhat difficult for you. To give a more definite advices I need to know [tblProjects] and [tblResources] metadata.

Here is an example of how to post table MetaData :
Table Name=tblStudent
Regards,
Fish

[list][*] Objective 1.
To get on the main form some control indicating the record has child records. This may be textbox with "+" or checkbox. The controlsource for this control will be calculated field in a query - form's recordsource.
Have you tried my suggestion from post#2 ? If yes, then did you've got it working? If you feel its somewhat complicated I'd be glad to provide you with comprehensive instructions.

I didn't try your suggestion because, admittedly, I only would've been tinkering around without certainty. Nonetheless, yes, I would love a comprehensive explanation if it's not too much trouble.

To give a more definite advices I need to know [tblProjects] and [tblResources] metadata.

Here is an example of how to post table MetaData :
Table Name=tblStudent

Expand|Select|Wrap|Line Numbers

1. Field; Type; IndexInfo
2. StudentID; AutoNumber; PK
3. Family; String; FK
4. Name; String
5. University; String; FK
6. Mark; Numeric
7. LastAttendance; Date/Time

Table Name=tblProjects
Table Name=tblViewAllResources
I hope this is right. Thank you.

Hi, there.

Taking into account that you are facing deadline, I'm attaching more or less working db sample.

Pay attention to:

• [frmProjects].RecordSource
  
  Expand|Select|Wrap|Line Numbers

  1. 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; 
  2.  
  [txtPlus] is bound to the form's [txtPlus] textbox
• [frmProject]'s DblClick event handler
  
  Expand|Select|Wrap|Line Numbers

  1. Private Sub Form_DblClick(Cancel As Integer)
  2.  
  3.     Dim strFilter As String
  4.  
  5.     With Me
  6.         If .NewRecord Then Exit Sub
  7.         strFilter = "FiscalYear=" & Nz(.FiscalYear, 0) & _
  8.             " AND BulkObligation='" & Nz(.BulkObligation, 0) & _
  9.             "' AND Project='" & Nz(.Project, 0) & "'"
  10.         DoCmd.OpenForm "frmViewAllResources", acFormDS, , strFilter
  11.     End With
  12.  
  13.     With Forms!frmViewAllresources
  14.         !FiscalYear.DefaultValue = Me.FiscalYear
  15.         !BulkObligation.DefaultValue = Me.BulkObligation
  16.         !Project.DefaultValue = Me.Project
  17.         .Modal = True
  18.     End With
  19.  
  20. End Sub
  21.  

Expand|Select|Wrap|Line Numbers

1. Private Sub Form_DblClick(Cancel As Integer)
2.  
3.     Dim strFilter As String
4.  
5.     With Me
6.         If .NewRecord Then Exit Sub
7.         strFilter = "FiscalYear=" & Nz(.FiscalYear, 0) & _
8.             " AND BulkObligation='" & Nz(.BulkObligation, 0) & _
9.             "' AND Project='" & Nz(.Project, 0) & "'"
10.         DoCmd.OpenForm "frmViewAllResources", acFormDS, , strFilter
11.     End With
12.  
13.     With Forms!frmViewAllresources
14.         !FiscalYear.DefaultValue = Me.FiscalYear
15.         !BulkObligation.DefaultValue = Me.BulkObligation
16.         !Project.DefaultValue = Me.Project
17.         .Modal = True
18.     End With
19.  
20. End Sub
21.  

[/list]

FishVal, thank you for your patience and generosity! I generally understand the code and have input it into my form according to your instructions, but I'm getting the following error when the frmViewAllResources opens:

"Run-time error '438': Object doesn't support the property or method."

When I hit Debug, it refers me to the following highlighted code:

It's been a long day and I worked on implementing this solution rather haphazardly. So, unless this error makes any sense to you, I'll give it another shot tomorrow at work and then let you know what's up.

Thanks again so much!

FishVal, thank you for your patience and generosity! I generally understand the code and have input it into my form according to your instructions, but I'm getting the following error when the frmViewAllResources opens:

"Run-time error '438': Object doesn't support the property or method."

When I hit Debug, it refers me to the following highlighted code:

Expand|Select|Wrap|Line Numbers

1. !FiscalYear.DefaultValue = Me.FiscalYear

It's been a long day and I worked on implementing this solution rather haphazardly. So, unless this error makes any sense to you, I'll give it another shot tomorrow at work and then let you know what's up.

Thanks again so much!

• Check whether controls in both forms has names "FiscalYear"
• Having clicked on <Debug> button, try the following in VBA Immediate window (if Immediate window is not visible, then press Ctrl-G):
  ? Me.FiscalYear
  ? !FiscalYear
  ? !FiscalYear.DefaultValue

• Check whether controls in both forms has names "FiscalYear"
• Having clicked on <Debug> button, try the following in VBA Immediate window (if Immediate window is not visible, then press Ctrl-G):
  ? Me.FiscalYear
  ? !FiscalYear
  ? !FiscalYear.DefaultValue

1) I checked that each corresponding table had "FiscalYear" in it. Each does. "frmViewAllResources" didn't have form controls for "FiscalYear" and "Project." I put those in and the same error popped up and pointed to the same spot in the debugger.

2) I placed:
...into the debugger's immediate window and it didn't change a thing.

==========

Question: This "+" sign feature is a "homemade" solution, right? I mean, this isn't the same "+" that automatically appears next to datasheet records when subrecords are associated in the subdatasheet.

All I really want to do is the following:

Looking at the attached 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.

1.jpg: http://i235.photobucket.com/albums/ee140/BASSPU03/1.jpg
2.jpg: http://i235.photobucket.com/albums/ee140/BASSPU03/2.jpg
3.jpg: http://i235.photobucket.com/albums/ee140/BASSPU03/3.jpg

1) I checked that each corresponding table had "FiscalYear" in it. Each does. "frmViewAllResources" didn't have form controls for "FiscalYear" and "Project." I put those in and the same error popped up and pointed to the same spot in the debugger.

Both forms [frmProjects] and [frmViewAllResources] must have controls bound to corresponding tables' fields [FiscalYear], [Project], [BulkObligation] - all table fields participating in PK/FK relationship. If you don't want to see them, then make them invisible.

2) I placed:

Expand|Select|Wrap|Line Numbers

1. ? Me.FiscalYear
2. ? !FiscalYear
3. ? !FiscalYear.DefaultValue
4.  

...into the debugger's immediate window and it didn't change a thing.

That is not supposed to cure something. It is a test for what property actually causes the error. So, again, when in debug mode after error has occured, type each of the above lines in VBA Immediate window pressing enter after each (to run them, Immediate window is just for that - to run code immediately). What is the result of each command?

Question: This "+" sign feature is a "homemade" solution, right? I mean, this isn't the same "+" that automatically appears next to datasheet records when subrecords are associated in the subdatasheet.

All I really want to do is the following:

Looking at the attached 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.

1.jpg: http://i235.photobucket.com/albums/ee140/BASSPU03/1.jpg
2.jpg: http://i235.photobucket.com/albums/ee140/BASSPU03/2.jpg
3.jpg: http://i235.photobucket.com/albums/ee140/BASSPU03/3.jpg

[/quote]

Oh, yea. "+" is something home-made. Access.Form does not provide, to the best of my knowledge, an opportunity to change behavior of that native "+" box. You need either another ActiveX control (I have nothing to suggest you) or be happy with this home-made trick. BTW, instead of "+" the textbox may contain a number of resources associated or this may be checkbox.

Regards,
Fish

Both forms [frmProjects] and [frmViewAllResources] must have controls bound to corresponding tables' fields [FiscalYear], [Project], [BulkObligation] - all table fields participating in PK/FK relationship. If you don't want to see them, then make them invisible.

I inserted all three controls into frmViewAllResources. They are, in fact, useful on there.

That is not supposed to cure something. It is a test for what property actually causes the error. So, again, when in debug mode after error has occured, type each of the above lines in VBA Immediate window pressing enter after each (to run them, Immediate window is just for that - to run code immediately). What is the result of each command?

Ah, geez...please pardon my ignorance! Here're the results:
1) 2007
2) Null
3) This line generates the error

Oh, yea. "+" is something home-made. Access.Form does not provide, to the best of my knowledge, an opportunity to change behavior of that native "+" box. You need either another ActiveX control (I have nothing to suggest you) or be happy with this home-made trick. BTW, instead of "+" the textbox may contain a number of resources associated or this may be checkbox.

So, datasheets within forms don't actually display a + sign when there are subrecords? They only do so when viewed as tables?

Thanks.

Ah, geez...please pardon my ignorance! Here're the results:
1) 2007
2) Null
3) This line generates the error

Oops! Very strange. Just to make sure we are mentioning the same. Is frmViewAllResources.FiscalYear a textbox or a combobox or something else?

So, datasheets within forms don't actually display a + sign when there are subrecords? They only do so when viewed as tables?

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.

If your major intension is to have a sign showing a presence of associated records in [tblViewAllResources], then this native Access.Form feature will be useless.

On the other hand you can imitate it pretty close in continuous forms view: textbox with "+" and to open subform you may handle click event on this textbox.

P.S. I need to leave for a couple of hours.

Oops! Very strange. Just to make sure we are mentioning the same. Is frmViewAllResources.FiscalYear a textbox or a combobox or something else?

Nevermind! I got it to work! I deleted the ".defaultvalue" from each line and voilà! Now the frmViewAllResources opens without an error, properly displays the FiscalYear, BulkObligation, and Project, and closes without a problem! Thank you so much! You're a genius and I'm just lucky!

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.

Yeah, I can see what you mean. You're right: They show the [+] box so long as the tables are related, even if the subrecords are empty. But, it seems kind of pointless if it shows the sign whether or not subrecords exist, doesn't it? Is there a way to tell frmProjects to display it only when "Resources" is dirty in frmViewAllResources?

Hi, BASSPU.

Nevermind! I got it to work! I deleted the ".defaultvalue" from each line and voilà! Now the frmViewAllResources opens without an error, properly displays the FiscalYear, BulkObligation, and Project, and closes without a problem! Thank you so much! You're a genius and I'm just lucky!

I don't want to disappoint you but it isn't a time to celebrate victory yet. :)
Though it seems to work, it actually doesn't.
The idea was to open the form in such a way that every resource added to it will be associated with a proper project. What it is doing so far? It creates a new record with proper values of FK fields. This will cause the following:

• a new record is created just when a user opens the form, if the user will close the form w/o entering information, then the empty record will remain in database
• if a user decide to add more than one record, then only the first will have a proper FK fields values, all subsequent records will be associated with nothing

I've been thinking about a possible reason of the fault, but have no idea so far. The results of your debugging tells us:

• reference to a control on the main form returns a proper value
• reference to a control on the subform returns a proper value
• reference to the latter control.DefaultValue property causes the error mentioned

Thats rather weird as I couldn't reproduce it with TextBox control.
I think I should take a look at your db. I will send you PM with my email address.

My premature celebration is honor of progress! With my end-zone victory dance over, the game continues....

So, yes, I DID notice:
1) that I can't add more than one subrecord in the frmViewAllResources. The FKs only copy to one new record. I discovered this this morning when I tried to show your programming genius to a colleague.
2) that, because FKs are transferred upon clicking, a new record is produced even if no other information is entered and I simply close the window.

I worked a little bit at home last night and e-mailed the zipped DB to myself this morning. Unfortunately, the zip utility at work doesn't support the compression format I chose at home. So, I've requested assistance from our techs. As soon as I can get it unzipped, I'll "sanitize" the database according to your instructions and send it your way. Thank you for your diligence.