Trying to update a bound textbox from an unbound textbox after update

**Buster55** · 01-01-2016, 12:48 PM

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.

**vicsaccess** · 01-01-2016, 01:42 PM

sounds like you need to be using an update query, not sure what the table name is but something like this

Private Sub txtRPT_DATE_AfterUpdate()
dim sql as date
sql="UPDATE tablename SET tablename.casenumber=txtcase_number
currentdb.execute(sql)

you might look into update queries.

**vicsaccess** · 01-01-2016, 02:07 PM

sorry just hit me that your table field is not date, the dim should be dim as string.

**Buster55** · 01-01-2016, 03:59 PM

OK So here is my new code. Sorry but it didn't work.

[txtCASE_NUMBER] text box still doesn't change after changing the [txtRPT_DATE].

Code:

    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
    Dim sql As String
    sql = "UPDATE Tbl_UCR_UniformIncidentOffense SET Tbl_UCR_UniformIncidentOffense.casenumber=txtCase_Number_Config"
    CurrentDb.Execute (sql)

**Buster55** · 01-01-2016, 04:02 PM

I'm sorry but I forgot to add the Private Sub and end sub to the last post.

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
    Dim sql As String
    sql = "UPDATE Tbl_UCR_UniformIncidentOffense SET Tbl_UCR_UniformIncidentOffense.casenumber=txtCase_Number_Config"
    CurrentDb.Execute (sql)

End Sub

**vicsaccess** · 01-01-2016, 04:42 PM

which part is not working? have you tried debugging? comment out the currentdb.execute and add a line between sql= and current with the statement debug.print(sql). open your immediate window and see what prints. sorry i'm not used to the naming convention that you are used to with the underscores.

**vicsaccess** · 01-01-2016, 04:45 PM

which part is not working? have you tried debugging? comment out the currentdb.execute and add a line between sql= and current with the statement debug.print(sql). open your immediate window and see what prints. sorry i'm not used to the naming convention that you are used to with the underscores. post your database and i'm sure someone can help.

**ssanfu** · 01-01-2016, 11:17 PM

I tried to follow your explanation, but got lost trying to follow the forms and code.

In the sub "Sub cmdAddNewReport_Click()", you have a line

Code:

DoCmd.Close acForm, "Frm_UCR_MainPage", acSaveYes

You don't need the last argument "acSaveYes". This does not save the DATA, it saves changes to the form object.
This is the command I use to close a form

Code:

 DoCmd.Close acForm, Me.Name

Obviously, you would use an actual for name if you wanted to close a different form.
Access automatically saves date when the form closes.
You can force a save to the data by using

Code:

If Me.Dirty Then
  Me.Dirty = False
End If

Then you have

Code:

DoCmd.SetWarningsFalse
DoCmd.OpenQuery"Qry_UCR_New_AppendOfficer"
DoCmd.SetWarningsTrue

Why the query when you can use command to create a new record:

Code:

DoCmd.GoToRecord ,, acNewRec

Next you:
open form "Frm_UCR_AdminReport",
move to the last record,
close form "Frm_UCR_AdminReport",
open form "Frm_UCR_AdminReport" and
move to the last record.
I can't get my head around what this does.

Using

Code:

DoCmd.GoToRecord ,, acLast

moves the current record to the last record IN THE DATASET, not necessarily to the new record.

OK, now to the txtRPT_DATE_AfterUpdate() sub. I modified your code a little...
It looks like you might have a control name wrong (in red) and be missing a line (in blue):

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])  '<<-- shouldn't this be Me.txtRptDay??
  Me.txtREPORT_NUMBER = DCount("*","Tbl_UCR_UniformIncidentOffense", "[RPT_DATE]=#" &[txtRPT_DATE] & "#")

  'recreate the case number
  Me.txtCase_Number_Config = Me.txtRptYear &"-" & Me.txtRptMonth & "-"& Me.txtRptDay & "-" & Me.txtREPORT_NUMBER

  Me.txtCase_Number =  Me.txtCase_Number_Config  ' set the control to the new case number 

  Me.Refresh   ' now refresh the form to see changes. 

End Sub

I think these two lines are in the wrong order. You requery before you set the control value.

Code:

    Me.txtCase_Number_Config.Requery
    Me.txtCase_Number =  Me.txtCase_Number_Config
End Sub

You could post the dB for analysis - remove any sensitive data first, compact and repair, then Zip.

Trying to update a bound textbox from an unbound textbox after update

Thread Tools

Trying to update a bound textbox from an unbound textbox after update

Similar Threads

Update of Textbox in Main form from Subform Textbox afterupdate

How to update tables from bound and unbound fields from forms

unbound textbox to bound textbox

Transfer data from Unbound Textbox to Bound Textbox

Unbound textbox in bound form

Posting Permissions