Calculated Field in Form

[jkbyerly]

We started an Access Database recently to track our incoming quality records. The user opens the form, enters the required information, and the record is saved in the table. We are trying to streamline parts of this process. Currently, the user enters the quantity received, looks at an AQL template that tells them how many pieces we need to check for our sample, and then enters that sample size. I would like the user to enter the quantity received and then have the sample size automatically populated based on the AQL table I added to the database.

I have tried using DLOOKUP in the control source and the default value field, but I can never get it to recalculate after the quantity received is populated. Most of my experience has been with queries, so I am pretty good with SQL functions, but I am at best a novice when it comes to VBA. Can someone give me a quick rundown on how I can get this field to calculate properly?



Thanks!

[ItsMe]

Create a VBA procedure that handles an event. Maybe after the user updates the value of a control on the form you can run some VBA that does some calculations. In the property sheet for the control, you can select the Event tab and click the ellipses(...) located next to the On After Update field. A window will open. Select the "Code Builder" option. This will launch the VBA editor and add a couple of lines of code. The code is the beginning line and the ending line of the event handler.

Your code to do the calculations will go in the middle of what Access created for you.

[jkbyerly]

That's about what I thought, but I don't think I know what the code should be. In a form named QC_FORM, they will enter the data in the QTY_RCV field. The SAMPLE_SIZE field should then be populated with the results of =DLookUp("SAMPLE_QTY","AQL","LOT_QTY"=[Forms]![QC_FORM]![QTY_RCV])

Can you help me convert that into vb code?

[ItsMe]

The DLookup function retrieves a value. You can create a variable in VBA and use the variable to store values. Here is an example of a long integer variable

Dim lngCount as long

Now that you have a bucket to collect what the DLookUp returns, you can put that in front of your expresion and create a statement.

lngCount = DLookUp("SAMPLE_QTY","AQL","LOT_QTY"=[Forms]![QC_FORM]![QTY_RCV])

You could put it all together in an after update event that would assign the value to a control or a field in a table.

Dim lngCount as long
lngCount = DLookUp("SAMPLE_QTY","AQL","LOT_QTY"=[Forms]![QC_FORM]![QTY_RCV])
Me.ControlName = lngCount

This is assuming that the data type for SAMPLE_QTY will fit into a long integer variable.

[ssanfu]

Actually, I think the DLookup syntax is wrong.

lngCount = DLookup("SAMPLE_QTY", "AQL", "LOT_QTY = " & [Forms]![QC_FORM]![QTY_RCV])

The equals sign should be inside of the quote and the reference to [QTY_RCV] needs to be concatenated to the criteria string.

[jkbyerly]

Thanks for the help. I did not follow your exact instructions, but it did get me thinking in the right way. For future reference, this is the code I ended up using.

Private Sub QTY_RCV_AfterUpdate()
Me.SAMPLE_SIZE = Nz(DLookup("[SAMPLE_QTY]", "AQL", "[LOT_QTY] = [Forms]![QC_FORM]![QTY_RCV]"), 29)
End Sub

[rpeare]

I am surprised that worked with your where clause the way it is, learn something new every day.

[ssanfu]

Great!

Me.SAMPLE_SIZE = Nz(DLookup("[SAMPLE_QTY]", "AQL", "[LOT_QTY] = [Forms]![QC_FORM]![QTY_RCV]"), 29)

This is not the correct syntax. (in RED)

You have created a criteria string of= "[LOT_QTY] = [Forms]![QC_FORM]![QTY_RCV]"
The value form the control on the form needs to concatenated to the field so the criteria string looks like [LOT_QTY] = 12
What you have is literally a string that is [LOT_QTY] = [Forms]![QC_FORM]![QTY_RCV]
(See HELP)


The correct syntax is for the criteria argument is "[LOT_QTY] = " & [Forms]![QC_FORM]![QTY_RCV]




I'm just saying......

[rpeare]

that's why I was surprised it worked ssnafu!

[jkbyerly]

I am just as surprised as you all! Every time I tried it the other way, it returned a null value. I just kept manipulating it until it finally returned the correct data. Luck is sometimes just as important as skill

[TioAdjie]

Dear All,
I think to find the value from the table by using Dlookup is not good idea. It will make slow searching. Why not using Select Query ?

[June7]

I have seen that before but I am still astounded it works. Wish I could understand why it does.

Tio, I have LOTS of DLookup in VBA. Runs plenty fast.

True, domain aggregates can be slow in queries and textboxes.

[TioAdjie]

June7,
I see it in internets tip "Increasing database design" . Here's I enclosed the tip.
So, do you mean that Aggregate Functions will go slow if it is stored in Query and textbox, but it will fast in VBA ?

[June7]

Your link did not post.

I have read performance comparisons between recordset and DA functions. I just know code is fast enough for my requirements. I use DAs when I want only a single value and no real need to instantiate recordset object.

Maybe if db were much larger I would notice VBA performance issue.

DAs can be noticeably slow in query and textbox, even in a small db.

[TioAdjie]

Sorry, here is the tips from internet :
===
"Don't Use Domain Aggregate Functions
If a query is based on one or more linked tables, avoid using functions (built-in or user-defined) or
domain aggregate functions in the query criteria. When you use domain aggregate functions such
as DLookup), Access must fetch all records in the function's data source to execute the query."
===
So, if we use Dlookup, Access must fetch all records, not only the record we want, so it will make slow. But may be your right. Above tips mention , Dlookup slow in Query, but may be fast in form with VBA.