Count records in a table matching form control value and save count to a bound control

[Buster55]

I have 2 tables that I am working with for this problem.
Tbl_UCR_Admin and Tbl_UCR_Victim

The fields in these tables of concern are:
Tbl_UCR_Admin.CASE_NUMBER
Tbl_UCR_Victim.CASE_NUMBER and Tbl_UCR_Victim.VictimNumber

I have a form:
Frm_UCR_Admin
When I open the form Frm_UCR_Admin a case number isgenerated as text and is saved on a control which is bound to theTbl_UCR_Admin.CASE_NUMBER field.
I have a form:
Frm_UCR_Victim thatis opened by clicking on a command button (cmd_AddVictim) on the footer of Frm_UCR_Admin.
The txtCASE_NUMBER is supplied to the control from theprevious Frm_Admin page using this code:

Private Sub cmdVictim_Click()

DoCmd.OpenForm"Frm_UCR_Victim"
DoCmd.GoToRecord ,"", acNewRec
Forms!Frm_UCR_Victim.txtCASE_NUMBER = Me.txtCASE_NUMBER

End Sub

What I am trying to do now is supply a victim number to eachnew victim and save it in the Tbl_UCR_Victim.VictimNumber field.
First victim would be 1 the second 2 and so on.

I would like to accomplish this by counting the records inthe victim table that match the txtCASE_NUMBER value. If there are no recordsmatching the case number value in Tbl_UCR_Victim then the txtVictimNumber wouldbe 1. If there are 3 the value would be 4 and so on. I can’t use anything thatdepends on the last record as there may be other cases with other victimsentered before adding victims to that case number.

Also on the footer of the Frm_UCR_Victim I have a commandbutton to add another victim which will have to move to a new record, copy thetxtCASE_NUMBER value and again count the number of records with matching casenumbers and add 1 again.

I have tried for days, many many suggestions until I havetotally confused myself.
I’m sure it isn’t that difficult but it is killing me hereand I have to do that with three more tables for suspect, arrestee, vehicle,etc. If I can figure this one out, the others should just follow suit.


Thank you in advance.

[pbaldy]

Typically you'd use DMax:

Nz(DMax(...), 0) + 1

DLookup Usage Samples

[Buster55]

Thank you soooo much. I will try that as soon as I can get back to my database and let you know if that works for me.

[pbaldy]

No problemo!

[Buster55]

Ok I know I'm probably just really stupid but I guess I still don't get it. I tried the code below but it still comes up as an error. I am putting this code into an unbound textbox on the form.

=NZ(DMax("Tbl_UCR_Victim","[CASE_NUMBER]","Criteria='" & Forms!Frm_UCR_Victim!txtCASE_NUMBER & "'"),0+1)

[Buster55]

I'm sorry here is the code that I am using that isn't working. it is early and not enough coffee yet.

=NZ(DMax("Tbl_UCR_Victim]","[CASE_NUMBER]","Criteria='" & Forms![Frm_UCR_Victim!txtCASE_NUMBER] & "'"),0+1)

[pbaldy]

The word Criteria should be the name of the field in the table the criteria is applied to. It also looks like you flipped the first two arguments.

[Buster55]

Thank you very much. You were absolutely right and It is now working sort of.
The only problem is that each victim number was still coming up as 1.
I realized that I was using an unbound control and there for the bound control wasn't going to save the changes to the new victim number.
I decided that what I needed to do was use the onclick event on the command button for the new record and use what you had shown me to add 1 numerical value to the highest value recorded for the victims assigned to the current case number.
Thought I was doing great.
I changed the format of the table from text to general number.
I went through all of the previously entered records and changed the victim number entries to as number.
Previous officers had entered v-1, 01, victim1 or whatever they chose.
Thus the reason for the standard, they can't choose, format.
The code below is what I have on the onclick event for the new victim.
It is erring out and highlighting the last line of code where I am trying to assign the victim number.

Private Sub cmdAddNewVictim_Click()
'When the user clicks on the command button it will copy and paste the highlighted record
 'Assign field values to be carried forward to variables
     MyField_1 = Me.txtCASE_NUMBER

 'Go to a new record
     DoCmd.GoToRecord , , acNewRec
 'Plug in old values from variables to new record
     Me.txtCASE_NUMBER = MyField_1
     
 'Count the number of victims assigned to this case number and assign the next numerical number to this victim
     
     [txtVICTIM_No] = Nz(DMax("[CASE_NUMBER]", "[Tbl_UCR_Victim]", "txtVICTIM_NO=" & [Forms]![Frm_UCR_Victim]![txtCASE_NUMBER] & ""), 0 + 1)
     
 End Sub

[pbaldy]

What's the error message? If you changed the data type of the field (as opposed to the format), then that's close. I think the closing parentheses is misplaced. Try

[txtVICTIM_No] = Nz(DMax("[CASE_NUMBER]", "[Tbl_UCR_Victim]", "txtVICTIM_NO=" & [Forms]![Frm_UCR_Victim]![txtCASE_NUMBER]), 0) + 1

[Buster55]

I'm sorry, you were right. I changed the data type in the table.
Same error with the old code and the new that you sent me to try.

Run-time error '2001':
You canceled the previous operation.

[pbaldy]

Is txtVICTIM_NO the name of the field in the table? It sounds like a textbox name. Besides that, I would think you'd want the case number field in the criteria.

Edit: and the victim number field in the first argument, the field to find the max of.

[Buster55]

yes txtVICTIM_NO is a textbox bound to the VICTIM_NO field n the table.
The object is to populate that control on the form with a victim number 1 higher than the highest victim number, in the table, matching the txtCASE_NUMBER control value.
And of coarse a 1 if there are no victim numbers with that case number.
I hope that makes sense because I am starting to confuse myself again.

[pbaldy]

My point is you want the name of the field in the table there, not the name of a textbox. You also have a logic problem. This:

DMax("[CASE_NUMBER]", "[Tbl_UCR_Victim]", "VICTIM_NO=" & [Forms]![Frm_UCR_Victim]![txtCASE_NUMBER])

says "find me the largest CASE_NUMBER where the VICTIM_NO equals whatever". You want that reversed.

[Buster55]

OK I studied and studied and tried to understand what you were telling me.
I changed the code to the following:

Private Sub cmdAddNewVictim_Click()
'When the user clicks on the command button it will copy and paste the highlighted record
 'Assign field values to be carried forward to variables
     MyField_1 = Me.txtCASE_NUMBER

 'Go to a new record
     DoCmd.GoToRecord , , acNewRec
 'Plug in old values from variables to new record
     Me.txtCASE_NUMBER = MyField_1
     
 'Count the number of victims assigned to this case number and assign the next the next numerical number to this victim
     
    [VICTIM_NO] = Nz(DMax("[VICTIM_NO]", "[Tbl_UCR_Victim]", "CASE_NUMBER=" & [Forms]![Frm_UCR_Victim]![txtCASE_NUMBER] & ""), 0 + 1)
     
 End Sub

When I add a new victim, the victim number still shows 1 instead of 1 for the first one and 2 for the second and so on.
I hate to keep bothering you. I have been able all of these years to find the answers on the internet and or resolve them myself. Maybe I am just in over my head but it just seems like simple code that I am not getting.

[Micron]

Look again a post #9. There are no double quotes after case number, plus you have the +1 inside the brackets.