What am I missing

[Triscuit]

I keep getting an Error 94 when I click the button that I have applied this code to. I think I am close but obviously missing something. When it doesn't give the error code and claims to have saved the data doesn't actually end up on the table. Please help.

Option Compare Database


Private Sub manageWorkRecords_Click()
DoCmd.OpenForm "frmWorkRecordEdit", , , , acFormEdit
End Sub


Private Sub qryAppendMassWorkRecords_Click()


On Error Resume Next
DoCmd.RunSQL "DROP TABLE Tmp"

'Error handling
On Error GoTo cmdOK_Click_err

'Declare Vars
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rstRecords As Recordset
Dim dynamicSQL As String
Dim strSQL As String
Dim insertSQL As String
Dim strTable As String
Dim hrsWorked As Integer
Dim Rate As Integer
Dim DateStarted As Date
Dim DateEnded As Date
Dim pgmWorked As Integer
Dim volId As String

Set db = CurrentDb

'Create new temp table to hold values from the form
strSQL = "CREATE TABLE Tmp (VolunteerID VARCHAR(20), DateStarted DATE,DateEnded DATE, HoursWorked INT, Rate INT, WorkCategory INT);"
db.Execute strSQL

'Grab the values for the static vars and assign them
For Each ctl In Me.Controls
If ctl.Name = "DateStarted" Then
DateStarted = ctl.Value
End If
If ctl.Name = "DateEnded" Then
DateEnded = ctl.Value
End If
If ctl.Name = "Rate" Then
Rate = ctl.Value
End If
If ctl.Name = "HoursWorked" Then
hrsWorked = ctl.Value
End If
If ctl.Name = "WorkCategory" Then


pgmWorked = ctl.Value
End If

Next ctl

'If combo box length > 0, create an INSERT statement to add the record to the temp table
For Each ctl In Me.Controls
If ctl.ControlType = acComboBox Then
'Category is the only combo box other than the ones for volunteer names; make sure it's not getting picked up
If ctl.Name <> "WorkCategory" Then
'Verify that the field contains a name
If Len(ctl.Value) > 0 Then
volId = ctl.Value
'Reset the dynamicSQL to the initial code & add form values
dynamicSQL = "INSERT INTO Tmp VALUES (" _
& "'" & volId & "' , #" & DateStarted & "#, #" & DateEnded & "#, " & hrsWorked & ", " & Rate & ", " & pgmWorked & ");"
db.Execute dynamicSQL
End If
End If
End If
Next ctl

'Insert records from temp table into tblWorkRecords table
insertSQL = "INSERT INTO tblWorkRecords (Volunteer, DateStarted, DateEnded, HoursWorked, Rate, WorkCategory) SELECT VolunteerID, DateStarted, DateEnded, HoursWorked, Rate, WorkCategory FROM Tmp;"
db.Execute insertSQL

'Confirmation message box
MsgBox ("Your records have been saved.")

'Clear form data
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
If Len(ctl.ControlSource) = 0 Then
ctl.Value = Null
End If
Case Else
' pass
End Select
Next


'Error handling
cmdOK_Click_exit:

Set qdf = Nothing
Set db = Nothing

Exit Sub


'Error handling
cmdOK_Click_err:


MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note the following details:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_exit

End Sub

[pbaldy]

It would help to know what line it errors on. You can temporarily comment out the "On Error..." line. You can get errors from the SQL:

db.Execute strSQL, dbFailOnError

without the last bit, it will fail silently. There's also:

BaldyWeb-Immediate window

[Triscuit]

Thanks for looking at my question. All I know is what the error message says.

An unexpected error has occurred. Please not the following details: Error Number: 94 Description: invalid us of Null.

But when it doesn't give an error it just doesn't work which might be worse. I think the error comes in when I don't have someone listed under volunteer.

[pbaldy]

Did you modify the code as I suggested?

[Triscuit]

Where would I put that code? I am really new at this. I am just adapting an existing system.

[pbaldy]

Anyplace you use Execute, add the bit at the end.

[Perceptus]

Does the Code Compile? Or only Error Out when you run it?

[Triscuit]

Does the Code Compile? Or only Error Out when you run it?

Sometimes it claims to have saved. But when I go to check the information didn't save onto the table as it is supposed to.

[Triscuit]

Anyplace you use Execute, add the bit at the end.

Okay. The issues seems to be with the date fields now. Don't know what changed but it now adds the information to the right table etc. But puts 12/30/1899 as the start and end date even though I gave a different date. Thoughts?

Thanks guys we're getting there. Don't know how we are but we are.

[Triscuit]

Okay it worked with the date error twice and now it is giving me error 3201. Which is a new error. Saying "You cannot add or change a record because a related record is required in table tbleVolunteers." But it did just work a second ago but with the 1899 date. BAAAAH

[pbaldy]

Did you try the link in post 2, which will let you see what the finished SQL looks like?

[pbaldy]

It probably worked with a different volunteer. The related record error comes from referential integrity, where you've basically told Access "don't let a record in this table with a volunteer that doesn't exist in tbleVolunteers".

[Triscuit]

I added the code that was suggested. But I don't know if I did it right or what it was supposed to do to help. I thought it would tell me where my issues are. But that wasn't the result.

There is no real data in this database yet I am still just playing. Anyone willing to let me send it to you for analysis and fixing. I am quite at a loss.

[Triscuit]

When it sorta works it does everything right accept the date. So if we could fix that, we would have a good start. Anyone see why my date fields would pull through a 12/30/1899 instead of the date I enter in those fields??

[Triscuit]

Here is what I have right now:

Option Compare Database


Private Sub manageWorkRecords_Click()
DoCmd.OpenForm "frmWorkRecordEdit", , , , acFormEdit
End Sub


Private Sub qryAppendMassWorkRecords_Click()


On Error Resume Next
DoCmd.RunSQL "DROP TABLE Tmp"

'Error handling
On Error GoTo cmdOK_Click_err

'Declare Vars
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rstRecords As Recordset
Dim dynamicSQL As String
Dim strSQL As String
Dim insertSQL As String
Dim strTable As String
Dim hrsWorked As Integer
Dim Rate As Integer
Dim DateStarted As Date
Dim DateEnded As Date
Dim pgmWorked As Integer
Dim volId As String

Set db = CurrentDb

'Create new temp table to hold values from the form
strSQL = "CREATE TABLE Tmp (VolunteerID VARCHAR(20), DateStarted DATE,DateEnded DATE, HoursWorked INT, Rate INT, WorkCategory INT);"
db.Execute strSQL, dbFailOnError

'Grab the values for the static vars and assign them
For Each ctl In Me.Controls
If ctl.Name = "DateStarted" Then
DateStarted = ctl.Value
End If
If ctl.Name = "DateEnded" Then
DateEnded = ctl.Value
End If
If ctl.Name = "Rate" Then
Rate = ctl.Value
End If
If ctl.Name = "HoursWorked" Then
hrsWorked = ctl.Value
End If
If ctl.Name = "WorkCategory" Then
pgmWorked = ctl.Value
End If

Next ctl

'If combo box length > 0, create an INSERT statement to add the record to the temp table
For Each ctl In Me.Controls
If ctl.ControlType = acComboBox Then
'Category is the only combo box other than the ones for volunteer names; make sure it's not getting picked up
If ctl.Name <> "WorkCategory" Then
'Verify that the field contains a name
If Len(ctl.Value) > 0 Then
volId = ctl.Value
'Reset the dynamicSQL to the initial code & add form values
dynamicSQL = "INSERT INTO Tmp VALUES (" _
& "'" & volId & "' , #" & DateStarted & "#, #" & DateEnded & "#, " & hrsWorked & ", " & Rate & ", " & pgmWorked & ");"
db.Execute dynamicSQL, dbFailOnError
End If
End If
End If
Next ctl

'Insert records from temp table into tblWorkRecords table
insertSQL = "INSERT INTO tblWorkRecords (Volunteer, DateStarted, DateEnded, HoursWorked, Rate, WorkCategory) SELECT VolunteerID, DateStarted, DateEnded, HoursWorked, Rate, WorkCategory FROM Tmp;"
db.Execute insertSQL, dbFailOnError

'Confirmation message box
MsgBox ("Your records have been saved.")

'Clear form data
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
If Len(ctl.ControlSource) = 0 Then
ctl.Value = Null
End If
Case Else
' pass
End Select
Next


'Error handling
cmdOK_Click_exit:

Set qdf = Nothing
Set db = Nothing

Exit Sub


'Error handling
cmdOK_Click_err:


MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note the following details:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_exit

End Sub