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:
<TBODY>
</TBODY>
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!
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:
| ID | PLANT |
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | B |
| 5 | B |
| 6 | D |
| 7 | A |
| 8 | C |
| 9 | A |
| 10 | A |
<TBODY>
</TBODY>
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!
Last edited:
