Solved

Linking a Subform to an Unbound Main form

Posted on 2006-06-19
10
877 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 142

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 500 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
 
LVL 16

Assisted Solution

by:Rick_Rickards
Rick_Rickards earned 500 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now