Having Trouble with DLookup in a Control 1

[JOEYB99]

I am using Access 2007.

I have a split form that reads from a table that I created. I have created a control on this form that uses DLookup to get a rate from an imported table based on data in the form.

I have tested several records manually and the rate that appears is correct. Therefore I know my DLookup formula is working properly.


When the user calls up a record in this form I want them to be able to change certain values on the form. These changes affect the rate and it should update accordingly, but it doesn't!

I have the formula in the control's property called Control Source. Should it be in a different property, and if so, why?

Any other input or suggestions would be greatly appreciated.

 

[MajP]

The Recalc method immediately updates all calculated controls on a form.
expression.Recalc

expression Required. An expression that returns one of the objects in the Applies To list.

Remarks
Using this method is equivalent to pressing the F9 key when a form has the focus. You can use this method to recalculate the values of controls that depend on other fields for which the contents may have changed.
Example

The following example uses the Recalc method to update controls on an Orders form. This form includes the Freight text box, which displays the freight cost, and a calculated control that displays the total cost of an order including freight. If the statement containing the Recalc method is placed in the AfterUpdate event procedure for the Freight text box, the total cost of an order is recalculated every time a new freight amount is entered.

Sub Freight_AfterUpdate()
Me.Recalc
End Su

 

[JOEYB99]

Thank you MajP for the prompt response!

I inserted this simple code into the After Update property of the control which calculates the rate.

It does not work dynamically but when I close the form and re-open it the rate is properly updated for that record.

What am I doing wrong?

 

[MajP]

I inserted this simple code into the After Update property of the control which calculates the rate.

Read the article again.

This form includes the Freight text box, which displays the freight cost, and a calculated control that displays the total cost of an order including freight. If the statement containing the Recalc method is placed in the AfterUpdate event procedure for the Freight text box, the total cost of an order is recalculated every time a new freight amount is entered.

You need to but this in the after update of the other controls. Lets say you have controls A and B where you change values, and control C is the calculated control that sums A and B. When you change A or B you want the form to recalc so you need to call it in their afterupdate. You never update C since it is calculated, so that will not fire anyways.

 

[JOEYB99]

Thanks MajP, that worked!

I guess its true - you can teach an old dog (me!) some new tricks!

Thanks again.

 

[MajP]

There is often a lot of confusion between
Repaint
Recalc
Refresh
Requery

And when to use which one. This may help.
projectperfect.com.au/blog/?p=425

Microsoft Access Requery, Recalc, Refresh and Repaint
Access has four methods that seem to be similar and often get new VBA users confused. Here is what each of the methods does, and how they are different.

Requery

This is the most powerful. It goes to the database and runs the underlying query again. For example, if you had a form which was used to add people. On that form you had a combo box that listed people. Now you open the form and you have 10 people in your table. You use the form to add another person. If you go to the combo, it will still only show 10 people. The 10 who were there when the form was opened. If you use the Got Focus to fire off a requery on the combo, you will have 11 people.

Recalc

Recalc does not get new records. It processes any pending screen changes. Here is an example. You have a unit price, and quantity field. There is a textbox that has a formula that gives a total price (e.g. = me.txtUnitPrice * me.txtQty). If you use recalc on the Lost Focus event of both quantity and price textboxes, you will recalculate the total price. It will not do anything to the underlying records.

Refresh

Refresh is sort of like requery except that it only updates the records on the screen. If someone has added another record in a multi user environment, it will not get display the new record. The good thing about refresh is that it leaves the cursor where it was. If you have a datasheet, and you are on the third row, use requery and you are back to the first row. Use refresh and you are still on the third row.

I have a particular application that has a subform which is a datasheet. Each record has a sequence number. On the main form, I have buttons to move records up or down. I select a record on the subform, then use the button on the main form to move it down. What happens is that it grabs the sequence number, finds the next in sequence and gives it a new sequence number (existing number -1) and gives the selected record a new sequence number (existing number + 1). I now need to resort them so can use refresh which leaves the cursor on the selected record. If I used requery, it would be back at the start. If I wanted to move down twice, I would have to find the record and select it again with requery.

Repaint

Finally we come to repaint. This is used to refresh the screen without interacting with the database. Where do you use repaint? Say you have a label “Update Processing” to warn users that a lengthy process is happening. If you set the visible property to false when you open the form, you can set it to true at the start of the update procedure. At the end you set it back to false. To make the screen display the change to the label use repaint after the change to visible.

It can be confusing and cause considerable frustration when you use a shotgun approach to the four terms. I hope this will make it clearer to people which method to use in which situation.

Tags: Microsoft Access Developer, Microsoft Access Development, Microsoft Access Help, Microsoft Access VBA, recalc, refresh, requery