Popup Subform not retrieving parent form value

[solom190]

Okay this is the situation I have two forms and they have a 1:M relationship. I don't have enough space screen-wise to do a traditional "drag form to form" to create a subform so what I did was create command buttons that open the subform when I click on it.

I added a filter to the VBA that filters the results of the subform to display only records that match the linking field of the two forms. And it those and would filter but..I am having problems when I putting information into the subform. The issue is the foriegn key value(CaseNo) (in the underlying table: tblHearings, fyi. the form name is frmHearings, and table name is tblHearings) that is in subform does not get populated with the primary key value(CaseNo) of the parent form that entered before opening the subform. The field that links the two table together is: CaseNo. This is the code I used:

Private Sub Command52_Click() 
On Error GoTo Err_Command52_Click 
  
    Dim stDocName As String 
    Dim stLinkCriteria As String 
  
    stDocName = "frmHearings" 
  
    stLinkCriteria = "[CaseNo]=" & "'" & Me![CaseNo] & "'" 
    DoCmd.OpenForm stDocName, , , stLinkCriteria 
  
Exit_Command52_Click: 
    Exit Sub 
  
Err_Command52_Click: 
    MsgBox Err.Description 
    Resume Exit_Command52_Click 
  
End Sub

to filter the informtion that appears in the subform and it does filter the information if I manually type in the matching, in my case "CaseNo" in the "tblHearing " table but it shouldn't work like that. I want the foreign key field in that "TblHearing " table to be automatically populated, on the table level, with the primary key value of the parent form. Can you or anyone else lend some assistance? I have been racking my brain for a few days trying to get this and I am drawing complete blanks.

[Stewart Ross]

Hi. What Access does when the parent-child relationship is set is to automate the default value of the child field - which you have not done when opening the form manually. As there is no default value set you will indeed find you can filter but not get the 1-m relationship to work for new records.

The simple solution is to set the default value property for the CaseNo field immediately after you open the form. The syntax for this is very similar to the syntax for the filter statement you are already using. Include the following line immediately after your DoCmd.OpenForm statement:

Forms(stDocName)![CaseNo].DefaultValue = "'" & Me![CaseNo] & "'"

-Stewart

[solom190]

Stewart, First I'd like to take you for the reply, and answering my question. I put in the code that you suggested and now I am getting what I believe to be an error message when I click on the command button. What the error message says is "Object doesn't support this property or method." I tried putting the code on other command buttons and I get the same message. I'm trying to fix the problem and will more than likely be trying the rest of the day. If you or anyone could offer some insight it would be greatly appreciated.

[Stewart Ross]

Hi. Obvious question: you do have a control placed on the form you open which will implement field CaseNo, don't you? If you don't, you will need to add it to the form as you will not be able to reference the defaultvalue property of a non-existent control.

If you do have such a control in place, make sure it is named the same as the field concerned. Also, it is on the form you are opening, and not in a subform within that form, isn't it? This is important, as the syntax would be different for a subform within a main form:

Forms(stDocName)!NameOfSubform.Form![CaseNo].Defaultvalue = ...

-Stewart

[solom190]

Thanks Stewart. Before I even posted my question I figured that the issue might be with not having control on the form that held CaseNo, because the first design didn't so created two forms and was testing the code on both of them. I came across another issue with code you just provided and was just playing around with it an got it to working. I changed it to.

Forms(stDocName).Form![CaseNo].DefaultValue=...

Which is actually close to what you first suggested. It works on the form, course, the form with the control of CaseNo on it and doesn't on the one with CaseNo. Only issue now is that it won't allow you to close that subform until the "cursor" from the main form has been moved and technically "saved" before you can close the subform. But that is neither here nor there and is another issue that can be saved for next week. I appreciate all your help. Thank you.

[Stewart Ross]

Hi. Glad you got this working. Please note that Forms(name of form).Form is the same as Forms(name of form) - so the code you've settled on is indeed what I suggested in my first reply.

On the matter of saving the main form record, you can test whether or not the record has been changed using the form's Dirty property before you open the subform:

If me.Dirty then me.Dirty = False

This simple line of code actually saves the record if it has been changed. This is necessary if you have created a new one-side record and want to add many-side records that correspond, because until you save the record you will not have a match between the one-side and the many-side keys (as the one-side primary key will not actually exist as far as the DB is concerned).

-Stewart

[jbrumbau]

Though VBA code works in form view, it actually doesn't work when you do

DoCmd.RunCommand acCmdPrint

Instead, I took the unbound control of interest and referenced my parent control txtSpecCaption by doing:
Default Value = [Parent].[txtSpecCaption]
No code required and it works like a charm.