Solved

Why isn't a linked subform updating correctly?

Posted on 2011-03-03
13
1,092 Views
Last Modified: 2013-11-28
I have two tables that should be linked and I don't know it is no longer updating. i don't think i did anything to change it from when it was working but it has recently stopped.  

Error on subforms
As you can see:

 Error on form
it's not updating the recommendation table when a record in the observation table is selected. I am sure i am just missing something small, but thanks for the help!
0
Comment
Question by:jtovar3
[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
  • 6
  • 5
  • 2
13 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35028465
So you have two subfoms there and one of them is not synchronizing?

Make sure you are referencing (and spelling) the master/child links exactly the same
0
 

Author Comment

by:jtovar3
ID: 35028622
I am almost certain that they are the same.

Name of the Referenced Table: tbl_Observation Subform

Link Master Fields: [tbl_Observation Subform].Form![Obs_ID]
Link Child Fields: Obs_ID

so all the links look like they are correct. I had done an automatic set up and it used to work. somewhere along the line, the link stopped working and I don't know why.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35029786
<I am almost certain that they are the same.>
"Almost" Certain?...
;-)

What I tend to do is to actually create the subfom *Form* itself.
Then drag and drop it into the main form.
This way the links should be created automatically...
Even if Access gets confused, it will present you with a list of field options, so you can select from the list.
(You can select the wrong field, but  at least the syntax will be correct.)
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:jtovar3
ID: 35031180
I auto created these tables when i first started the form and they used to work. I dont know what could have changed since I havent touched them.

also, i only say almost certain because while I might be sure that they are correct, I am still an access noob. so i could very well be wrong.
0
 

Author Comment

by:jtovar3
ID: 35031438
I have a sample of my database attached. You can see that in the Audit table that the recommendations subform doesnt update when you cycle through the observations table.

is there an option that i should be using.. like an on current or afterupdate or something?
AuditTrackerVersionTest.accdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35031535
Try this as a quick test.

Create the form using the Form Wizard.

Select the Main Fields from the Parent Table
Then select the main  fields from the first Child table
Then select the main fields from the second child table.

Now this new form should work as specified.

Keep me posted...
0
 

Author Comment

by:jtovar3
ID: 35031751
I did what you had suggested. I believe this is the way that I FIRST created my Audit Form. I redid it and it once again works. I guess my question is what might have happened while i was editing the rest of my database to cause it not to work. While I GUESS i could recreate the work that I had done to the form, I wonder if there is something I can do to fix the original?
0
 

Author Comment

by:jtovar3
ID: 35031809
actually, i tried to do this on my actual database, and am encountering an error. It still wont update correctly. Nothing should be different between the databases that I am working on except for a lack of data.

Strange!
0
 
LVL 14

Expert Comment

by:pteranodon72
ID: 35032114
I don't think a subform will can react to changes on another subform. I've never seen a Link Master Field property that had a .Form!Property in it. You can get around this by creating a calculated control on the *main form* that gets its value from the Observations subform. That will let you connect the recommendations subform to an object on the main form.

Do yourself a favor and change the name of the Observations subform control to "sfcObs". Change the Reccommendations subform control to sfcRec. Then create a new textbox control on the master form. Name it txtLink. Set its control source to:
=[sfcObs].[Form]![Obs_ID]

Now, go to the properties of sfcRec. Change LinkMasterFields to
txtLink
Change LinkChildFields to
Obs_ID

Now the reccommendations will synch with navigation on the Obs subform. Once you are satisfied, you can set txtLink's Visible property to No and it will keep working without distracting.

Hope this helps,
pT72
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 35032859
pteranodon72,

<I don't think a subform will can react to changes on another subform.>
Yes, correct with just the links, it is not possible.
But if you create the 3 tiered form with the wizard, it also adds vba code to help with the sync.
Sub Form_Current()
' This code created by Form Wizard.
    Dim strParentDocName As String

    On Error Resume Next
    strParentDocName = Me.Parent.Name

    If Err <> 0 Then
        GoTo Form_Current_Exit
    Else
        On Error GoTo Form_Current_Err
        Me.Parent![Customer Orders Subform2].Requery
    End If

Form_Current_Exit:
    Exit Sub

Form_Current_Err:
    MsgBox Err.Description
    Resume Form_Current_Exit

End Sub


I have also linked subfoms the way you outlined above.
I find that it is sometimes easier to follow than the "code-link" technique the wizard uses.
So thanks for showing the asker another alternative.

;-)

Jeff
0
 
LVL 14

Expert Comment

by:pteranodon72
ID: 35036660
Ah. Good to know. I hadn't been down the three-tiered path with the wizard.

pT72
0
 

Author Closing Comment

by:jtovar3
ID: 35037201
So somewhere along the line, I had lost the VB scripts that were originally generated by the form wizard. I put it back and Voila, it worked.

Thanks for all the input everyone! I wish I could give 500 points to everyone!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35037784
;-)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 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