Hit Ctrl G and look in the immediate window.
Copy the code and paste it in a post.
Hit Ctrl G and look in the immediate window.
Copy the code and paste it in a post.
Gotcha - thanks. And the result is as expected:
sActivity = Checking / Approval
sDepartment = Electronics Dep
sProject = Test
sHours = 1.5
sDescription = xxxaa
SO is there a reason it wouldnt insert it into the table/subform? Thanks again
The debug.print was just a debugging techniques to see exactly what values were assigned to your variables. These variables are used in the MySql string and get executed in the DoCmd.RunSQL
At this point we know what values were being used, but nothing more.
Is the TableTimesheet.Hours Field a Text, Number, or Date/Time data type? If it's a number or Date/Time then that's your problem:
If the Field is a number, use the following:
(take out the single quotes around sHours)Code:Mysql = "INSERT INTO TimesheetTable (Activity, Department, Project, Hours, Description) Values ('" & sActivity & "', '" & sDepartment & "', '" & sProject & "', " & sHours & ", '" & sDescript & "')"
Thanks but it still doesnt work... Just to reitterate my other question. The fact I am trying to submit using a button and I also have a bound entity - does this matter? Can you do both or does it have to be one or the other?
cheers
Try adding the bolded lines and commenting out the Green lines
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
On Error GoTo AddToSheet_Click_Error
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]
'
Debug.print MySql
'DoCmd.RunSQL Mysql
db.Execute Mysql, dbFailOnError
'DoCmd.SetWarnings True
Me!TimesheetTableSubform.Form.Requery
On Error GoTo 0
Exit Sub
AddToSheet_Click_Error:
MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure AddToSheet_Click"
End Sub
Ooo, I didn't catch that you're using a Bound Form.
First, I'll answer the question directly: Yes, but it's more pain than it's worth and largely unnecessary.
Indirectly: If you're using a Bound Form, I wouldn't use a Submit button at all. If you have to have one, you can just make it run the following code to force a Record save:
Code:RunCommand acCmdSaveRecord
So is a bound form a better method than a submit button method? I have been toying between the two. I had all my fields bound so they were in the subform, but I couldnt then move on to get it so I could add another record. Rawb are you saying the code you mentioned does this? And if so how/where do I add it?
If you havent noticed (due to the amount of posts) Im not the best when its comes to access :P
As to whether or not you should use a Bound Form over an Unbound one, I guess it depends on your personal preference and what you want to do. Unless you're using aggregate data (Your Recordset has a GROUP BY or DISTINCT Clause in it) it's generally easier and faster to use a Bound Form.
No, the code I used does nothing except write any changes to your current Record. It's like a Save Button.
If you want to create a new Record, you can make a button with the following code:
Both this code and what I posted earlier would go in the button's OnClick Event.Code:RunCommand acCmdRecordsGoToNew
Access forms have built-in record navigation (bar at bottom of form), unless you turned it off in the form's properties. If you don't find the built-in navigation 'friendly' enough, will need code - VBA or macros - to program events like button Click. Some find macros easier to understand but they are harder to debug.
If you want to use VBA code to make project more 'user friendly', I strongly advise you learn debugging techniques. The link at bottom of my post is a good tutorial. The more friendly, the more code.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.