Solved

Refresh a form when selecting a record in Microsoft Access

Posted on 2009-07-06
60
282 Views
Last Modified: 2013-11-28
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.)
0
Comment
Question by:Dontremb
  • 31
  • 22
  • 7
60 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 24785733
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
 

Author Comment

by:Dontremb
ID: 24786089
Hrm...  Where do I find this, "LinkMasterFields" option?
0
 

Author Comment

by:Dontremb
ID: 24786102
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
 
LVL 44

Expert Comment

by:GRayL
ID: 24786355
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
 

Author Comment

by:Dontremb
ID: 24786631
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
 
LVL 44

Expert Comment

by:GRayL
ID: 24790581
But are the SourceObjects, Forms or Table/Querys?
0
 

Author Comment

by:Dontremb
ID: 24793275
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
 
LVL 44

Expert Comment

by:GRayL
ID: 24794981
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
 
LVL 44

Expert Comment

by:GRayL
ID: 24795049
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
 

Author Comment

by:Dontremb
ID: 24795635
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
 
LVL 1

Expert Comment

by:swedishtongue
ID: 24795836
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
 

Author Comment

by:Dontremb
ID: 24795930
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
 
LVL 1

Expert Comment

by:swedishtongue
ID: 24795941
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
 
LVL 44

Expert Comment

by:GRayL
ID: 24795999
Now that we realize that Accomodations have nothing to do with Accomodations ;-) - be back later if not resolved.
0
 

Author Comment

by:Dontremb
ID: 24796464
@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
 
LVL 44

Expert Comment

by:GRayL
ID: 24798170
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
 

Author Comment

by:Dontremb
ID: 24803109
Are you saying that my LinkMasterFields property of my sfmAccommodations has to be my cboSearch?

How do I even do that?
0
 

Author Comment

by:Dontremb
ID: 24803143
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
 

Author Comment

by:Dontremb
ID: 24803247
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
 

Author Comment

by:Dontremb
ID: 24842493
Just checking back to see if there's been any more help.  
0
 
LVL 1

Expert Comment

by:swedishtongue
ID: 24843050

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
 
LVL 44

Expert Comment

by:GRayL
ID: 24844047
I've run into an inexplicable.  I'll get back to you tomorrow.
0
 

Author Comment

by:Dontremb
ID: 24848519
@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
 
LVL 1

Expert Comment

by:swedishtongue
ID: 24850644
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
 
LVL 44

Expert Comment

by:GRayL
ID: 24851134
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
 
LVL 44

Expert Comment

by:GRayL
ID: 24851482
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
 

Author Comment

by:Dontremb
ID: 24852222
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
 

Author Comment

by:Dontremb
ID: 24852488
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
 
LVL 1

Expert Comment

by:swedishtongue
ID: 24853008
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
 

Author Comment

by:Dontremb
ID: 24853037
@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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 1

Expert Comment

by:swedishtongue
ID: 24853055
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
 
LVL 1

Expert Comment

by:swedishtongue
ID: 24853094
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
 
LVL 44

Expert Comment

by:GRayL
ID: 24853184
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
 

Author Comment

by:Dontremb
ID: 24853209
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
 

Author Comment

by:Dontremb
ID: 24853282
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
 

Author Comment

by:Dontremb
ID: 24853289
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
 
LVL 44

Expert Comment

by:GRayL
ID: 24853447
I'll give you the modified file back.
Show.mdb
0
 

Author Comment

by:Dontremb
ID: 24853539
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
 
LVL 44

Expert Comment

by:GRayL
ID: 24853665
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
 

Author Comment

by:Dontremb
ID: 24860991
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
 
LVL 44

Expert Comment

by:GRayL
ID: 24861191
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
 

Author Comment

by:Dontremb
ID: 24863354
Yes...  That's what it says.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24864081
So I guess you made the name changes and you still have a problem?
0
 

Author Comment

by:Dontremb
ID: 25003519
Yup, I still have the problem.  No changes.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 25004284
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
 

Author Comment

by:Dontremb
ID: 25004801
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
 

Author Comment

by:Dontremb
ID: 25004910
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
 
LVL 44

Expert Comment

by:GRayL
ID: 25007596
I'm working on it, making a lot of changes to names so I don't get confused.  Hopefully, sometime tomorrow.
0
 

Author Comment

by:Dontremb
ID: 25007865
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
 
LVL 44

Expert Comment

by:GRayL
ID: 25025889
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
 

Author Comment

by:Dontremb
ID: 25026790
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
 
LVL 44

Expert Comment

by:GRayL
ID: 25027783
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
 

Author Comment

by:Dontremb
ID: 25032239
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
 
LVL 44

Expert Comment

by:GRayL
ID: 25037958
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
 

Author Comment

by:Dontremb
ID: 25042125
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
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
ID: 25043438
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
 

Author Comment

by:Dontremb
ID: 25045881
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
 

Author Comment

by:Dontremb
ID: 25045913
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
 

Author Closing Comment

by:Dontremb
ID: 31600138
Completely fixed the problem by using a VERY eloquent work-around.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 25055816
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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

747 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

12 Experts available now in Live!

Get 1:1 Help Now