Datasheet to Open Child Form

**FishVal** · Nov 26 '07, 05:14 PM

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.
[code=sql]
SELECT tblProjects.*, iif(isnull(DLoo kUp("keyProject ", "tblResourc es", "keyProject =" & tProjects.keyPr oject)),Null,"+ ") as txtPlus
FROM tblProjects;
[/code]
Or returning a number of related resources, like the following.
[code=sql]
SELECT tblProjects.*, DCount("keyProj ect", "tblResourc es", "keyProject =" & tProjects.keyPr oject) as lngProjectResou rcesCount
FROM tblProjects;
[/code]
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

**BASSPU03** · Nov 26 '07, 06:41 PM

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 "sfrmProjec ts" over into "frmViewAllReso urces." However, since frmViewAllResou rces 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 (frmViewAllReso urces) pops up with "dirty" records. If I click on another Project ("Environmen tal Clean-Up"), the same frmViewAllResou rces 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), frmViewAllResou rces pops up as a data entry form.

The concept is generally fine, but I want the data entry frmViewAllResou rces 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 frmViewAllResou rces 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 (frmViewAllReso urces) by linking the forms via the form wizard:

Code:

Private Sub FilterChildForm()

    If Me.NewRecord Then
        Forms![frmViewAllResources].DataEntry = True
    Else
        Forms![frmViewAllResources].Filter = "[FiscalYear] = " & """" & Me.[FiscalYear] & """" & " AND [BulkObligation] = " & """" & Me.[BulkObligation] & """" & " AND [Project] = " & """" & Me.[Project] & """"
        Forms![frmViewAllResources].FilterOn = True
    End If

End Sub

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.NewReco rd" 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 frmViewAllResou rces 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

**FishVal** · Nov 26 '07, 07:09 PM

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

Code:

[i]Field; Type; IndexInfo[/i]
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Regards,
Fish

**BASSPU03** · Nov 26 '07, 08:12 PM

Originally posted by FishVal

[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

Code:

[i]Field; Type; IndexInfo[/i]
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Table Name=tblProject s

Code:

[i]Field; Type; IndexInfo[/i]
AutoNumber; AutoNumber
Date; Date/Time
ProjectCode; Text
FiscalYear; Number (Long Integer); PK (concatenated)
BulkObligation; Text; PK (concatenated)
Project; Text; PK (concatenated)
Subobligated; Currency
Liquidated; Currency
Balance; Currency

Table Name=tblViewAll Resources

Code:

[i]Field; Type; IndexInfo[/i]
AutoNumber; AutoNumber; PK
Quantity; Number
FiscalYear; Number (Long Integer); FK
BulkObligation; Text; FK
Project; Text; FK
Resource; Text
Type; Text
GeneralDescription; Text
Subobligated; Currency
Liquidated; Currency

I hope this is right. Thank you.

**FishVal** · Nov 26 '07, 10:34 PM

Hi, there.

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

Pay attention to:

[frmProjects].RecordSource
[code=sql]
SELECT tblProjects.*, IIf(IsNull(DLoo kUp("AutoNumber ","tblViewAllRe sources","Fisca lYear=" & nz(tblProjects. FiscalYear,0) & " AND BulkObligation= '" & nz(tblProjects. BulkObligation, 0) & "' AND Project='" & nz(tblProjects. Project,0) & "'")),Null, "+") AS txtPlus FROM tblProjects;
[/code]
[txtPlus] is bound to the form's [txtPlus] textbox
[frmProject]'s DblClick event handler
[code=vb]
Private Sub Form_DblClick(C ancel As Integer)

Dim strFilter As String

With Me
If .NewRecord Then Exit Sub
strFilter = "FiscalYear =" & Nz(.FiscalYear, 0) & _
" AND BulkObligation= '" & Nz(.BulkObligat ion, 0) & _
"' AND Project='" & Nz(.Project, 0) & "'"
DoCmd.OpenForm "frmViewAllReso urces", acFormDS, , strFilter
End With

With Forms!frmViewAl lresources
!FiscalYear.Def aultValue = Me.FiscalYear
!BulkObligation .DefaultValue = Me.BulkObligati on
!Project.Defaul tValue = Me.Project
.Modal = True
End With

End Sub
[/code]

Attached Files

SeparateSbf.zip (19.8 KB, 190 views)

**BASSPU03** · Nov 27 '07, 02:22 AM

Originally posted by FishVal

[code=vb]
Private Sub Form_DblClick(C ancel As Integer)

Dim strFilter As String

With Me
If .NewRecord Then Exit Sub
strFilter = "FiscalYear =" & Nz(.FiscalYear, 0) & _
" AND BulkObligation= '" & Nz(.BulkObligat ion, 0) & _
"' AND Project='" & Nz(.Project, 0) & "'"
DoCmd.OpenForm "frmViewAllReso urces", acFormDS, , strFilter
End With

With Forms!frmViewAl lresources
!FiscalYear.Def aultValue = Me.FiscalYear
!BulkObligation .DefaultValue = Me.BulkObligati on
!Project.Defaul tValue = Me.Project
.Modal = True
End With

End Sub
[/code][/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 frmViewAllResou rces 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:

Code:

!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!

**FishVal** · Nov 27 '07, 09:07 AM

Originally posted by BASSPU03

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 frmViewAllResou rces 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:

Code:

!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.Def aultValue

**BASSPU03** · Nov 27 '07, 01:34 PM

Originally posted by FishVal

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.Def aultValue

1) I checked that each corresponding table had "FiscalYear " in it. Each does. "frmViewAllReso urces" 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:

Code:

? Me.FiscalYear
? !FiscalYear
? !FiscalYear.DefaultValue

...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

**FishVal** · Nov 27 '07, 06:13 PM

Originally posted by BASSPU03

1) I checked that each corresponding table had "FiscalYear " in it. Each does. "frmViewAllReso urces" 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 [frmViewAllResou rces] 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:

Code:

? Me.FiscalYear
? !FiscalYear
? !FiscalYear.DefaultValue

...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

**BASSPU03** · Nov 27 '07, 06:30 PM

Originally posted by FishVal

Both forms [frmProjects] and [frmViewAllResou rces] 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 frmViewAllResou rces. 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.

**FishVal** · Nov 27 '07, 06:49 PM

Originally posted by BASSPU03

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 frmViewAllResou rces.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 [tblViewAllResou rces], 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.

**BASSPU03** · Nov 27 '07, 07:02 PM

Originally posted by FishVal

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

Nevermind! I got it to work! I deleted the ".defaultva lue" from each line and voilà! Now the frmViewAllResou rces 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 frmViewAllResou rces?

**FishVal** · Nov 28 '07, 11:54 AM

Hi, BASSPU.

Originally posted by BASSPU03

Nevermind! I got it to work! I deleted the ".defaultva lue" from each line and voilà! Now the frmViewAllResou rces 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.Default Value 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.

**BASSPU03** · Nov 28 '07, 02:13 PM

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 frmViewAllResou rces. 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.

Datasheet to Open Child Form

Datasheet to Open Child Form

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment