Help with SQL code in Module

[angellrp]

Please help I've written the code below so that when a name is selected in a list (lstName) the code adds a line to a table (tbl_logon_history) to show when someone logged on. However I keep getting an error:
"Syntax error in INSERT INTO statement"

Please can someone tell me what I'm doing wrong as I've re-written the code lots of times and I still get an error.

Thanks in advance for any help.

code:
Dim Table
Dim FldDate
Dim FldStaff
Dim ValDate
Dim ValStaff
Dim SQL

Table = "tbl_Logon_History"
FldDate = "Date_Time"
FldStaff = "Staff_Member"
ValDate = Now
ValStaff = lstName.Value

SQL = "INSERT INTO Table ( FldDate, FldStaff ) SELECT ( ValDate , ValStaff );"

DoCmd.RunSQL SQL

 

[namliam]

Tip: Dim should allways be Dim ... AS somthing, i.e. String, integer
If not it becomes a variant with all the problems that (may) come with it.

If you want to use variables you have to replace them into your sql string.
Like so...
SQL = "INSERT INTO " & Table & " ( " & FldDate & "," & FldStaff & ") SELECT ( " & ValDate & " , " & " ValStaff " & ");"

If you dont it will try to search for a table called "table" which obviously doesnt excist.

Also if your variable is a string you will have to enclose it by quotes (') and dates by hashes (#), numbers dont need to be enclosed.
Like so:
#" & ValDate & "#

Good luck!

 

[angellrp]

Hi, thanks for that, but it still s gives me errors so I took out some variables etc and did this:

Dim ValDate As Date
Dim SQL As String

ValDate = Now

SQL = "INSERT INTO 'tbl_Logon_History' ( 'Date_Time','Staff_Member') SELECT ( #" & ValDate & "# , " & lstName.Value & ");"
DoCmd.RunSQL SQL


However I now get the following error:
"Syntax error in query. Incomplete query clause"

sorry to be a pain but I don't understand what I'm doing wrong.

 

[namliam]

table names and column names are NOT enclosed... only values...
Actually if table and column names are to be enclosed (in cases where you have spaces or special characters *&^%$# etc in the names) it should be square brackets []

I knew I should have said this in my first post *hits self upside the head*

 

[angellrp]

Thank you but it still throws an error back - sorry

here is my code now:

Dim ValDate As Date
Dim SQL As String

ValDate = Now

SQL = "INSERT INTO tbl_Logon_History ( Date_Time, Staff_Member) SELECT ( #" & ValDate & "# , " & lstName.Value & ");"
DoCmd.RunSQL SQL

error message -
"Syntax error (missing operator) in query expression '( #<date here># , <name here>)'.


Although I did try this sql
SQL = "INSERT INTO tbl_Logon_History ( Date_Time, Staff_Member) SELECT ( #" & ValDate & "# , '" & lstName.Value & "');"

And got error message -
"Syntax error (comma) in query expression '( #<date here># , '<name here>')'.


If that makes sense.

 

[namliam]

Using "Currentdb.execute" instead of Docmd.RunSQL it works...
Also it prevents you from getting the popup "do you want to do this" ??

Ah wait... I now see the problem... it is the () in the select part.... that is not needed...
SQL = "INSERT INTO tbl_Logon_History ( Date_Time, Staff_Member) SELECT #" & ValDate & "# , " & lstName.Value & ";"

 

[MSAccessRookie]

Using "Currentdb.execute" instead of Docmd.RunSQL it works...
Also it prevents you from getting the popup "do you want to do this" ??

Ah wait... I now see the problem... it is the () in the select part.... that is not needed...
SQL = "INSERT INTO tbl_Logon_History ( Date_Time, Staff_Member) SELECT #" & ValDate & "# , " & lstName.Value & ";"

Two observations:

1. Isn't there supposed to be a FROM part of the SELECT clause?
2. Does there need to be " ' " characters around the lstName.Value? If it is a String, there would need to be.

In the event that ValDate="11/25/2008", and lstName.Value="JohnDoe", then:

SQL = "INSERT INTO tbl_Logon_History ( Date_Time, Staff_Member )
    SELECT #" & ValDate & "# , [B][COLOR=#ff0000]'[/COLOR][/B]" & lstName.Value & [B][COLOR=black]"[COLOR=red]'[/COLOR];"[/COLOR][/B]

would be interpreted as:

SQL = "INSERT INTO tbl_Logon_History ( Date_Time, Staff_Member )
    SELECT #[COLOR=seagreen][B]11/25/2008[/B][/COLOR]# , [COLOR=black][COLOR=red][B]'[/B][/COLOR][/COLOR][COLOR=seagreen][B]JohnDoe[/B][COLOR=black][COLOR=red][B]' [/B][/COLOR][/COLOR][/COLOR][COLOR=red][B]{ FROM Some Table or Query }[/B][/COLOR];

 

[namliam]

Two observations:

1. Isn't there supposed to be a FROM part of the SELECT clause?
2. Does there need to be " ' " characters around the lstName.Value? If it is a

1. 
   
   1)
   No... this syntax acts as Oracles " Insert into ... values ...."
   This just inserts values not something from a table.
   
   2)
   He tried both... and I did tell him already... Dates ## strings '' numbers nothing.
   
   Greets from a dark amsterdam

 

[angellrp]

Thanks so much, it all works now.
*hugs*