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

[Buster55]

Here is the new code and still I get each new record showing victim Number as 1.

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

[Micron]

Then your expression must always be returning null, which is coerced to zero, upon which 1 is added. To test, put a break point on the gotorecord line and when you click the button, it will stop there. Then in the immediate window, type in a question mark and paste this part of your expression and hit return to see what the value is.
DMax("[VICTIM_NO]", "[Tbl_UCR_Victim]", "CASE_NUMBER=" & [Forms]![Frm_UCR_Victim]![txtCASE_NUMBER])

I suspect the result will be Null because I seem to recall from your first post that your victim number was text; if so, you are trying to add a number to a string.

[Buster55]

I'm sorry to bother you yet Paul but try as I may, I can't get it to work.
I even tried making a query to count the records that match the case number on the loaded form record.
That works as long as I close the form and reopen it to that record before running the query.
I tried refresh, requery, and even save but only by closing and reopening.
I decided I would have to live with that.
When I enter control value = that query comes up as a data type mismatch.
I ten double check the table field and it is set to number.
I figure the number field should accept the result of a count as that should be a number.
You are right in that the database that these tables were originally in, had the victim number as text.
I had changed that.
I even deleted the control on the form and added a new one in case the old control was still using the text data type.
Still the same result.
I then inserted a new field in the table, copied all of the existing data to the new field, deleted the old field and renamed the new field to the old field name.
Still the same result. (Type mismatch) using the query on the control.
It make absolutely no sense to me.
I have stripped all of the confidential data from the database and have uploaded it to bimcomputers.com/downloads/UCR_Test.zip
I certainly don't blame you if you are tired of working with this and I do appreciate all of your help.

[pbaldy]

Link doesn't work for me. You should be able to attach the file here after a compact/repair and zipping.

[Buster55]

UCR_Okeene.zip

OK Paul. I tried to attach earlier but the database was still too large even after compact repair and zip. I just made a new database importing only the 2 tables and 2 forms that we should be concerned with. Now it is small enough. When I told it to attach I just got the above tags, I hope that will do it for you.
And again thank you so much. I think our police department has given up on me doing this for them because I am just not bright enough.

[pbaldy]

Won't pretend to follow what's going on in the app, but this appears to work (your case number field is text):

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

[Buster55]

Hallelujah!! YOU ARE THE MAN PAUL!!! I laid several of the codes by one another so I could try to figure out what I was missing and just decided I'm too old and not smart enough. I should be able to use that same code for all of the 7 other things I have to put in and count the same way though. I did have a problem with the first record being 0 instead of 1. I tried changing the 0 to 1 for the Nz but that didn't work so I just put the 1 in as the default property on the control and all is working great now.

[Buster55]

I can't thank you enough for all of your help on this and I am so sorry that I was so ignorant about it. You were right and the CASE_NUMBER is a text file as it conforms to their formatting that they wanted with the yyyy-m-d-case number for that date format. I didn't realize that would be an issue since all we were doing was counting all of the victims entered who were assigned to that case number.

[pbaldy]

Happy to help!