Text combo box 1

[ncopeland]

Hi

I have text combo box that I control using the following. In the control source I have =Date().

Because the user does not have to enter on the box this field appears not be updated. When I try to write this value to another field I get 30/12/1899. Is there a function to update this text box so that the correct value is retrieved.

KR

NC.

 

[SkipVought]

Hi,

Why not a BUTTON?

Skip,

_[red][/red]

 

[ncopeland]

Hi

I have a button that runs a selection of code.


I want the code to write the value in the date text box to a field in a table, but unfortunately it writes the date 30/12/1899 to the field in the table.

KR


NC.

 

[Zygor]

Have you tried aomething like format(date(),"mm/dd/yyyy")?
Also, what is the format of the field in the table. Is it a date field?

 

[SkipVought]

"... write the value in the date text box to a field in a table"

The problem is that you have a ComboBox without any COMBINATION OF VALUES.

Sub MyButton_Click()
   [YourTableField] = Date()
End Sub

Skip,

_[red][/red]

 

[ncopeland]

Hi

Back to the start to give a flavour. I have a form that has a text box that in the control source has the following formula =Date().

I also have a button that runs an event procedure. When this button is clicked a number of procedures are run. One function is to take the value stored in the text box and write it to a field in a table.

The problem seems to be that the text box is not updated as the user does not enter the value in the box.

I need to know the code that updates that text box without the user actually having to enter on the value in it.

KR

NC.

 

[SkipVought]

There is no magic. Some EVENT has to run to execute the code. If its a ComboBox, the CHANGE or CLICK events can run the code.

What's the use of a TEXTBOX if no one enters any TEXT into it?

You can have some code that PUTS a value into the TextBox that you can run on the Form Activate or some other event.

Skip,

_[red][/red]

 

[ncopeland]

Hi

Have got sort off to the bottom of this. I have a variable that is declared using dim.

ie. dim costingdate as date

In access you can use date() to pick up todays date. In coding date is a reserved word.

So how do I get todays date into a field that I can write to a field in a table.

KR

NC.

 

[SkipVought]

costingdate = Date

Skip,

_[red][/red]

 

[kjv1611]

Not 100% sure what you are wanting here, ncopeland, but it sounds like you just want today's date entered into a table field when a user creates a new record.

If that is the case, go to the actual table, and for the field that needs updating, open it in Design View.

Then, look at the bottom of the window, where you'll see the "General" tab. There, find the field marked, "Default Value".

There, enter "=Date()" but without the quotes.

Then, every time someone enters a new record, the date will be automatically populated. If you need a specific date format, be sure to specify that in the field, "Format", assuming you have "Date/Time" as the Data Type.

Let us know if that's what you're looking for.

Then, if you want that to show on the form, just have a text box with that as the record source, but do not allow updating/entering the text box. If it's not necessary, just leave it off of the form.

--

"If to err is human, then I must be some kind of human!" -Me

 

[ncopeland]

Hi Skip


When I write this field to the table I get the date 30/12/1899 stored in the field.

example of sql statement

theCostingDate = Date

strSql = "INSERT INTO CostingRun ([Year],[Period],[DateRun])"
strSql = strSql & "SELECT " & theYear & ", " & thePeriod & ", " & theCostingDate


The field is a date/time field in the table stored as a short date.


KR

NC.

 

[kjv1611]

Are you sure that the system date isn't just really messed up on your testing machine?

It looks like it is set to being:

December 30, 1899.

Have you checked the system date to verify that is not the problem?

--

"If to err is human, then I must be some kind of human!" -Me

 

[ncopeland]

Hi

When I debug the code the date is shown as 09/04/2007 but it is written to the table it is written as 30/12/1899 which I believe is as far as windows can go back on short dates.

KR

NC.

 

[SkipVought]

"stored as a short date" FORMATTED as a short date.

You INSERT [DateRun]

This makes absolutely no sense...

strSql = strSql & "SELECT " & theYear & ", " & thePeriod & ", " & theCostingDate

You don't Select VALUES, you select FIELD NAMES.


Skip,

_[red][/red]

 

[ncopeland]

Hi Skip


theYear = Me.Combo3
thePeriod = Me.Combo0

Me.CostingDate = Date


theCostingDate = Date

If Not DCount("Period", "CostingRun", "Year = " & theYear & " AND Period = " & thePeriod) = 0 Then
MsgBox "Period and Year already exist"
Exit Sub
End If
strSql = "INSERT INTO CostingRun ([Year],[Period],[DateRun])"
strSql = strSql & "SELECT " & theYear & ", " & thePeriod & ", " & theCostingDate


'Debug.Print strSql

DoCmd.RunSQL strSql

The code in full

KR

NC

 

[SkipVought]

The SQL would be...

    strSql = "INSERT INTO CostingRun ([Year],[Period],[DateRun]) "
    strSql = strSql & "VALUES (" & theYear & ", " & thePeriod & ", " & theCostingDate & ")"

Skip,

_[red][/red]

 

[ncopeland]

Hi Skip

The strsql seems to be generated properly.
ie 2007,1,09/04/2007 when I watch it.

The daterun field in the table is stored as date/time but when it updates the field on the table I get 30/12/1899 stored in the field.

KR

NC.

 

[SkipVought]

30/12/1899 is a datevalue of ZERO.

Of course

theYear & ", " & thePeriod & ", " & theCostingDate

is

2007,1,09/04/2007

The question is the SYNTAX.

So what are the values in [Year] & [Period] after the INSERT?

Skip,

_[red][/red]

 

[ncopeland]

Hi Skip

Values in table for year and period after insert are 2007 and 1.

KR

NC.

 

[SkipVought]

strSql = strSql & "VALUES (" & theYear & ", " & thePeriod & ", #" & theCostingDate & "#)"

Skip,

_[red][/red]