[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Dlookup field not updating every time

Posted on 2004-11-30
7
Medium Priority
?
372 Views
Last Modified: 2008-03-06
I have a really weird problem, and I'm running into a HUGE time crunch.  I have an Access 97 database with several tables, queries and forms.  It's not a real complicated database.  I have a form that has a Dlookup field.  This field should automatically update when I change another field.  However, it's not changing all the time.  It depends on certain records that I choose from my drop-down in the one field that determines if it will automatically update my dlookup field.  I can't find any rhyme or reason to it.  I have a Requery on the AfterUpdate and OnChange events for the one field.  However, it's not updating my Dlookup field.  It will, however, update the field as soon as I exit the form and come back in or if I change from Design View to Form View.  That's why I keep thinking I need a requery somewhere in the Form Properties, but I can't figure it out.  HELP - and FAST!!!!  If this doesn't make sense, I'd be glad to send my database to someone who can help me.  I'd give 500 points, but I don't have that many to give.
0
Comment
Question by:esu4236
  • 3
  • 2
  • 2
7 Comments
 
LVL 8

Expert Comment

by:Jeff Tennessen
ID: 12709882
When you say you call Requery, is it Requery of the form or of the control that has the DLookup as its ControlSource?

Jeff
0
 

Author Comment

by:esu4236
ID: 12709910
It's the control that has the DLookup as its ControlSource.  Here it is (I have it in both the AfterUpdate and Change events):

Private Sub District_AfterUpdate()
    District.Requery
    DivisionName.Requery
End Sub

Private Sub District_Change()
    District.Requery
    DivisionName.Requery
   
End Sub

The District field is the field I'm changing, and it's based on a drop-down.  The DivisionName field is the Dlookup field.
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 1200 total points
ID: 12709924
Try this in the AfterUpdate event of your drop down:

Me.Dirty=False      'Save changed data
Me!MyTextbox.Requery

Where MyTextbox is the name of the textbox that has the DLookUp in it.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:esu4236
ID: 12710038
You are GENIUS!!!!!!!!!!!!!  That worked.  But before I award you your points, can you explain to me why the plain old Requery wouldn't work the way I had it?  It seems to work fine in another form I have in this same database.  I'm curious and want to learn how this works.  THANKS.
0
 
LVL 8

Expert Comment

by:Jeff Tennessen
ID: 12710076
Hmm. Off the top of my head, that seems like it should work. I'm not sure the Requery of District is necesary. You might also try using the Click event of the combo box instead; contrary to its name, it actually functions much like the AfterUpdate event, but perhaps it would give better results. Also, try setting the requery in only one event at a time, it may be that multiple Requeries is confusing Access.

Those are all things you can try, though I am skeptical any of them will work. Sometimes approaches that seem perfectly valid just don't work for no discernable reason and you've got to try something else. Here's one approach. First, unbind the DivisionName control on your form, then change your District_AfterUpdate procedure as follows:


Private Sub District_AfterUpdate()

  Dim rs As New ADODB.Recordset   'you could also use a DAO recordset if you prefer, though the code below assumes ADO
 
  With rs
    .CursortLocation = adUseClient
    .ActiveConnection = CurrentProject.Connection
    .CursorType = adOpenForwardOnly
    .LockType = adLockReadOnly
    .Open "SELECT DivisionName FROM <tablename> WHERE District = '" & District & "'"
   
    If Not .BOF And Not .EOF Then DivisionName = .Fields(0)
  End With
 
  rs.Close
  Set rs = Nothing

End Sub


Since I haven't seen your exact DLookup function and don't know the specifics of your database schema, the SQL in the .Open statement may need to be modified. Let me know if you think this approach is worth trying, and I'll be glad to help you get it working.

Jeff
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12710079
Basically, the Dirty commits any changed data into your table. The reason it probably wasn't working before was because the data hadn't been committed - that is to say, it looks like it has changed on-screen but it hasn't "really" changed - Access still "sees" the old data. The Dirty line forces it to use the new data.
0
 

Author Comment

by:esu4236
ID: 12710123
Thank you for the prompt responses.  Appreciate it very much!!!!!!!!!!!!
0

Featured Post

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.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

872 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