Dlookup field not updating every time

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.
esu4236Asked:
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.

Jeff TennessenAssistant Vice PresidentCommented:
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
esu4236Author Commented:
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
shanesuebsahakarnCommented:
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

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

esu4236Author Commented:
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
Jeff TennessenAssistant Vice PresidentCommented:
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
shanesuebsahakarnCommented:
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
esu4236Author Commented:
Thank you for the prompt responses.  Appreciate it very much!!!!!!!!!!!!
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.