[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Linking a Subform to an Unbound Main form

Posted on 2006-06-19
10
Medium Priority
?
986 Views
Last Modified: 2012-06-27
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
Comment
Question by:cdmac2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16939761
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
 
LVL 16

Accepted Solution

by:
Rick_Rickards earned 2000 total points
ID: 16939999
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
 
LVL 1

Author Comment

by:cdmac2
ID: 16941284
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 16

Assisted Solution

by:Rick_Rickards
Rick_Rickards earned 2000 total points
ID: 16941535
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16942316
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
 
LVL 1

Author Comment

by:cdmac2
ID: 16947534
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16947545
"select"?
Or just type in?
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 16947560
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16947622
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16961092
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question