Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Linking a Subform to an Unbound Main form

Posted on 2006-06-19
10
Medium Priority
?
1,015 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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

810 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