Update form field based on count of two grouped table fields? 2

[wlwoman]

-I have a form with fields "monthreceived", "lotnumber" and "count" that are my targets of interest.
-My table has fields "lotnumber" and "monthreceived" that need to be grouped then counted via query. (The query for grouping by lotnumber and monthreceived produces accurate counts)
-I want to update the unbound form field "count" based on the number of lotnumbers in the table during a specific month that match the lotnumber and monthreceived on the form.
-The "count" field does not have to be saved anywhere since it is used only to provide a value on a label printed from info on the current record.
-----------
For example, upon entering lotnumber '73222' and monthreceived 'JAN' in the form, I want to update 'count' field based on the number of records in the table matching lotnumber '73222' and monthreceived 'JAN'.

------------

I have run out of ideas! Can anyone help me refocus on the right way to accomplish this task?

 

[RickSpr]

I'm a bit confused by your use of the terms "group" and "count" together here. If all you want is a count, I don't see why you need to group anything, in the sense of a GROUP BY clause. So I'll just ignore the reference to grouping.

Here's what I would try first. At some point (which I'll discuss later), use VBA code to do a DLookup() to retrieve the count, and just assign the results to the unbound field. It might be something very close to this:

    Me.count = DLookup("[green]<count field name>[/green]", "[green]<count query name>[/green]", "lotnumber='" & Me.lotnumber & "' AND monthreceived='" & Me.monthreceived & "'")

As for where to do this, it depends on whether you have some specific event which corresponds to finishing with that form. You say you're printing labels? If you have a Print command button on the form, then the Print_Click event procedure would be a good place to put it.

If you don't have a specific event you can use, or even if you'd just rather have the count appear on the form as soon as both lotnumber and monthreceived have been entered, then I would put the code in the AfterUpdate event of each of the lotnumber and monthreceived fields. You should make it conditional on both fields being entered. For example:

   If Not IsNull(lotnumber) And Not IsNull(Me.monthreceived) Then
        Me.count = DLookup(...)
    End If

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[PHV]

In the Current event procedure of the form you may add this code:
Me!count = DCount("*", "[name of table]", "lotnumber='" & Me!lotnumber & "' And monthreceived='" & Me!monthreceived & "'")
If lotnumber and/or monthreceived are defined as numeric then get rid of the corresponding single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[wlwoman]

Thanks to both of you for your assistance - still working on this due to some monkeywrenches thrown in the works by the database requestor! Both codes work; the count update seems to be one step behind but I think it's a minor issue.

 

[wlwoman]

Once again, many thanks. This worked out very well without having to run a query at all, and a good thing! The project has expanded from a single department label generator to a plantwide FIFO system including the label generator. The generator form is so easily adapted to any count parameter a department chooses, and I can't thank you enough for getting me over that hump!

The count update issue was minor and has been resolved.

Your help enabled me to get the baseline set for this project and allowed me to move onto the other three short-deadline database development projects that ended up on my lap when a new customer announced an imminent audit of our quality system, complete with some requirements we were totally unprepared for. With all four projects on schedule, I have both of you to thank for saving me a lot of frustration and wasted time.

 

[RickSpr]

Expanded? Sounds like it exploded!

This isn't scope creep, it's an all-out scope onslaught! Good luck!

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein