Moving a date from a form to a table automatically

  • Thread starter Thread starter Neil
  • Start date Start date
N

Neil

I am creating a form that will have a box containing
todays date.

Either when a button is pressed, or on closing the form i
want this date to be put into a table.

I need either some code for how to do this or if i need to
run an append query of some sort.

Any help would be gratefully received.

Thanks

Neil
 
Neil said:
I am creating a form that will have a box containing
todays date.

Either when a button is pressed, or on closing the form i
want this date to be put into a table.

I need either some code for how to do this or if i need to
run an append query of some sort.

Any help would be gratefully received.

Thanks

Neil
Click to expand...

There's many ways to do this, here is one.

You didn't say whether you wanted to add a new record in the table or update
an existing record. This example is for adding a new record. I'm assumuing
you are using ADO, although I think it would be the same for ADO, almost.


Put the following code into your "OnClick" event of the button:


Dim dbs As ADODB.Connection, strSQL As String
If IsDate(Me.MyDate) Then
strSQL = "INSERT INTO MyTableName (MyDateFieldName) VALUES (" &
Me.MyDate & ")"
Set dbs = CurrentProject.Connection ' connect to the database
dbs.Execute strSQL ' run the query
dbs.Close ' close database
Set dbs = Nothing
End If
End If
 
I am creating a form that will have a box containing
todays date.

Either when a button is pressed, or on closing the form i
want this date to be put into a table.

I need either some code for how to do this or if i need to
run an append query of some sort.

Any help would be gratefully received.

Thanks

Neil
Click to expand...

Normally no code is needed at all - simply base the Form on the table,
and bind the textbox to the date/time field, or a query on the table
containing that field.

What's the Recordsource of the form? What other information is in the
table? is it displayed on the form, or does the form display
information from some other table?
 
I'm sorry but i don't know what ADO is.

In the code you gave, what goes in as CurrentProject?

At the moment all tables are in the same database, ,and i
require the date from the form to be added/appended to the
table when the button is clicked.

So for example the box will include todays date, and so
when the button is pressed todays date will be added to
the table.

Don't know if this makes it any clearer?

Thanks for your help

Neil
 
Using the code you supplied i can get the table to be
added to but the date added is not the one in the box
(todays date), it is 30/121899.

Can you help please?

Is it because i'm not using ADO? Also do i need to put
anything different in where your code says
CurrentProject.connection
 
Using the code you supplied i can get the table to be
added to but the date added is not the one in the box
(todays date), it is 30/121899.
Click to expand...

That's the date corresponding to 0...
->>
Of you want *today's date* rather than the value (whatever it is) in
the textbox Me.Mydate, replace Me.MyDate by Date().
 
John

I have a text box on my form that will contain todays date
i.e. "=Date()" as default Value.

Here is my code, i want to be able to either get today's
date from the text box or just from the system, wherever
as long as the table is updated with that date.

Private Sub Import_Click()

Dim dbs As ADODB.Connection, strSQL As String

If IsDate(Me.ImportDate) Then
strSQL = "INSERT INTO tblImportDates (Import_Date)
VALUES (" & Me.ImportDate & ")"
Set dbs = CurrentProject.Connection
dbs.Execute strSQL
dbs.Close
Set dbs = Nothing
End If

End Sub

'ImportDate' is the Text box on my form, my table is
called 'tblImportDates', and the field in the table whcih
to add the date to is 'Import_Date'.

This date will be a check as to when the form was last
used.

I've made the changes as you explained but it still adds
the date value for '0' to my table not todays date.

Thanks again for you time

Neil
 
I've made the changes as you explained but it still adds
the date value for '0' to my table not todays date.
Click to expand...

I'm guessing it's interpreting 2/26/2004 as a division operation
giving .0000191... - try delimiting it with # marks to indicate that
it's a date:

strSQL = "INSERT INTO tblImportDates (Import_Date)
VALUES (#" & Me.ImportDate & "#)"
 
Back
Top