Refresh a form when selecting a record in Microsoft Access

I have a form with the date from 3 linked tables on it.

tblPersonnel  (This shows each individual person)
tblAccomodations (This is a linked table that shows each person's accommodations, there can be more than one per person, and yes, I know the table name is spelled incorrectly)
tbl Correspondence (This is a linked table that shows each accommodation's correspondence, there can be more than one per accommodation)

They're all on a form with the Accommodations, and Correspondence displayed in spreadsheet-style format.  When I select a person's name, it shows a list of all their accommodations, and under that, a list of all the selected accommodation's correspondence.

My problem is this:  Upon selecting an individual, the selected accommodation is the first in the list (that is fine), so the correspondence subform displays all the correspondence for that accommodation.  (that is also fine.)

What isn't fine, is that if I select a different row to look at a different accommodation, the correspondence subform doesn't update to show the correspondence for that accommodation.  It continues to show the correspondence for the previous accommodation.

Now, if I then select one of the rows of the corresponcence subform, and hit F5, it will display the correct set of correspondence for that accommodation.

I could be wrong, but I'm pretty sure that when I first made the database, it automatically displayed the correct correspondence when I selected different accommodations.  So, I just need to get the sfmCorrespondence to refresh whenever I select a different record in sfmAccomodations.

Or find a way to fix it, if it's not working properly.  (Note that when I select a different individual, the accommodation list updates to the new person, so I'm not quite sure what the difference should be.)
DontrembAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GRayLCommented:
It sounds like you should have Personnel on the main form, Accomodations on sub-form, and Correspondence on a sub-sub-form.  Each sub-form must have the LinkMasterFields and LinkChildFields properly completed.  That being the case,  selecting any person on the main form would result in all Accomodations for that Person on subform1 and all Correspondence relating to that person and the first Accomodation on subform2.  Now if you click to a different Accomodation on subform1, the Correspondence on subform2 will change accordingly.
0
DontrembAuthor Commented:
Hrm...  Where do I find this, "LinkMasterFields" option?
0
DontrembAuthor Commented:
Ok, I found the fields for Master and Child, but they seem to have the correct information in them.

Note, that technically, it's working correctly, I just have to manually refresh whenever I select a new row in Accommodations
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

GRayLCommented:
are the subform source objects a form or a table/query?  If a form, in the you can set use this code in the On Current event of the form in subform1:

Private Sub frmAccomodations_Current()
  Me.Form.subform2.requery
End Sub
0
DontrembAuthor Commented:
Ok, well, that didn't work too well.  Either nothing happened, or it gave me an error.

I tried:

Private Sub sfmAccommodations_Current()
   Me.Form.sfmCorrespondence.requery
End Sub

**That didn't do anything because I had to actually change the "On Current" part of the Sub to that version, and it moved out of that event.**

Private Sub Form_Current()
  Me.Form.sfmCorrespondence.Requery
End Sub

**I also tried this, but it also gave me an error.**
0
GRayLCommented:
But are the SourceObjects, Forms or Table/Querys?
0
DontrembAuthor Commented:
Hrm... I have the 3 tables:

tblPersonnel
tblAccomodations
tblCorrespondence

then 1 form and 2 subforms

frmPersonnel                          (source=tblPersonnel)
sfmAccomodations                 (source=tblAccomodations)
sfmCorrespondence               (source=tblCorrespondence)

On the form frmPersonnel, I have the two subforms listed there.

I hope that answers your question.
0
GRayLCommented:
The subform as a control has only two events Enter and Exit.  Because the source is a table, you have to use those events:

Private Sub sfmAccomodations_Enter()
  Me.Form!sfmCorrespondence.Requery
End Sub

Private Sub sfmAccomodations_Exit()
  Me.Form!sfmCorresponcence.Requery
End Sub

If the source were an embedded form, then you could use the Current() event of the form to trigger the requery.  BTW I'm not certain if it should be Requery or Refresh.  Use what works ;-)
0
GRayLCommented:
BTW can you show me a screen shot of the whole form.  I'm not clear as to how you can have a subform within a subform when the source object is a table.  They would both be datasheetviews??   I have just reviewed the thread and see that sfmCorrespondence is not a subform in sfmAccomodations.  Being separate subforms,  you need the Exit event of sfmAccomodations to trigger the refresh/requery.  For
0
DontrembAuthor Commented:
I don't think OnExit or OnEnter will work, because I need it to update the Correspondence sfm whenever I select a different record.

I may be missing something.  I know parts of access pretty well, but since I'm self-taught, I often find I'm missing some very basic stuff.

It would be best if you assume I know nothing.
Screen.gif
0
swedishtongueCommented:
first is the "search in" combo box named differently than the field you are trying to link to? let me explain:
if your combo box is named "Combo45" then you need to set the master link to combo45. because you are linking to the combo box not the field. make sense?
0
DontrembAuthor Commented:
I think that makes sense.  However, I'm not having a problem with my combo box.  

If you look at the picture In the sfm "Reasonable Accommodation Requests"  The second record, "Ergonomic Chair" is selected (noted by the black arrow.)

So, in the sfm below that, "Correspondence" it is displaying all of the records associated with "Ergonomic Chair"

When I select "Anti-Glare Computer Screen" the Correspondence sfm doesn't update anymore (though it used to)  It will still show all of these records.  To get to to show the records associated with "Anti-Glare Computer Screen", I have to select one of the records in Correspondence, and hit F5, then it will show all the records associated.

I hope this clears it up.
0
swedishtongueCommented:
i realize you are not having a problem with the combo box but if your subform is linked to the field and not the combo box you will have a problem with the subform not refreshing
0
GRayLCommented:
Now that we realize that Accomodations have nothing to do with Accomodations ;-) - be back later if not resolved.
0
DontrembAuthor Commented:
@GRayL, I'm not sure what your last comment meants at all.  "Accommodations has nothing to do with Accommodations?"  I'm sorry, would it be possible for you to explain that?

@swedishtongue.  Ah, I guess I didn't understand, then. tblPersonnel has a one to many relationship with tblAccommodations, which has a one to many relationship with tblCorrespondence.

So, unless I'm missing something, which I probably am, my sfm is not linked to the combo box in any way at all.  The combo box is linked to the tblPersonnel ID
0
GRayLCommented:
This is the table layout you need at a minimum:

tblEmployees
=========
EmpID - pk
LName - text
FName - text

tblAccomodations
============
AccomodationID - pk - autonumber
EmpID - fk - from tblEmployees
Description - text
etc.

tblCorrespondence
=============
CorrID
AccomodationID - fk - from tblAccomodations
CorrDesc
CorrDate
etc.

cboSearch, the combo box is based on tblEmployees.  sfmReasonableAccommodationRequests is based on the form based on tblAccommodations.  The LinkMasterFields property of the subform is cboSearch.  sfmCorrespondence is based on the form based on tblCorrespondence.  The LinkMasterFields property of the form is the field AccomodationID in sfmReasonableAccommodationRequests.

In the beginning I thought tblAccommodations was going to be a list of rooms that may be assigned to an employee for various events.  When I saw Ergonomic Chair as an accommodation, I knew you were making accommodations ;-)
0
DontrembAuthor Commented:
Are you saying that my LinkMasterFields property of my sfmAccommodations has to be my cboSearch?

How do I even do that?
0
DontrembAuthor Commented:
Perhaps I should add this:

When I use my combo box to select an employee, it properly displays that employee, the correct information from the sfmAccommodations, and the correct information for sfmCorrespondence.  This all updates accurately.

The only thing that is wrong, is if I have 2 records in sfmAccommodations, and select (with my mouse or tab) the second record, sfmCorrespondence doesn't update.

0
DontrembAuthor Commented:
Ok.  I changed sfmAccommodations LinkMasterFields to Combo16, and it still works (like it worked before)

On sfmCorrespondence, my LinkMasterFields is set to  [sfmAccomodations].Form![ID]

I don't know what else to do.
0
DontrembAuthor Commented:
Just checking back to see if there's been any more help.  
0
swedishtongueCommented:

i am attaching a view of a main form and subform linking , hopfully you can see that there are two forms, one titled FrmStart the other frmMain(couseling issues). the frmMain resides within frmStart and is linked to a field "Dr Assigned To" cboAssignedTo. FrmMain has the same field but is titled AssignedTo.

hopefully this helps

main-and-SubForm.bmp
0
GRayLCommented:
I've run into an inexplicable.  I'll get back to you tomorrow.
0
DontrembAuthor Commented:
@swedishtongue.

I did try that.  And if you'll notice I'm not having a problem with my first subform.  Whenever I select a record in the main form, sfmAccommodations updates just fine.

It's sfmCorrespondence that doesn't refresh its records when I click on a record in sfmAccommodations.
0
swedishtongueCommented:
correct but linking is the same.

if one subform is referencing a field in another subform then the master field would be
forms!mainform.form!subformfield, if it is referencing the same field then you would reference it the way i indicated before.

other than that i am at a loss to know why it isnt working.

0
GRayLCommented:
Dontremb:  Any way you can pare down the mdb to the min required to support the question, cull the records down to provide a few options in the combo box, sfmAccommodations, and sfmCorrespondence, then do a Compact & Repair, and upload the result using Attach File below?
0
GRayLCommented:
Better yet, have a look at the Customer Orders form in Northwind.mdb, which is the sample mdb that came with your version of Access.  It has precisely the autoupdate function in sfm2 when a different selection is made in sfm1.  
0
DontrembAuthor Commented:
I may be being stupid, but I'm pretty sure I have all the Master and Child fields set up correctly... As I've already said:

"On sfmCorrespondence, my LinkMasterFields is set to  [sfmAccomodations].Form![ID]"

I want to reitterate that it DOES work.  And it USED to display the correct records in sfmCorrespondence whenever I clicked on a record in sfmAccomodations.  It suddenly stopped, and now, to see the correct records, I have to hit F5.  

Also, GRayL, my northwinds doesn't have anything like this.  I have MS office 2007, I don't know if that makes a difference.
0
DontrembAuthor Commented:
Ok, I made a real quick database that is set up the exact same way that my real one is made.  You can see that this one works perfectly.  It works exactly the same as my current one used to work, and now suddenly no longer does.  Hopefully this will clear up some confusion, since I don't think I'm properly addressing my problem.

Thanks again for all the help!  (Waiting for it to upload, it looks stuck.)

Ah, there we go.
Show.mdb
0
swedishtongueCommented:
sorry to say but you are not linking the information correctly. here it all it is linked correctly please note that your combo field is named Combo9 and you were linking it to the field ID so it wasnt updating without the f9
Show.mdb
0
DontrembAuthor Commented:
@Swedishtongue:

I'm sorry to say that you're incorrect.  I've mentioned it twice now, that I have tried it both ways.  If you looked at the database I sent posted, before you changed it, you'll notice that it worked fine before linking it to the combo box.

I honestly don't even know why you would link it to the combo box anyway (though I did try, to see if it worked.), since the combo box was created on the form AFTER it was set up and working.  

To sumarize again:  The database I posted is exactly how my first one started, and worked exactly the same.  For some reason, it no longer works without refreshing.
0
swedishtongueCommented:
did you check my database? because when i ran it your way i had to refresh, when i ran it my way i did not.
0
swedishtongueCommented:
sorry i know you referred to this before but i always assumed that the ID was in the sub form. i fixed the id and the LName on the main form so that it will change with the change of the combo box


Show.mdb
0
GRayLCommented:
In the Change event of the combo box insert this code.  It works well for me!


Private Sub Combo9_Change()
  Me.Filter = "ID = " & Me!Combo9
  Me.FilterOn = True
End Sub

Now when ever I click on a second item in sfm1, it updates sfm2.  
0
DontrembAuthor Commented:
That's very odd... Yes, I checked your database.  It worked correctly for me, (and my version still does), and so does yours.

The problem with your new one, is that you can't input any data.  Linking everything to the combo box is a personal preference, and doesn't not accomplish what I would need it to do.  It might be best to just pretend the combo box doesn't exist.  (Or simply remove it.)  The combo box is completely inconsequential to this problem.
0
DontrembAuthor Commented:
Perhaps something is wrong between versions...  Does the database I posted not work exactly as I have posted it?

Specifically, when I open the database that I posted on this thread...  I can click on "Desk" on sfmAccommodations, and the sfmCorrespondence automatically changes.  If I click back on "Chair" sfmCorrespondence automatically changes back.

Does this database not work the same way for you guys?
0
DontrembAuthor Commented:
And Gray, I tried your suggestion, and it didn't help in the least.  (Except that it filtered my records, which I don't really want to do.  )
0
GRayLCommented:
I'll give you the modified file back.
Show.mdb
0
DontrembAuthor Commented:
It appears you posted your message while I was posting both of mine.

I don't want to filter my form.  And I tried this on my  main form (the one that's actually not working)  and it doesn't work at all.
Does the database I posted not work exactly as I have posted it?

Specifically, when I open the database that I posted on this thread...  I can click on "Desk" on sfmAccommodations, and the sfmCorrespondence automatically changes.  If I click back on "Chair" sfmCorrespondence automatically changes back.

Does this database not work the same way for you guys?
0
GRayLCommented:
I open your mdb, open form frmPersonnel.  ID = 1, LName = Doe.  click the combo, and select Divorak, nothing happens.  I was able to get the name to change to Divorak using the code I gave you.

With LName = Doe, when I click record 2 in Accommodation, the Correspondence choices changed.  Clicking back to record 1, Correspondence reset.  That part appears to be working normally.   After inserting my code, it all worked as I thought it should.
0
DontrembAuthor Commented:
Well, that's not how it functions for me.

I open my mdb, open form frmPersonnel.  ID = 1, LName = Doe.  click the combo, and select Divorak, it changes to Divorak.

Remember guys, I'm not having a problem with the combo box.  As I mentioned earlier, it would be best if we pretended there was no combo box there.   It has absolutely nothing to do with my problem.
0
GRayLCommented:
Ignoring the combo box and using the record selector at the bottom I am able to change personnel.  Similarly when I change the record highlighted in sfmAccommodation I get the corrrect response in sfmCorrespondence, ie.  No Problem.

However, I noticed that you have the same name for your subform controls as you do for the forms embedded in the controls.  That may be a problem.  I suggest the controls be named sfmAccommodation and sfmCorrespondence and the forms be renamed frmAccommodation, and frmCorrespondence.  There is really no need for a sfm identifier in the form name as now there is no doubt that the Source Object is indeed a saved form.  I know the form wizard does this but it is bad practice.  Now when you refer to the LinkMasterField in sfmCorrespondence it should say sfmAccomodation.ID - right?
0
DontrembAuthor Commented:
Yes...  That's what it says.
0
GRayLCommented:
So I guess you made the name changes and you still have a problem?
0
DontrembAuthor Commented:
Yup, I still have the problem.  No changes.
0
GRayLCommented:
I have gone back to the mdb and it works as advertised.  When I change the ID in the Accomodation form the Correspondence subform behaves properly.  When I use the record selector at the bottom to change the person, all works fine.  You may have a 'busted' mdb.

I suggest you first try a compact and repair.  If the problem persists, create an new mdb, and then copy over all the objects from you old mdb to the new - using File, Get External Data.  
0
DontrembAuthor Commented:
Ok... I don't think I'm getting it.  I certainly appreciate all your help.  I've created a copy of my database, Saved it as a 2003 Access file so it will upload here, and removed all data, except an example record I put in there.

To sum it all up again:  I just want to be able to click on an Accommodation and have the Correspondence display the Correspondence that is associated.

Thanks again for your help.
2003-RA-Database.mdb
0
DontrembAuthor Commented:
Oh, to make sure that I'm not ignoring you...  I tried a compact and repair, and created a new DB and imported all objects.  Still not working the way it used to, or the way the DB I posted here before works.
0
GRayLCommented:
I'm working on it, making a lot of changes to names so I don't get confused.  Hopefully, sometime tomorrow.
0
DontrembAuthor Commented:
You're the one helping me...  So take your time.  If you can fix it, then I'm in your debt.  If you can get me to understand what I did wrong, that would be amazing.
0
GRayLCommented:
Try this.  I changed field names by getting rid of underscores.  used sfmControl for the control, and frmName for the form embedded in the subform, etc.
2003-RA-Database.mdb
0
DontrembAuthor Commented:
Sorry, it doesn't even function.

I get a VBA error when trying to open the form.  It asks me for Last_Name when I try to open the personnel form.  When I do get the main form open, and try to use the combo box, there's another error.

Does this Database work at all on your computer?  I don't see how the fact that I have 2007 would make a database not work, if it works for you.
0
GRayLCommented:
It worked fine on my system A2003 under XP Pro.  You have to use the mdb I gave you, with the tables, forms and modules contained therein.  Last_Name was changed to LastName as were all the other elements containing an underscore.  Here it is again, I just tested this and it works fine.  Don't copy anything over to another mdb - for now.
2003-RA-Database.mdb
0
DontrembAuthor Commented:
Nope... Does the exact same thing.  I used your database both times.  Still wants me to input a value for Last_Name.  Also gives an order by Clause error when I click on the CBO Box.

I have NO idea what could cause this.

Perhaps only someone with 2007 will be able to help me.
0
GRayLCommented:
My point is where does Last_Name come from?  Because it is no longer a field name, I can understand the prompt, but from what?

I found it.  Open frmPersonnel in design view.  Highlight the small square top-left of the form - right-click, Properties, and note that Order By has the offending term - remove the underscore.

This has nothing to do with 2007 versus 2003.
0
DontrembAuthor Commented:
Ok, it works perfectly...  The combo box still gave me an error, but I made a new one, and it works perfectly.

So...  You have fixed the problem.

Step 2:  Can you explain to me how you did it so I can fix my real database?

0
GRayLCommented:
Note carefully the naming conventions I used for table, field, form and control names.  Examine the code behind the form frmAccommodations.  Note than when the record is changed, the event OnCurrent fires.  That event has the code which  re-queries frmCorrespondence contained in the control sfmCorrespondence.  Note you re-query the subform control, not the subform.  I also removed the queries you had behind the forms and replace then with tables.  None of the forms needed a Join, which I believe you had in frmPersonnel.

That shed enough light on the problem?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DontrembAuthor Commented:
I haven't tried it yet (which I will do right after this.)  

I'm sure this will fix the problem, however this still doesn't actually solve the problem, it just is a workaround.  Remember, that this USED to work without the need for an "OnCurrent" code.  Also, I can make a new DB using the same system, and it works.  I'll go see if I can make that OnCurrent code for mine and see if it works.
0
DontrembAuthor Commented:
Ok, update:

All I did was copy your OnCurrent Code and paste it into mine.  I didn't change any names or anything.

It works great.  That little bit of code will certainly suffice.

I DO wonder what happened to REQUIRE me to use that OnCurrent code, since I didn't have to before...

Anyway, Thank you, Gray.  You stuck with me for exactly 32 days on this problem of mine.  It's too bad I can't give you a higher score than A 500 points.  Thanks again!
0
DontrembAuthor Commented:
Completely fixed the problem by using a VERY eloquent work-around.
0
GRayLCommented:
Thanks, glad to help.  If you check the code behind the forms you were using previously, you will find a Form_Current() event which re-queried the subform.  In that case, however, as the subform and the form used as a sub form had the same name, I was not able to determine which one needed shaking.  Use of a proper naming convention allowed resolution in short order.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.