Help with simple INSERT statement 4

[Rmcta]

I have tblLog with 2 fields only:
id - autonumber (primary key)
DateTime - which is always current date and time - Now()

I wish to create a form with a command button that when clicked will insert current date and time into my table.

Can somebody please help me with the on click code?

Thank you so very much,

Rama

 

[coco86]

in the click event of your button:

currentdb.execute "insert into tblLog (DateTime) values (" & now & ""



-Coco

 

[Rmcta]

Thank you Coco,

However I am getting an error: SYNTAX ERROR in INSERT INTO statement

Run-Time Error 3134

This is what I have entered:

Private Sub cmdCall_Click()

CurrentDb.Execute "insert into tblLog (DateTime) values (" & Now & ""

End Sub

 

[dvannoy]

Is datetime a textbox??

 

[coco86]

A few minor adjustments:

"insert into tblLog ([DateTime]) values ('" & now() & "');"

-Coco

 

[Rmcta]

No, it is Date/Time - Format: General Date

 

[Rmcta]

yes....GREAT! It works!
Thank you very, very much!

Rama

 

[Rmcta]

Oh boy..I hope you don't get upset now.
I wish to add one more thing to the form.

I txtbox that displays the result of a query which counts the records. I need the text box to be updated every time a user click on the command button we've set up above, to show the new count.

This is the query:
SELECT Count(*) AS CallCount
FROM tblLog
WHERE (((Left([tblLog].[DateTime],8))=Date()));

Can you guide me with this?

Thank you in advance

 

[coco86]

If you're using Access 2000, try it this way:

Dim rs as new ADODB.recordset

set rs = currentproject.connection.execute _
         "SELECT Count(*) AS CallCount " & _
         "FROM tblLog " & _
         "WHERE (((Format([datetime],'mm/dd/yyyy'))=" & _
         "Format(Date(),'mm/dd/yyyy')));"

if not rs.eof then me.txtbox = rs!CallCount

rs.close
set rs = nothing

-Coco

 

[Rmcta]

I am using Access 2000.
I am getting compile error. Argument not optional. The word that gets highlighed when I run the code is: execute
In line (Set rs = CurrentProject.Connection.Execute)

I have inserted the code in the onclick event as follows:

Private Sub cmdCall_Click()


CurrentDb.Execute "Insert into tblLog ([DateTime]) values ('" & Now() & "');"

Dim rs As New ADODB.Recordset

Set rs = CurrentProject.Connection.Execute & _
"SELECT Count(*) AS CallCount " & _
"FROM tblLog " & _
"WHERE (((Format([datetime],'mm/dd/yyyy'))=" & _
"Format(Date(),'mm/dd/yyyy')));"

If Not rs.EOF Then Me.txtBox = rs!CallCount

rs.Close
Set rs = Nothing


End Sub

 

[dvannoy]

Do you have a reference set to ADO?

 

[coco86]

Your problem is probably here

Set rs = CurrentProject.Connection.Execute & _
"SELECT Count(*) AS CallCount " & _
"FROM tblLog " & _
"WHERE (((Format([datetime],'mm/dd/yyyy'))=" & _
"Format(Date(),'mm/dd/yyyy')));"

Take out the ampersand and see if it works

-Coco

 

[Rmcta]

When I remove the & the entire Set rs statement gets highlighted in red

 

[coco86]

ok, try this instead

Set rs = CurrentProject.Connection.Execute _
("SELECT Count(*) AS CallCount " & _
"FROM tblLog " & _
"WHERE (((Format([datetime],'mm/dd/yyyy'))=" & _
"Format(Date(),'mm/dd/yyyy')));&quot

-Coco

 

[Rmcta]

YES! .
You are GREAT! It works.
Thank you very, very much!