I have currently migrating my applications to Access 2007 ADODB from Access 2000. Most of what I have done now works. I am having a minor problem with one of my forms though. When I save the form, and close it using
DoCmd.Close acForm, Me.Name
, the code closes the access application. The following is the code that runs when I click on the save button
'check for errors first
fCancel = False
' form_beforeUpdate (fCancel)
' first check that mandatory fields have been completed
Dim strField As String
Call glrEnableButtons(Me, Me)
If Me.txtTotalRecs > 1 Then
Call glrTglNavButtons(Me, True)
Else
Call glrTglNavButtons(Me, False)
End If
If IsNull(Me.txtCompanyName) Then
MsgBox "All yellow fields must be completed", vbExclamation
strField = "CompanyName"
End If
If IsNull(Me.txtAddress1) Then
MsgBox "All yellow fields must be completed", vbExclamation
strField = "Address1"
End If
If IsNull(Me.ReviewRequester_Label) Then
MsgBox "All yellow fields must be completed", vbExclamation
strField = "ReviewRequester"
End If
If IsNull(Me!Score) Then
Me!Score = Me.txtFinalScore
End If
Pass_Fail
If fCancel Then
Exit Sub
End If
Dim strSQL As String
'close history
history_update
If formMode = "Add" Then
createCompanyRecords
createReviewRecords
formMode = ""
End If
If MsgBox("Do you wish to update the history", vbQuestion + vbYesNo) = vbYes Then
'Update history
createHistoryRecords
End If
' SaveRecord Me
'save the records into the database
Call glrchangeformstate(Me, glrcFormModeBrowse)
Call glrEnableButtons(Me, Me)
Me.cmdDelete.Enabled = True
Me.cmdHistory.Enabled = False
below are the 3 subs that you are called during the save process, that saves the records
Private Sub createCompanyRecords()
Dim strSQL As String
Dim company_id As Integer
company_id = DMax("[companyid]", "tblCompany") + 1
strSQL = "INSERT INTO tblCompany (companyid, CompanyName,Address1,Address2,Address3,Address4,Postcode,Telephone,Email,Contact)" & _
"values ('" & company_id & "', '" & Me.txtCompanyName & "' ,'" & Me.txtAddress1 & "' ,'" & Me.txtAddress2 & "' , '" & Me.txtAddress3 & "' ," & _
"'" & Me.txtAddress4 & "', '" & Me.txtPostcode & "', '" & Me.txtTelephone & "', '" & Me.txtEmail & "'," & _
"'" & Me.txtContact & "')"
cmdExecute strSQL
End Sub
Private Sub createReviewRecords()
Dim strSQL As String
Dim company_id As Integer
company_id = DMax("[companyid]", "tblCompany")
strSQL = "INSERT INTO tblReview (CompanyID,ReviewDate,ReviewerID,Requester,Commitment,Duties,Cooperation,Arrangements," & _
"Risk,CDM,Signed,Passed,Score) values ('" & company_id & "' ,'" & Me.txtReviewDate & "' ,'" & Me.cboReviewerName & "' ," & _
"'" & Me.txtReviewRequester & "' ,'" & Me.Quest1 & "', '" & Me.Quest2 & "', '" & Me.Quest3 & "', '" & Me.Quest4 & "'," & _
"'" & Me.Quest5 & "', '" & Me.txtCDMScore & "', '" & Me.Signed & "', '" & Me.lblFail & "','" & Me.txtFinalScore & "')"
cmdExecute strSQL
End Sub
Private Sub createHistoryRecords()
Dim strSQL As String
Dim company_id As Integer
company_id = DMax("[companyid]", "tblCompany")
If DCount("[CompanyID]", "tblHistory1", "[CompanyID] =" & company_id) > 0 Then
strSQL = "execute spUpdateHistory '" & txtCompanyName & "'"
cmdExecute strSQL
'Update history1
strSQL = "execute spUpdateHistory1 '" & txtCompanyName & "'"
Exit Sub
ElseIf DCount("[CompanyID]", "tblReview", "[CompanyID] =" & company_id) > 0 Then
strSQL = "execute spInsertHistory1 '" & txtCompanyName & "'"
Exit Sub
ElseIf DCount("[CompanyID]", "tblHistory1", "[CompanyID] =" & company_id) > 0 Then
strSQL = "execute spInsertHistory & company_id & "
End If
cmdExecute strSQL
End Sub
Please can someone tell me what I am doing wrong?