Why do I get a compile error?

[shabbaranks]

Hi guys,



Im getting a compile error for the following code:

Private Sub AddToSheet_Click()
Dim sActivity As String
Dim sDepartment As String
Dim sProject As String
Dim sHours As Integer
Dim sUser As String
Dim Msql As String

sActivity = Me.Activity
sDepartment = Me.Department
sProject = Me.Project
sHours = Me.Hours
sUser = Me.User
sDescription = Me.Description

Mysql = "INSERT INTO TimesheetTable ( Activity, Department, Project, Hours, User ) SELECT '" & sActivity & "' AS Activity, '" & sDepartment & "' AS Deptartment, '" & sProject & "' AS Project, '" & sHours & "' AS Hours, '" & sUser & "' AS User,'" & sDescription & "' AS Description"
DoCmd.SetWarnings False
DoCmd.RunSQL Mysql
DoCmd.SetWarnings True
End Sub

It flags up method or data member not found - can anyone shed some light on this for me please?

Thanks

[June7]

Which line does the compiler fail on? Your SQL has more values than fields. Try this for the SQL statement:

Mysql = "INSERT INTO TimesheetTable (Activity, Department, Project, Hours, User, Description) Values ('" & sActivity & "', '" & sDepartment & "', '" & sProject & "', " & sHours & ", '" & sUser & "', '" & sDescription & "')"

The apostrophe delimiter is needed for text data. I assume sHours is number. Use # as delimiter for date values.

[shabbaranks]

Thanks but still no joy Below is what I am trying now

Private Sub AddToSheet_Click()
Dim sActivity As String
Dim sDepartment As String
Dim sProject As String
Dim sHours As Integer
Dim sUser As String
Dim Msql As String

sActivity = Me.Activity
sDepartment = Me.Department
sProject = Me.Project
sHours = Me.Hours
sUser = Me.User
sDescription = Me.Description

Mysql = "INSERT INTO TimesheetTable ( Activity, Department, Project, Hours, User ) VALUES ('" & sActivity & "', '" & sDepartment & "', '" & sProject & "', " & sHours & ", '" & sUser & "', '" & sDescription & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL Mysql
DoCmd.SetWarnings True
End Sub

It errors on Me.Hours - any thoughts? Thanks

[June7]

You declared sHours as integer. Is it possible that data could have decimal? If so, need to declare as Double. Although I would expect a different error message for datatype conflict. Hour is a reserved word in VBA and Access. Should not use reserved words as names. This might be causing a conflict in the code. Either rename the field or try Me.[Hour] or Me![Hour].

[shabbaranks]

Oops yeah I did but I still get this error Method or Data member not found as screen grabbed here:

http://postimage.org/image/1ptc75dqc/

[June7]

The code is not changed, did you try my suggestion?

[shabbaranks]

Appreciate your help on this and apologies, the previous error was because I was trying to add data to a field which is already populated - the name.

I keep taking about 1 step forward and 10 steps back... Ive amended my code as I am binding the username part to the subform and the rest I am trying to submit using the click button - is this possible? If so it seems like its doing something but nothing gets added to the subform here is the current code:

Private Sub AddToForm_Click()
Dim sActivity As String
Dim sDepartment As String
Dim sProject As String
Dim sHours As Double
Dim sDescription As String
Dim Msql As String

sActivity = Me.Activity
sDepartment = Me.Department
sProject = Me.Project
sHours = Me.[Hour]
sDescription = Me.[Description]

Mysql = "INSERT INTO TimesheetTable (Activity, Department, Project, Hours, Description) Values ('" & sActivity & "', '" & sDepartment & "', '" & sProject & "', " & sHours & ", '" & sDescription & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL Mysql
DoCmd.SetWarnings True
End Sub

I can see why people get frustrated with Access

[orange]

Put in a debug.print statement to see what values Access is dealing with.

Debug.print "sActivity = " & Me.Activity & vbcrlf _
& "sDepartment = " & Me.Department & vbcrlf _
& "sProject = " & Me.Project & vbcrlf _
& "sHours = " & Me.[Hour] & vbcrlf _
& "sDescription = " & Me.[Description]

[shabbaranks]

Does this replace the current code or do I just add it at the bottom?

Thanks

[SteveH2508]

Put Option Explicit at the top of your module.

It will then show you that you have declared MSql as String but you are using MySql variable.

[June7]

Access isn't the issue here. It is VBA and SQL programming you are struggling with. All of the identified errors are spelling and syntax mistakes. Sorry, I did not spot the error in the variable spelling. Include Option Explicit in the header of every module and spelling errors like that could be revealed. Check out the link at bottom of my post to learn debugging techniques.

Debug.Print would be temporary code just for the debugging effort. You would remove or comment it after issue resolved.

[orange]

Put the Debug.print before the DoCmd.RunSQL line

And as the others have said

use Option Explicit at the top of the module. It will highlight differences in spelling and unDimmed variables

[shabbaranks]

Done and done but still it doesnt work... I dont get any error messages which is really throwing me. Just to confirm - is it ok to have a single bound entity and try and usa vba to add the rest to the subform? Code as ever - below

Option Compare Database
Option Explicit
Private Sub AddToSheet_Click()
Dim sActivity As String
Dim sDepartment As String
Dim sProject As String
Dim sHours As Double
Dim sDescript As String
Dim Mysql As String
sActivity = Me.Activity
sDepartment = Me.Department
sProject = Me.Project
sHours = Me.Hour
sDescript = Me.[Description]
Mysql = "INSERT INTO TimesheetTable (Activity, Department, Project, Hours, Description) Values ('" & sActivity & "', '" & sDepartment & "', '" & sProject & "', '" & sHours & "', '" & sDescript & "')"
DoCmd.SetWarnings False
Debug.Print
DoCmd.RunSQL Mysql
DoCmd.SetWarnings True
Me!TimesheetTableSubform.Form.Requery
End Sub

[orange]

Please show us the output from the debug.print

And use the whole debug.print

Debug.print "sActivity = " & Me.Activity & vbcrlf _
& "sDepartment = " & Me.Department & vbcrlf _
& "sProject = " & Me.Project & vbcrlf _
& "sHours = " & Me.[Hour] & vbcrlf _
& "sDescription = " & Me.[Description]


Also, your comment earlier

I can see why people get frustrated with Access

is little misleading.

You have to learn Access, just like other software. It isn't a simple On/OFF, as you are discovering.

[shabbaranks]

Not having used debug.print what and where am I expecting an output? I have added the code as instructed before the DoCmd.RunSQL Mysql as here:

Option Compare Database
Option Explicit
Private Sub AddToSheet_Click()
Dim sActivity As String
Dim sDepartment As String
Dim sProject As String
Dim sHours As Double
Dim sDescript As String
Dim Mysql As String
sActivity = Me.Activity
sDepartment = Me.Department
sProject = Me.Project
sHours = Me.Hour
sDescript = Me.[Description]
Mysql = "INSERT INTO TimesheetTable (Activity, Department, Project, Hours, Description) Values ('" & sActivity & "', '" & sDepartment & "', '" & sProject & "', '" & sHours & "', '" & sDescript & "')"
DoCmd.SetWarnings False
Debug.Print "sActivity = " & Me.Activity & vbCrLf _
& "sDepartment = " & Me.Department & vbCrLf _
& "sProject = " & Me.Project & vbCrLf _
& "sHours = " & Me.[Hour] & vbCrLf _
& "sDescription = " & Me.[Description]
DoCmd.RunSQL Mysql
DoCmd.SetWarnings True
Me!TimesheetTableSubform.Form.Requery
End Sub

And I dont experience anything - not even a tickle under the chin. What am I doing wrong? Thanks