AfterUpdate Event & DLookup Function in Microsoft Access - How to Look Up Values from Table to Form

[@Jojosmith342]

all thumbs up to one of the most favourite teachers 👍 thank you very much Richard for educating us

[@sonmezb]

You are a natural teacher, excellent videos, love watching them.

[@599CD]

Thank you very much

[@ReneB099]

Excellent tutorial video Richard. Following your clear explanation, I was able to successfully modify and apply your code to my own application. A big thank you. ☺👍

[@599CD]

Nice work!

[@Quackamagooska]

This just helped me solve a perplexing problem that, in hindsight, was so obvious! I also found two other issues that this resolves, better. Signed up as a Silver Member, a month ago and it is paying off.

[@599CD]

Glad you're enjoying the videos! :)

[@springsnature7037]

Honestly, man, thank you so much! This was such a great help to me as I'm learning about Access in college and homework was being a pain. Subscribed!

[@599CD]

You're very welcome!

[@anandmallikarjunan7775]

Excellent video. A small video that got a big impact indeed. Wish you best of luck. Thanks.

[@599CD]

Welcome

[@27oslec]

Finally! Thanks for this video.. it solved my dlookup with a text criteria! Kudos..

[@599CD]

Glad it helped!

[@ranjishiyama5875]

This saved me a lot. Such a weird string concatenation!

[@599CD]

Yep

[@VforAllofUs]

YOU MADE MY DAY))) THNK YOU

[@emarreola]

Thank you soooo much, been looking for this for ages!

[@599CD]

Glad I could help!

[@pvb1905]

Thank You very much will see it deeply and will learn . Salute to you SIR.

[@599CD]

You are most welcome

[@zdong1590]

hello how do i auto update a field in another form instead? with both forms having same unique ids.

[@599CD]

599cd.com/ask

[@MrKenCollectibles]

I'm new to using MS Access. I need to find a way to update a field with a new date when another field is changed. I have an pending email table with the following fields (email_box, sent by, receive_date, subject, track_date, complete_date, category). I need to update the track date if the user changes the category. Any assistance you could provide would be greatly appreciated.

[@599CD]

599cd.com/AfterUpdate

[@599CD]

That's what this video shows... updating one field when another is changed.

[@gayanthafernando9395]

Thanks sir , i am studing lot of thing from you.

[@599CD]

Glad to hear that

[@garethwoodall577]

Hi Richard, thanks for the awesome video. You mentioned a ZIP video (around 11 mins) and vlookup, is it in the links above as I couldnt see it?

[@599CD]

VLOOKUP was in the comments below the video (click SEE MORE) but you're right - I forgot the ZIP Code Lookup. I just added it: kzbin.info/www/bejne/a3eZmIyNbpaqeKs

[@tutsecret499]

What if say do DLOOKUP if DOB field is not null on the table, then populate the Age field column in the table with the data on the Calculated Age field column from the query. Is that possible that using DLOOKUP?

[@599CD]

Use the NZ function. If the DOB is not in the table, it will return a zero. Then with IIF you could say if the DOB is zero, set the age to zero. 599cd.com/NZ - 599cd.com/IIF

[@tutsecret499]

Thank you. I watched the DLOOKUP after update. I will start set it up for my situation and see if it will work, and I let you know. Cross your fingers.

[@bayanmobarak4661]

Hello Richard, I have a value of planned production that will be *updated* each week, I want this value to be stored on one of my column in my database. I`m thinking about making a form with a single input box to put the value, but i`m confused about how to tell access to store that value on a certain table, I mean is it possible to write a function in a table field on access? Can you please advice me about the right approach to do this ? Thank you!

[@599CD]

I would make a related table for your updates and display them in a subform. 599cd.com/Subform

[@bayanmobarak4661]

@@599CD Thank you so much for the fast respond! but can I know what the purpose of displaying it on a subform? I need the planned production to be automatically filled on a table when the user fill a form.

[@599CD]

I don't have any idea what you mean by that. Please see 599cd.com/Context

[@akokohjoel9854]

Mr Rost you are the generation Master of ms access. Pls sir can the above VBA code be use in ms access 2010?

[@599CD]

Thank you. Yes, that code should work in pretty much every version of Access going back as far as I can remember.

[@27oslec]

Hello.. this is perhaps what i am looking for. But i am having trouble with the currency field (unit price). Your expertise can give me a head up.

[@599CD]

I need more specifics.

[@carlosmrivas268]

Hi, I' have been trying to auto-populate a field by looking up a field in another table for hours, and when I found your tutorial I thought I got it, but it didn't work as it should, all I keep getting it's the first value from the table I'm doing the look up. I tried sorting the table but whatever info I enter, I always get back the first value in the table, What could I been doing wrong?

[@599CD]

Well, what's your statement?

[@raymondmimay3307]

@Computer Learning Zone I have a query named "StudentQ". It has a field named "Name". I created a blank form then add a combo box named "NameCombo" where it shows the names in the field "Name" from the query "StudentQ". I then created a text box and named it "NameBox". Now, I want to show in the textbox "NameBox" whatever is chosen in the combo box "NameCombo". So I did a DLookUp in the code builder. Me.Namebox = DLookUp("Name", "StudentQ", "Name = " & Me.NameCombo) But it always says "syntax error (missing operator) in query expression ...." Why is that? How can I correct that?

[@599CD]

I get a ton of questions every day, and I don't have time to answer them all here on KZbin. Feel free to submit your question on my website at: 599cd.com/AskYT

[@nishu621]

Thank you for wonderful explanation.

[@599CD]

You are welcome!

[@Sorrocasharked]

Did I miss something, or do I also have to manually add the zone for the customer too?

[@599CD]

You probably missed something. Watch it again. The Zone is a field in the CustomerT. When you pick a state they get assigned a Zone, but it can be changed. This is why we needed an AfterUpdate event with DLOOKUP instead of just a relationship.

[@emielbeus]

Really Helpful!

[@599CD]

Glad it was helpful!

[@rabidfollower]

The user says that sometimes he needs to enter zones that are different from the defaults. So the AfterUpdate event needs to prevent accidentally overwriting values that the user has filled in himself -- especially when Access often doesn't let the user undo mistakes. In your example, even if you type in the same state as before, the AfterUpdate event will still run, because Access sees that as an "update" too. You need to prevent accidental erasure of values at all cost. If I were the user, I would have AfterUpdate change the zone only when the zone is empty. I would also add an unbound field showing the default zone right next to the bound field of the actual zone in the table, so the user could see if they are different.

[@599CD]

There's always something more you can add... The more I play with a database, the more I find I want to change. These are quick tip videos. I cover many more in-depth situations in my full lessons.

[@mayureshvirkar8337]

Suppose the matching field "State" is of datatype number. Then it should be "State=State" or "State='"&State&"''" ?

[@599CD]

You don't need quotes for numbers. Always keep your values outside of the quotes. They SOMETIMES work inside the quotes, but they ALWAYS work outside. "CustomerID=" & ID "State=""" & MyState & """" "DateOfBirth=#" & DOB & "#"

[@mayureshvirkar8337]

@@599CD Thank you. Very useful content.

[@599CD]

You're very welcome.

[@nemo9396]

I didn't understand if in your VBA code, the first zone you write is the name of the text box in the form or the name of the field in the customer table. Edit: I figured it out so it's like this: Name of field box in the form = Dlookup ("[field name to look up]", "table where field is to be looked up", "[field name to look up] =" & Forms![Name of Form]!Name of field box where the value is located") Edit 2: Yeah well actually it works but it doesn't quite... I can't for the life of me make this work haha

[@599CD]

Without seeing exactly what you're doing, I can't tell ya.

[@theresaflorek8798]

@@599CD I am the same. You should name your fields differently so that we know which field from which table you are referring - State in your customer table and then StateName in the StateZoneT table. Zone in the customer table and ZoneNbr in the StateZoneT table.