I have a database that I am building for our local policedept. This portion of the database is to record incidents /arrests. They needthe a case number automatically assigned to each new report. The format forthis would be yyyy-m-d-report number. Ihave accomplished all of this.
The report date in the table is RPT_DATE and the defaultvalue is set to DATE().
I have 4 hidden textboxes on my form
[txtRptYear] ControlSource =DatePart("yyyy",[txtRPT_DATE])
[txtRptMonth] ControlSource =DatePart("m",[txtRPT_DATE])
[txtRptDay] Control Source =DatePart("d",[txtRPT_DATE])
[txtREPORT_NUMBER] Control Source =DCount("*","Tbl_UCR_UniformIncidentOffense","[RPT_DATE]=#"& [txtRPT_DATE] & "#")
On the main Officer page I have a command button which opensthe new report.
Code:
Private Sub cmdAddNewReport_Click()
'Close the UCRmain page form
DoCmd.CloseacForm, "Frm_UCR_MainPage", acSaveYes
'Append the loggedon officer information to a a new record
DoCmd.SetWarningsFalse
DoCmd.OpenQuery"Qry_UCR_New_AppendOfficer"
DoCmd.SetWarningsTrue
'Open the form andgo to the new record
DoCmd.OpenForm"Frm_UCR_AdminReport"
DoCmd.GoToRecord ,, acLast
DoCmd.CloseacForm, "Frm_UCR_AdminReport", acSaveYes
DoCmd.OpenForm"Frm_UCR_AdminReport"
DoCmd.GoToRecord ,, acLast
‘Create a case number
Forms!Frm_UCR_AdminReport.txtCASE_NUMBER.Value= Forms!Frm_UCR_AdminReport.txtRptYear & "-" &Forms!Frm_UCR_AdminReport.txtRptMonth & "-" &Forms!Frm_UCR_AdminReport.txtRptDay & "-" &Forms!Frm_UCR_AdminReport.txtREPORT_NUMBER
End Sub
This works great and when the new form opens a case numberis assigned as expected to the txtCASE_NUMBER textbox which is bound to theCASE_NUMBER field.
But suppose the officer doesn’t fillout the report until thenext day.
I have a popup calendar on the double_click event of the [txtRPT_DATE]that allows them to change the date. (This works great).
Now the problem is with this new date, I need the casenumber to reflect the date change.
I have tried:
Code:
Private Sub txtRPT_DATE_AfterUpdate()
Me.txtRptYear =DatePart("yyyy", [txtRPT_DATE])
Me.txtRptMonth =DatePart("m", [txtRPT_DATE])
Me.txtRPT_DATE =DatePart("d", [txtRPT_DATE])
Me.txtREPORT_NUMBER = DCount("*", "Tbl_UCR_UniformIncidentOffense","[RPT_DATE]=#" & [txtRPT_DATE] & "#")
'Me.txtCASE_NUMBER.Value = Forms!Frm_UCR_AdminReport.txtRptYear &"-" & Forms!Frm_UCR_AdminReport.txtRptMonth & "-"& Forms!Frm_UCR_AdminReport.txtRptDay & "-" &Forms!Frm_UCR_AdminReport.txtREPORT_NUMBER
End Sub
This does not changethe [txtCASE_Number] value.
I then tried adding an unbound text box [txtCase_Number_Config]to the form.
Control Source =[txtRptYear] & "-" &[txtRptMonth] & "-" & [txtRptDay] & "-" &[txtREPORT_NUMBER]
Code:
Private Sub txtRPT_DATE_AfterUpdate()
Me.txtRptYear =DatePart("yyyy", [txtRPT_DATE])
Me.txtRptMonth =DatePart("m", [txtRPT_DATE])
Me.txtRPT_DATE =DatePart("d", [txtRPT_DATE])
Me.txtREPORT_NUMBER = DCount("*","Tbl_UCR_UniformIncidentOffense", "[RPT_DATE]=#" &[txtRPT_DATE] & "#")
Me.txtCase_Number_Config.Requery
End Sub
Now my unbound [txtCase_Number_Config] text box shows thenew case number correctly after changing the [txtRPT_DATE].
Now I want to update the bound text box .[txtCASE_NUMBER] tothe value of the unbound textbox [txtCase_Number_Config].
I tried:
Code:
Private Sub txtRPT_DATE_AfterUpdate()
Me.txtRptYear =DatePart("yyyy", [txtRPT_DATE])
Me.txtRptMonth =DatePart("m", [txtRPT_DATE])
Me.txtRPT_DATE =DatePart("d", [txtRPT_DATE])
Me.txtREPORT_NUMBER = DCount("*","Tbl_UCR_UniformIncidentOffense", "[RPT_DATE]=#" &[txtRPT_DATE] & "#")
Me.txtCase_Number_Config.Requery
Me.txtCase_Number = Me.txtCase_Number_Config
End Sub
And
Code:
Me!txtCase_Number = Me!txtCase_Number_Config
And
Code:
Me!txtCase_Number.value = Me!txtCase_Number_Config
And
Code:
Me!txtCase_Number.value = Me!txtCase_Number_Config.value
Nothing changes the [txtCase_Number] to match the [txtCase_Number_Config]
Sorry it is so long but the directions said to be as preciseas possible.
Also I am using Access 2003 but will be convertibg to Access2007 when it is finished.