[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1061
  • Last Modified:

Linking a Subform to an Unbound Main form

Hello...

I am working in MS Access 2000, and I am trying to get a subForm (frmPublication) to link to the Main Form (frmAddProject).

frmAddProject is an unbound form, and it has a combobox (cmbPubID) that selects the publication ID.  On the AfterUpdate of cmbPubID, I would like the subform to change the selected record to match the one in cmbPubID.

When I try to click "Link Master Fields" or "Link Child Fields" on the subform, I get an error:
"Can't build a link between unbound forms."

Thanks!
MikeMc
0
cdmac2
Asked:
cdmac2
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think you can make the frmAddProject a bound form...
just create a dummy table with a field for holding the value of cmbPubID (to make it bound)....
0
 
Rick_RickardsCommented:
Since your parent form is unbound the easiest way to bind these forms together is to use a WHERE clause in the RecordSource of the Sub Form and bind it to the combo box of the parent form.

eg.  Imagine you have a parent form named "frmMyParentForm" with a combo box named "cboMyComboBox" and a sub form named "frmMySubForm"

1) Set the record source of the sub form to be bound to the parent forms Combo Box...  The query would look something like this (changing table and field names to match your situation)...

           SELECT tblTest.* FROM tblTest WHERE tblTest.TetID=[Forms]![frmMyParentForm]![cboMyComboBox];

2) On the combo Box's AfterUpdate Event requery the sub form any time the combo box is updated.

           Private Sub cboMyComboBox_AfterUpdate()
               Me.frmMySubForm.Form.Requery
           End Sub

3)  As long as you don't need to add records to the sub form you can stop here.  If not you might want to add one more piece of code to the sub form's BeforeInsert event to link up the related field of the matching the combo box.  It might look like this (again updating the names of controls and fields to match your situation).

           Private Sub Form_BeforeInsert(Cancel As Integer)
               Dim varKeyID As Variant
               varKeyID = Me.Parent.Form.Controls("cboMyComboBox").Value
               Cancel = IsNull(varKeyID)
               If Not Cancel Then Me.TestID.Value = varKeyID
           End Sub

****************************

Good Luck

Rick
0
 
cdmac2Author Commented:
Hey Angel... I tried you idea, but no luck.

Rick, I like your idea, the problem is that the subform does not have a "Record Source" property, only "Source Object"

Thanks,
Mike
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Rick_RickardsCommented:
The sub form control does indeed only have a "Source Object" but I was actually refering the sub form itself not the control (although this may sound like hair splitting they are actually very different).

If you open the sub form by itself (or if in 2K you can right click a small square in the upper left hand corner of the sub form and select properties) you'll find that it does in fact have a "Record Source", (it appears under the data tab).

Rick
0
 
Leigh PurvisDatabase DeveloperCommented:
Hi

Not to say that limiting the Where clause isn't a good method (I too like to make sure the data pulled over is efficient as possible) but there's no reason you shouldn't be able to set up Linked fields between a bound subform and an unbound main form.

Did you select the name of the Combo as the Master Link Field.
And the relevant child *field* in the subform.
0
 
cdmac2Author Commented:
Got it Rick Thanks.

LPurvis, anytime I try to select a Child Field in the Subform, I get the "Can't build a link between unbound forms." Error.

Thanks!!
MikeMc
0
 
Leigh PurvisDatabase DeveloperCommented:
"select"?
Or just type in?
0
 
Rick_RickardsCommented:
The reason that you can't link the forms using the .LinkChildFields and .LinkParentFields properties of the sub form control is because the Parent form is UnBound.  UnBound means that it doesn't have a record source and the fact that it doesn't have a recordsource also means that it doesn't have fields.  The .LinkChildFields and .LinkParentFields properties look to bind to fields.  While your sub form has fields (as it is bound) the fact that the parent form does not means you'll be unable to link the forms together using the .LinkChildFields and .LinkParentFields properties since they look for fields (not controls).  

In any event I'm pleased to hear it's working for you now.  Best wishes to all.

Rick
0
 
Leigh PurvisDatabase DeveloperCommented:
Nah - on the parent form it looks for a control.  I promise you.
It will accept the theoretical underlying field "control" of a bound parent form if no so named control exists, and hence appear that it wants a field.  But controls on the parent form take precedence.

Hence it's fully possible to have an unbound parent form.

I described as much in my earlier post.
(And I do try not to tell porkies ;-)
0
 
Leigh PurvisDatabase DeveloperCommented:
No progress on this?

Naturally if you have a working solution you're always tempted not to try - but just for your future work... and knowledge - to save time etc - it is possible.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now