By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,146 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Popup Subform not retrieving parent form value

P: 5
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command52_Click() 
  2. On Error GoTo Err_Command52_Click 
  3.  
  4.     Dim stDocName As String 
  5.     Dim stLinkCriteria As String 
  6.  
  7.     stDocName = "frmHearings" 
  8.  
  9.     stLinkCriteria = "[CaseNo]=" & "'" & Me![CaseNo] & "'" 
  10.     DoCmd.OpenForm stDocName, , , stLinkCriteria 
  11.  
  12. Exit_Command52_Click: 
  13.     Exit Sub 
  14.  
  15. Err_Command52_Click: 
  16.     MsgBox Err.Description 
  17.     Resume Exit_Command52_Click 
  18.  
  19. End Sub 
  20.  
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.
Oct 30 '08 #1
Share this Question
Share on Google+
6 Replies


Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. Forms(stDocName)![CaseNo].DefaultValue = "'" & Me![CaseNo] & "'"
-Stewart
Oct 30 '08 #2

P: 5
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.
Oct 31 '08 #3

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. Forms(stDocName)!NameOfSubform.Form![CaseNo].Defaultvalue = ...
-Stewart
Oct 31 '08 #4

P: 5
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.

Expand|Select|Wrap|Line Numbers
  1. Forms(stDocName).Form![CaseNo].DefaultValue=...
  2.  
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.
Oct 31 '08 #5

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. 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
Oct 31 '08 #6

P: 52
Though VBA code works in form view, it actually doesn't work when you do
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 3 '11 #7

Post your reply

Sign in to post your reply or Sign up for a free account.