?
Solved

Synchronizing two subforms in MS Access

Posted on 2007-10-10
19
Medium Priority
?
4,448 Views
Last Modified: 2012-05-05
I have two subforms that I am trying to synchronize and it's not happening. I am wondering if you could help please.

Subform 1 is called frmCampaignsSub (continuous form)
Subform 2 is called frmCampaignsDetSub (single form)

The PK field CP_ID is common to both record sources

When a user clicks or selects a record in Subform 1, I want Subform 2 to show the details accordingly.

Here are my link settings in Subform2 as they related to Subform 1:

Link Child Fields = cp_id
Link Master Fields = Forms!frmMain!frmCampaignsSub.Form!cp_id

So far, Subform 2 is synchronizing ONLY with the first record/row of Subform 1. However, if I move from record to record on Subform 1, Subform 2 is not synchronizing.

Any clues? I would appreciate you nailing any coding syntax using the field and key values I provide in my text.

Thank you.
0
Comment
Question by:htamraz1
  • 7
  • 4
  • 4
  • +2
19 Comments
 
LVL 13

Expert Comment

by:adraughn
ID: 20049740
you need to requery the subform
0
 

Author Comment

by:htamraz1
ID: 20050018
I tried. Not working. Have some syntax in mind?

Parent form = frmMain
Subform 2 (the one to requery) = frmCampaignsDetSub

I am assuming I need to initiate the requery from Subform 1. Right?

I tried doing this on the Current Event as follows:

forms!frmMain!frmCampaignsDetSub.Requery

Did NOT work!!!

0
 
LVL 13

Expert Comment

by:adraughn
ID: 20050039
how are your users navigating thru records? add this to the click event for the button:

me.subformname.requery
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 13

Expert Comment

by:adraughn
ID: 20050064
putting this on the current event should work though:

[SubformName].Requery
0
 

Author Comment

by:htamraz1
ID: 20050112
It seems logical, but I get an error

Object required (Error 424)

I used SubformName].Requery as follows:

[frmCampaignsDetSub].Requery

To answer your other questions, users will be scrolling down a continue form in Subform 1 and as they hit each row, Subform 2 should synchronize according.
0
 
LVL 13

Expert Comment

by:adraughn
ID: 20050129
me.[frmCampaignsDetSub].Requery
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20050135
if the setting for the master/child links are correct, no need for the requery.
check the record source of the subforms.

also, what is the set up?

both subform in main form
Main form
   subform1
   subform2

or subform2 in subform1
Main form
   subform1
        subform2

0
 
LVL 42

Expert Comment

by:dqmq
ID: 20050468
Subform is continuous, which may not have a subform.  That, I expect, is the problem.  The the child is not a subform of the master.
0
 

Author Comment

by:htamraz1
ID: 20050653
capricorn1

It is the former example

both subform in main form
Main form
   subform1
   subform2
0
 

Author Comment

by:htamraz1
ID: 20050669
adraughn

Your last suggestion did not work...Please keep in mind what I noted to capricon1 about setup. You are referencing subform2 from subform1 using 'Me', which it did not understand because the subform2 object lives outside subform1.

both subform in main form
Main form
   subform1
   subform2
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20050678
what about the record source of the subforms?
0
 

Author Comment

by:htamraz1
ID: 20050703
dqmq

I understand your logic here, but why should this limit my scenario. The two subforms have related records that are tied by CP_ID. All I want to do is to select a campaing from this continuous form (i.e. Subform 1) and have the corresponding details for the campaign show in Subform 2. I assumed their common link CP_ID would do the trick and it does indeed, but only for the 1st record of Subform 1. As I go down my continuous Subform 1, I want the corresponding detail to show in Subform 2.

0
 

Author Comment

by:htamraz1
ID: 20050759
capricorn1,

The record source of the subforms is a SQL query both queries have CP_ID, which is a unique identifier.

I am not sure if I answered your question?


0
 

Author Comment

by:htamraz1
ID: 20050831
Capricorn1

I wanted to clarify the following further

both subform in main form
Main form
   subform1 (this is a continuous form)
   subform2 (single view form)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20051308
check this thing too.

in design view of frmMain
click on the outer edge of subform1 hit F4
click on the Data tab  check take note of what is on Source Object
click on the Other tab, is the Name the same as the one in the Source Object?

do the same thing for subform2
0
 
LVL 42

Accepted Solution

by:
dqmq earned 1200 total points
ID: 20058986
>I understand your logic here, but why should this limit my scenario.


I don't know the "why" of it, but let me try to clarify the problem.  For the master-child linking to work, both the master form and the child form must be bound. Furthermore, in my experience, the child form must be also be a subform of the master form.

The master-child links are not really a property of the child form.  They are a property of the subform/subreport control that references the child form as a source object.  Since, that control belongs to the master form, the master-side of the linkage can only refer to fields to which the master form is bound. The child-side of the linkage can only refer to bound fields of source object of that control.

To see what I mean, go the the data properties of the subform control where master-child linkage is entered.  Select either Child Field or Master Fields and double click the ... to popup the dialog that helps you complete those fields.  What you will see are dropdowns for the acceptable linkage fields. You might even get an error message that one of your forms is not bound.   I'm fairly sure you will not see that you can link two subforms of the same master form.

One solution, of course is to keep the forms in sync manually.  You would do that in the On Current event of the continuous form, so that whenever that form changes to a new record, you can reposition the other subform.

Off the top of my head, I don't remember the exact code, but it would be something like this:

    Dim rst As Recordset
'clone the recordset you want to reposition
    Set rst = me.parent.YOURCHILDSUBFORM.RecordsetClone
    rst.FindFirst  "YourID = " &        'reposition the clone
        If rst.NoMatch Then
            MsgBox "Record not found"
        Else
            me.parent.YOURCHILDSUBFORM.Bookmark = rst.Bookmark  'reposition the form
        End If
    rst.Close








 



0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 800 total points
ID: 20059047
i agree with dqmq

if the record source of both subform are independent from the record source of the main form, besides setting the master/child links for the two subform you will need to requery the subform(frmCampaignsDetSub)  in the current event of subform(frmCampaignsSub)...


Private Sub Form_Current()
Forms!frmMain!frmCampaignsDetSub.Requery
End Sub
0
 
LVL 10

Expert Comment

by:conagraman
ID: 33101853
it is possible to link two unbound subforms in ms access. see below.


create a new form
insert a text box -- name it "txtLink"
on the form insert two sub-forms

lets name them: subformA  and subformB


subformA will be your "main" display while subformB will contain a list of the records with a brief description/pic.

(make sure you set the control sources to your subforms)
also set subformB to be a continuous form. subformA should be set at single view

ok this gets a little hairy--

right click on the text box you created - set the control source to one of the fields in subformB (for example the ID/autogen number field.) use one that has unique data.
(use the expression builder to do this) it will end up being something like
=me.subformB!forms![yourfield]

after you have done that you will know you did it right if when you click on a record in subformB the text/number in your textbox will change.

now go to the properties of subformA
here we will set the parent and child fields.         *(do not set these in subformB)

you have to type these in manually if you click on the "..." in the fields an error will come up saying you cant do it.

in the parent block type in the name of your textbox we made earlier.   "txtLink"
the data has to match so
because the "txtlink" controlsource is set to the ID field in subformB
the child block should be the id field in subformA  which should be something like "ID"

following these steps will make you able to link two "unbound forms" and when you click on a record in subfromB subformA will goto that record.


i hope that wasn't too much info let me know if you need clarity. i hope this isn't as clear as mudd. i do not have access to ms access on this computer but i will be at work in the morning and if you need i can clarify.

hope this helps

good luck
0
 
LVL 10

Expert Comment

by:conagraman
ID: 33102225
this works!!!!!!  this is what you are looking for


create a new form
insert a text box -- name it "txtLink"
on the form insert two sub-forms

lets name them: subformA  and subformB


subformA will be your "main" display while subformB will contain a list of the records with a brief description/pic.

(make sure you set the control sources to your subforms)
also set subformB to be a continuous form. subformA should be set at single view

ok this gets a little hairy--

right click on the text box you created - set the control source to one of the fields in subformB (for example the ID/autogen number field.) use one that has unique data.
(use the expression builder to do this) it will end up being something like
=me.subformB!forms![yourfield]

after you have done that you will know you did it right if when you click on a record in subformB the text/number in your textbox will change.

now go to the properties of subformA
here we will set the parent and child fields.         *(do not set these in subformB)

you have to type these in manually if you click on the "..." in the fields an error will come up saying you cant do it.

in the parent block type in the name of your textbox we made earlier.   "txtLink"
the data has to match so
because the "txtlink" controlsource is set to the ID field in subformB
the child block should be the id field in subformA  which should be something like "ID"

following these steps will make you able to link two "unbound forms" and when you click on a record in subfromB subformA will goto that record.


i hope that wasnt too much info let me know if you need clarity. i hope this isnt as clear as mudd. i do not have access to ms access on this computer but i will be at work in the morning and if you need i can clarify.

hope this helps

good luck
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

850 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