VBA SQL insert query

[bootwalker]

Hi all,

I could use your help on this one. I get an error (Run-time error '438' Object doesn't support this property or methode), when I try to execute the following insert query:

SQLstring = "Insert into Hours (Entry date, Uses, Hours, Task, Description, )" & _
                            "Values ('#" & Me.Controls("day" & I).Caption & "#','" & TempVars!tmpUserID & "'," & _
                            "'" & Me.Controls("hours" & I) & "','" & Me.Controls("labelTask") & "'," & _
                            "'" & Me.Controls("comment" & I) & "')"

I'm not sure but i think the problen lies around this part

'#" & Me.Controls("day" & I).Caption & "#'

It trys to set as a date, a label.caption but i'm not realy sure, it could also be the TempVars.

Any one got a hint?

Thank You
Boot

 

[Estuardo]

G'd afternoon,
Replace the "#" with ' (single quote) that should work.

G'd luck

 

[bootwalker]

Thx for the fast reply Estuardo but your suggestion does not work.

 

[Estuardo]

Yes, you right, i didn't check your code, as just saw the "#", my mistake.
Let's see.
Why Me.Controls("xxxx" & I)? change all that to the normal reference for the control like me.myday.caption. What is TempVars!tmpUserID? is that a form?

 

[MSAccessRookie]

    SQLstring = "Insert into Hours (Entry date, Uses, Hours, Task, Description[B][COLOR=red], [/COLOR][COLOR=red])"[/COLOR][/B] & _
        [COLOR=red][B]"Values[/B][/COLOR] ('#" & Me.Controls("day" & I).Caption & "#',[B][COLOR=red]'[/COLOR][/B]" & TempVars!tmpUserID & "[B][COLOR=red]'[/COLOR][/B]," & _
        "[B][COLOR=red]'[/COLOR][/B]" & Me.Controls("hours" & I) & "[B][COLOR=red]'[/COLOR][/B],'" & Me.Controls("labelTask") & "'," & _
        "'" & Me.Controls("comment" & I) & "')"

I can see a couple of issues with this code, although I am not sure if any of these things causes your error.

• the insert List has a "," at the end of the last entry and it should not.
• There needs to be a space in between the end of the Insert list ")" and the word Values"
• If [Uses] or [Hours] is a number, then single quotes are not required.

-- Rookie

 

[bootwalker]

Yes, you right, i didn't check your code, as just saw the "#", my mistake.
Let's see.
Why Me.Controls("xxxx" & I)? change all that to the normal reference for the control like me.myday.caption. What is TempVars!tmpUserID? is that a form?

Well I'm actially working on a form yes. I have labels for the date and textboxes for the hours and the comment. The Me.Controls("xxx"&I) is uses to access all textboxes with help of a Loop where I is the iteration.
TempVars!tmpUserID is a temporary variable set when the user logs in the database.

I can see a couple of issues with this code, although I am not sure if any of these things causes your error.

• the insert List has a "," at the end of the last entry and it should not.
• There needs to be a space in between the end of the Insert list ")" and the word Values"
• If [Uses] or [Hours] is a number, then single quotes are not required.

-- Rookie

Thx for the Tip, I changed the code as you said but it did not affect the error. By the way I also tried to remove the ' (single quotes) as you said but that leaves me with a syntax error.

 

[bootwalker]

small update! I found the error, instead of [User] i wrote [Uses]
That solves the error message. But after a few syntax errors I'm left with a "End of statement" error on this code:

SQLstring = "Insert into Hours (Entry date, User, Hours, Task, Description) " & _
                            "Values ('#" & Me.Controls("day" & I).Caption & "# ','" & TempVars!tmpUserID & "','" & _
                            " & Me.Controls("hours" & I) & "','" & Me.Controls("labelTask").caption & "'," & _
                            "'" & Me.Controls("comment" & I) & "')"

The error highlights the [hours] in Me.Controls("hours"&I)

Any hints? I removes the quotes, did not work

 

[bootwalker]

I took your advise Estuardo and tried a simple insert query.

SQLstring = "insert into Hours (Entry date, User, Hours, Task, Description) " & _
                "Values (#24/4/2013#,31,6,147,'wiugwiuf')"
                DoCmd.RunSQL (SQLstring)

Even that does not work. I get the error message "Syntax error in INSERT INTO statement" and DoCmd.RunSQL... is highlighted yellow.
I checked the data types they are all correct. I even tried with a ; at the end of the query without any result. Im wondering if I should also use the ID (which should be autogenerated because it's an autonumber) what do you think?

 

[mdlueck]

Im wondering if I should also use the ID (which should be autogenerated because it's an autonumber) what do you think?

If the ID field is an autonumber field in the database table, then you may not include that column in your SQL INSERT statement. You may only reference the ID field in SELECT / UPDATE SQL statements, once the autonumber ID value has been assigned during a successful INSERT SQL statement.

I prefer to put my SQL statements in ADO objects and whenever possible to use the ADO.Command / ADO.Parameters type objects among the various ADO options available. Following are some posts with example SQL statements using ADO objects:

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

Example of SQL SELECT using ADODB.Recordset object to Access FE temp table to scan the FE temp table and perform operations
http://www.access-programmers.co.uk/forums/showthread.php?p=1214730 #post1214730

 

[PeterF]

I think because your field Entry date contains a space the SQL wil throw a error.
Try placing square brackets around the filed name, "[Entry Date]".

User is a reserverd word in SQL, temporary solve it by placing square brackets. Best way to avoid this kind of hickups by avoiding reserved names, see for info this Microsoft list.

 

[bootwalker]

Hey guys, I finally got the solution:

SQLstring = "Insert into [Hours]([Entry date], [User], [Hours], [Task], [Description]) " & _
                            "Values (#" & Me.Controls("day" & I).Caption & "#,'" & TempVars!tmpUserID & "'," & _
                            "'" & Me.Controls("hours" & I).Value & "','" & Me.Controls("labelTask").Caption & "'," & _
                            "'" & Me.Controls("comment" & I).Value & "')"

What I had to do:
- add a .caption, .value to all form controls in the SQL string
- remove the ' single quotes in front and after the date
- used [] on all field to avoid using reserved words (Thx to PeterF)

to mdlueck: Your solution with the ADO Command is a little to complex for me but I will look closerly at it later and maybe even use it. Thx anyway

I wanted to THANK YOU all for the help you gave me!!