CODEWITHSUNDEEP
ms-access
Paul Carlson
Paul Carlson

Reputation: 457

Updating expressions on form such as Dlookup when data has changed

I am working with Access 2010, and what I am trying to do is connect two fields in a form together. The first field is a combo box (TypeCombo), the other is a text box (PeriodText). What I need to happen is when the user selects an option from the TypeCombo, it will fill in the PeriodText with the correct period.

From what I have seen the easiest way to do this is use the =DLookup function, provided. Which is giving me the issues. My current code for DLookUp is:

=DLookUp("[Type_Name]","Type"," [TypeCombo] =" & [Forms]![Form1]![TypeCombo])

My current code for the combo box is: 

SELECT Type.Period_ID AS Expr1, Type.Type_Name AS Expr2
FROM Type
ORDER BY Type.Type_Name;

So what is suppose to happen is when the select the type I need to be able to get the Period_ID, then in the text box I want to use the Period_ID to select the correct period. When the form loads it will give me the Expr1 of the first item in the list, but then when I change it it will not update.

How do I get dlookup to update with new data from a combo box?

Upvotes: 1

Views: 1629

Answers (2)

Paul Carlson
Paul Carlson

Reputation: 457

E Mett, I want to thank you for your response, but I found another that works just as well for me and does what I need. I understand how yours is suppose to work, but when I tried coding it I could just not get it to work for me.

What I ended up looking at is the focus control for my text field. I used the first column of the Combo box and supplied that into a dlookup, I then was able to set the text of the field when ever it changes. I know that it will not show up automatically, but in the end I still get the correct information to save. This is what I ended up doing:

Strg333 = Me.TypeCombo.Value
VarX = DLookup("[Period.Period]", "Period", "[Period.Period_ID] =" & Strg333)
If PeriodText.Locked = True Then
PeriodText.Locked = False
End If
PeriodText.Text = VarX
PeriodText.Locked = True

I have the lock in there so that no one can change any values, its a design idea that I may end up just removing.

Upvotes: 0

E Mett
E Mett

Reputation: 2302

In TypeCombo's AfterUpdate event:

Me.Requery

This updates all the queries and dlookups etc. that are used in the controls.

Upvotes: 1

Related Questions