Subset Record Counter in Form

[AlexaS]

Ok so I've been searching around and I haven't been able to find anything. The end user changed requirements on me (again, yay! *sobs*).

Here's the setup:

I have a field labeled PLANT in the CLAIMS table. In a form the first field selected for a new record is PLANT. They want a counter that (once they enter the PLANT value) will tell them how many times that plant has been used in the table. For example, pretend we have the following (simplified) table of data:

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]ID
[/TD]
[TD]PLANT
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]A
[/TD]
[/TR]
</TBODY>[/TABLE]


ID is just an autonumber that, if I can do this, will have no bearing on anything going forward (which I can probably even delete). What I'm sure they would like is for each entry on the form to indicate the number of times the PLANT had been used as of that entry. I don't care about that.

What I'm trying to do is on the new record to have a counter that if they input A as the PLANT the counter will show '4' (or even '5' if we have it count the record that is being worked on) or if they input D, the counter will show '1'. The counter number when displaying past records (for example the very first A) would still display '4' (or not display at all except on new records).

What they will be doing with this counter is then combining PLANT and Counter to create a unique Claim# that is sequential by PLANT. The Claim# is then utilized to tie together three supporting tables.

I tried adding a text box to the form using =Count([PLANT]) but that just returns a total record count. Could/should I create a query to store these values and then attach the query to a text box on the form? Or a separate table that gets updated after each new record is added (I found code [here] that might work for that if I can figure out how to display it and where to put the event code)?

Any help is always gratefully received and fully appreciated. Thanks!

 

[xenou]

Hi,
I think you could use DCount

Assuming plant is TEXT, so we put the plant ID in single quotes:
=DCount("[PLANT]","[TableName]","[PLANT]='" & Me.TextBox1.Value & "'")

Might be necessary to stuff a default value in when the textbox is NULL (it is assumed that a cat face will never be used for an actual plant id):
=DCount("[PLANT]","[TableName]","[PLANT]='" & Nz(Me.TextBox1.Value,"=^..^=") & "'")


A danger in using counting is that if a record is ever deleted, you will have the possibility of duplicate keys. So this might be something to stay away from.

 

[AlexaS]

Thank you for your response. I'm getting a #NAME? error when I input the formula though. Here is what I put into my expression builder:

=DCount("[PLANT]","[Claims]","[PLANT]='" & [Me].[PLANT].[Value] & "'")

 

[xenou]

I've never put brackets around Me. Try it without? Also be sure your identifiers (names) are all correct.
=DCount("[PLANT]","[Claims]","[PLANT]='" & Me.PLANT.Value & "'")

 

[AlexaS]

I'm not putting the brackets in. The expression builder is adding them automatically when I click "ok". I figured out how to get it to not add them, but now I keep getting "The expression you entered has an invalid string." I tried to fix that by changing it to =DCount("[PLANT]","[Claims]","[PLANT]=[PLANT]") returns the total number of records and when I change it back to =DCount("[PLANT]","[Claims]","[PLANT]="&Me.PLANT.Value&") I get the error message again. Any thoughts?

 

[xenou]

What is the name of the textbox with Plant in it? What is the datatype of that field?

 

[AlexaS]

The name is PLANT and the data type is Combo Box. Is that what's killing me on this?

 

[xenou]

Not sure. The value of a combobox can be other than what you see displayed. Make sure the Value of the combobox is actually Plant and not something else (like PlantDescription).

I also think you need to cover the case of the combobox being Null (nothing selected):
=DCount("[PLANT]","[Claims]","[PLANT]='" & Nz(Me.Plant.Value,"=^..^=") & "'")

By datatype I mean Text or Number. I have been assuming Text, but I don't know for sure. You also have to be sure you're on the right form. If you are using a control on a subform for the DCount formula, but the Plant combo is on the main form, then it won't work.

 

[AlexaS]

I guess I'm not sure how to verify that the ComboBox is what it is. When I look at the control source it says PLANT and not anything else, which leads me to believe that it is what it is showing.

Why do you have the single quotes in your formula? When I add those in, that's when it puts the brackets around the Me variant.

The control is on the correct form (main form, same as the PLANT field). The datatype of the field is text.

Since this seems to be stubborn, do I maybe want to consider a code solution, as I suggested in my first post?

Or a separate table that gets updated after each new record is added (I found code [here] that might work for that if I can figure out how to display it and where to put the event code)?

 

[xenou]

To test the field, just put a textbox on the form, with the formula:
=[Plant]

You should see a plant id if it is working right.

You need to put single quotes around text values that are used as conditions in a where clause:

Where Plant = 'ABC'