Inserting information to a table on form close

[DawnTreader]

Hello All

i need a little help with a problem i am having when closing forms and inserting data into a table as the form closes. this applys to a few forms and so i need to have a generic understanding of how to correct my problem.

here is opening code that happens in the load event.

Dim sqlIssueTimeLog As String
Dim CurrentTimeLog As Integer
sqlIssueTimeLog = "INSERT INTO stblItemTimeLog (EmployeeID, ItemID, DateTimeOpened, ItemName, FormName, ModuleName) VALUES (" & Forms.frmManageAssets.Form.cboEmployee & ", " & Me.IssueID & ", #" & Now() & "#, 'Issue', '" & Me.Form.Name & "', 'Server Management')"
' MsgBox sqlIssueTimeLog
DoCmd.RunSQL sqlIssueTimeLog
CurrentTimeLog = DMax("TimeLogID", "stblItemTimeLog", "EmployeeID =" & Forms.frmManageAssets.Form.cboEmployee & " AND ItemID = " & Me.IssueID & " AND ItemName = 'Issue'")

Me.txtCurrentTimeLog = CurrentTimeLog

after the user does what i need in the form and they close the form i need this to happen:

Private Sub Form_Close()

Dim sqlIssueTimeLog As String


sqlIssueTimeLog = "UPDATE stblItemTimeLog SET stblItemTimeLog.DateTimeClosed = #" & Now() & "# WHERE stblItemTimeLog.TimeLogID = " & Me.txtCurrentTimeLog & " AND stblItemTimeLog.EmployeeID = " & [Forms]![frmManageAssets]![cboEmployee] & " AND stblItemTimeLog.ItemID= " & [Forms]![frmManageIssues]![IssueID] & " AND stblItemTimeLog.DateTimeOpened <#" & Now()& "#"

' MsgBox sqlIssueTimeLog

CurrentDb.Execute sqlIssueTimeLog

Call RefreshLists

End Sub

the problem is that because the form is "closed" the IssueID has been closed and the insert fails. is there a different event that i should be doing this on? i tried the unload event but got the same results.

i was also going to try and cause it to not happen in certain circumstancesbut that isnt working either.

basically, i just need to know how to do this insert. this general code is used on a few forms.

any and all help is appreciated.

 

[Jon Lewis]

I would disable the built in close button (the x) and use a command button
on the form that saves any record changes, then runs your 'Close' function
and then closes the form. However it would still be possible to close the
form by closing the Application without running the code. So to prevent
this do as follows:
' In the declarations section of the Form's Class Module:
Dim AllowClose As Boolean

'In the Form's Unload event:
If Not AllowClose Then
Cancel = True
MsgBox "Please click the Form's command button first"
End If

In the command button's Click event
'Your close function...
AllowClose = True
If Me.Dirty Then Me.Dirty = False 'saves record changes
DoCmd.Close acForm, Me.Name 'closes form

HTH

Jon

Hello All

i need a little help with a problem i am having when closing forms and
inserting data into a table as the form closes. this applys to a few forms
and so i need to have a generic understanding of how to correct my problem.

here is opening code that happens in the load event.

Dim sqlIssueTimeLog As String
Dim CurrentTimeLog As Integer
sqlIssueTimeLog = "INSERT INTO stblItemTimeLog (EmployeeID, ItemID,
DateTimeOpened, ItemName, FormName, ModuleName) VALUES (" &
Forms.frmManageAssets.Form.cboEmployee & ", " & Me.IssueID & ", #" & Now() &
"#, 'Issue', '" & Me.Form.Name & "', 'Server Management')"
' MsgBox sqlIssueTimeLog
DoCmd.RunSQL sqlIssueTimeLog
CurrentTimeLog = DMax("TimeLogID", "stblItemTimeLog", "EmployeeID = " &
Forms.frmManageAssets.Form.cboEmployee & " AND ItemID = " & Me.IssueID & "
AND ItemName = 'Issue'")

Me.txtCurrentTimeLog = CurrentTimeLog

after the user does what i need in the form and they close the form i need
this to happen:

Private Sub Form_Close()

Dim sqlIssueTimeLog As String


sqlIssueTimeLog = "UPDATE stblItemTimeLog SET
stblItemTimeLog.DateTimeClosed = #" & Now() & "# WHERE
stblItemTimeLog.TimeLogID = " & Me.txtCurrentTimeLog & " AND
stblItemTimeLog.EmployeeID = " & [Forms]![frmManageAssets]![cboEmployee] & "
AND stblItemTimeLog.ItemID= " & [Forms]![frmManageIssues]![IssueID] & " AND
stblItemTimeLog.DateTimeOpened <#" & Now() & "#"

' MsgBox sqlIssueTimeLog

CurrentDb.Execute sqlIssueTimeLog

Call RefreshLists

End Sub

the problem is that because the form is "closed" the IssueID has been closed
and the insert fails. is there a different event that i should be doing this
on? i tried the unload event but got the same results.

i was also going to try and cause it to not happen in certain circumstances
but that isnt working either.

basically, i just need to know how to do this insert. this general code is
used on a few forms.

any and all help is appreciated.