Access Message

S

Sue Wilkes

I have a form linked to a table. If the user edits a record a field
'reasonsforedit' must be completed with a least 12 characters, before
selecting the command button to save, print report & email report. All seems
to work okay it gives the required message to complete data but it is then
followed by an access message 'the setting you entered isn't valid for this
property' the user is then placed back on the form to complete data entry.
How do I stop this second message appearing any help would be greatly
appreciated. Below is the code behind the command button and in the forms
beforeupdate event.

Private Sub EditRecSave_Click()
On Error GoTo Err_EditRecSave_Click

Me.Dirty = False

'PRINT DOCUMENT CONTROL REPORT

Dim strWhere As String
Dim stDocName As String

If Me.NewRecord Then 'Check there is a record to print
'MsgBox "Select a record to print"
Else
strWhere = "[RegisterNumber] = """ & Me.[RegisterNumber] & """"
If Not IsNull(Me.DateReceived) And Not IsNull(Me.ReceivedFrom) Then
DoCmd.OpenReport "RegEntryINFormRpt", acViewPreview, , strWhere
Else
strWhere = "[RegisterNumber] = """ & Me.[RegisterNumber] & """"
If Not IsNull(Me.DateSent) And Not IsNull(Me.SentTo) Then
DoCmd.OpenReport "RegEntryOUTFormRpt", acViewPreview, , strWhere
End If
End If
End If

'OPEN OUTLOOK AND SENT REPORT TO EMAIL AS ATTACHMENT READY FOR SENDING

stDocName = "EMailRptEEOUT"
stDocName = "EMailRptEE"
strWhere = "[RegisterNumber] = """ & Me.[RegisterNumber] & """"

If Not IsNull(Me.DateReceived) Then
DoCmd.OpenReport "EMailRptEE", acViewPreview, , strWhere
DoCmd.RunMacro "EMailRptM"
Else
If Not IsNull(Me.DateSent) Then
DoCmd.OpenReport "EMailRptEEOUT", acViewPreview, , strWhere
DoCmd.RunMacro "EMailRptMOUT"
End If
End If

Exit_EditRecSave_Click:
Exit Sub

Err_EditRecSave_Click:
MsgBox Err.Description
Resume Exit_EditRecSave_Click

End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim StrError As String

'Audit Log code

UpdateLog = CurrentUser() & " " & Now()
bWasNewRecord = Me.NewRecord
Call AuditEditBegin("GenReg", "audTmpGenReg", "IDNo", Nz(Me.IDNo, 0),
bWasNewRecord)

'Validation Code - Standard Controls

If IsNull(Me.DeptCode) Then
MsgBox conMESSAGE, vbExclamation, "SELECT DEPARTMENT CODE BEFORE
CONTINUING"
Cancel = True
Me.DeptCode.SetFocus
Else
If IsNull(Me.Designation) Then
MsgBox conMESSAGE, vbExclamation, "SELECT DESIGNATION BEFORE
CONTINUING"
Cancel = True
Me.Designation.SetFocus
Else
If IsNull(Me.CompanyNames) Then
MsgBox conMESSAGE, vbExclamation, "ENTER COMPANY NAME DETAILS BEFORE
CONTINUING"
Cancel = True
Me.CompanyNames.SetFocus
Else
If IsNull(Me.Subject) Then
MsgBox conMESSAGE, vbExclamation, "ENTER SUBJECT DETAILS BEFORE
CONTINUING"
Cancel = True
Me.Subject.SetFocus
Else
If IsNull(Me.Hyperlink1) Then
MsgBox conMESSAGE, vbExclamation, "ENTER HYPERLINK BEFORE CONTINUING"
Cancel = True
Me.Hyperlink1.SetFocus
Else

'IN / OUT DATA VALIDATION

If Me.AddNewRecIN.Enabled = True And Len(Me.ReceivedFrom & vbNullString)
= 0 Then
MsgBox conMESSAGE, vbExclamation, "ENTER RECEIVED FROM TO DETAILS
BEFORE CONTINUING"
Cancel = True
Me.ReceivedFrom.SetFocus
Else
If Me.AddNewRecIN.Enabled = True And Len(Me.DateReceived & vbNullString)
= 0 Then
MsgBox conMESSAGE, vbExclamation, "ENTER DATE AS DD/MM/YYYY BEFORE
CONTINUING"
Cancel = True
Me.DateReceived.SetFocus
Else
If Me.AddNewRecOUT.Enabled = True And Len(Me.SentTo & vbNullString) = 0
Then
MsgBox conMESSAGE, vbExclamation, "ENTER SENT TO DETAILS BEFORE
CONTINUING"
Cancel = True
Me.SentTo.SetFocus
Else
If Me.AddNewRecOUT.Enabled = True And Len(Me.DateSent & vbNullString) =
0 Then
MsgBox conMESSAGE, vbExclamation, "ENTER DATE AS DD/MM/YYYY BEFORE
CONTINUING"
Cancel = True
Me.DateSent.SetFocus
Else
If Me.EditRec.Enabled = True And Len(Me.ReasonsforEdit) < 12 Then
Cancel = True
MsgBox "REASONS FOR EDIT MUST BE AT LEAST 12 CHARACTERS"
End If


'Delete Data Validation


If Me.ConfirmRecforDel.Enabled = True And Len(Me.DeleteDate &
vbNullString) = 0 Then
MsgBox conMESSAGE, vbExclamation, "DELETION DATE MUST BE COMPLETED
BEFORE CONTINUING"
Cancel = True
Me.DeleteDate.SetFocus
Else
If Me.ConfirmRecforDel.Enabled = True And Len(Me.PersonReqDel &
vbNullString) = 0 Then
MsgBox conMESSAGE, vbExclamation, "PERSONS REQUIRING THE DELETION
MUST BE COMPLETED BEFORE CONTINUING"
Cancel = True
Me.PersonReqDel.SetFocus
Else
If Me.ConfirmRecforDel.Enabled = True And Len(Me.DeptReqDel &
vbNullString) = 0 Then
MsgBox conMESSAGE, vbExclamation, "DEPARTMENT REQUIRING THE DELETION
MUST BE COMPLETED BEFORE CONTINUING"
Cancel = True
Me.DeptReqDel.SetFocus
Else
If Me.ConfirmRecforDel.Enabled = True And Len(Me.ReasonforDel &
vbNullString) = 0 Then
MsgBox conMESSAGE, vbExclamation, "REASONS FOR DELETION MUST BE
COMPLETED BEFORE CONTINUING"
Cancel = True
Me.ReasonforDel.SetFocus

End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

End Sub
 
N

NetworkTrade

first thought is to go to the table and doublecheck what is the field
property type of your 'reasonsforedit' field......obviously they are typing
in text and it doesn't like text...is it a number or date type field defined
in the table..??
--
NTC


Sue Wilkes said:
I have a form linked to a table. If the user edits a record a field
'reasonsforedit' must be completed with a least 12 characters, before
selecting the command button to save, print report & email report. All seems
to work okay it gives the required message to complete data but it is then
followed by an access message 'the setting you entered isn't valid for this
property' the user is then placed back on the form to complete data entry.
How do I stop this second message appearing any help would be greatly
appreciated. Below is the code behind the command button and in the forms
beforeupdate event.

Private Sub EditRecSave_Click()
On Error GoTo Err_EditRecSave_Click

Me.Dirty = False

'PRINT DOCUMENT CONTROL REPORT

Dim strWhere As String
Dim stDocName As String

If Me.NewRecord Then 'Check there is a record to print
'MsgBox "Select a record to print"
Else
strWhere = "[RegisterNumber] = """ & Me.[RegisterNumber] & """"
If Not IsNull(Me.DateReceived) And Not IsNull(Me.ReceivedFrom) Then
DoCmd.OpenReport "RegEntryINFormRpt", acViewPreview, , strWhere
Else
strWhere = "[RegisterNumber] = """ & Me.[RegisterNumber] & """"
If Not IsNull(Me.DateSent) And Not IsNull(Me.SentTo) Then
DoCmd.OpenReport "RegEntryOUTFormRpt", acViewPreview, , strWhere
End If
End If
End If

'OPEN OUTLOOK AND SENT REPORT TO EMAIL AS ATTACHMENT READY FOR SENDING

stDocName = "EMailRptEEOUT"
stDocName = "EMailRptEE"
strWhere = "[RegisterNumber] = """ & Me.[RegisterNumber] & """"

If Not IsNull(Me.DateReceived) Then
DoCmd.OpenReport "EMailRptEE", acViewPreview, , strWhere
DoCmd.RunMacro "EMailRptM"
Else
If Not IsNull(Me.DateSent) Then
DoCmd.OpenReport "EMailRptEEOUT", acViewPreview, , strWhere
DoCmd.RunMacro "EMailRptMOUT"
End If
End If

Exit_EditRecSave_Click:
Exit Sub

Err_EditRecSave_Click:
MsgBox Err.Description
Resume Exit_EditRecSave_Click

End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim StrError As String

'Audit Log code

UpdateLog = CurrentUser() & " " & Now()
bWasNewRecord = Me.NewRecord
Call AuditEditBegin("GenReg", "audTmpGenReg", "IDNo", Nz(Me.IDNo, 0),
bWasNewRecord)

'Validation Code - Standard Controls

If IsNull(Me.DeptCode) Then
MsgBox conMESSAGE, vbExclamation, "SELECT DEPARTMENT CODE BEFORE
CONTINUING"
Cancel = True
Me.DeptCode.SetFocus
Else
If IsNull(Me.Designation) Then
MsgBox conMESSAGE, vbExclamation, "SELECT DESIGNATION BEFORE
CONTINUING"
Cancel = True
Me.Designation.SetFocus
Else
If IsNull(Me.CompanyNames) Then
MsgBox conMESSAGE, vbExclamation, "ENTER COMPANY NAME DETAILS BEFORE
CONTINUING"
Cancel = True
Me.CompanyNames.SetFocus
Else
If IsNull(Me.Subject) Then
MsgBox conMESSAGE, vbExclamation, "ENTER SUBJECT DETAILS BEFORE
CONTINUING"
Cancel = True
Me.Subject.SetFocus
Else
If IsNull(Me.Hyperlink1) Then
MsgBox conMESSAGE, vbExclamation, "ENTER HYPERLINK BEFORE CONTINUING"
Cancel = True
Me.Hyperlink1.SetFocus
Else

'IN / OUT DATA VALIDATION

If Me.AddNewRecIN.Enabled = True And Len(Me.ReceivedFrom & vbNullString)
= 0 Then
MsgBox conMESSAGE, vbExclamation, "ENTER RECEIVED FROM TO DETAILS
BEFORE CONTINUING"
Cancel = True
Me.ReceivedFrom.SetFocus
Else
If Me.AddNewRecIN.Enabled = True And Len(Me.DateReceived & vbNullString)
= 0 Then
MsgBox conMESSAGE, vbExclamation, "ENTER DATE AS DD/MM/YYYY BEFORE
CONTINUING"
Cancel = True
Me.DateReceived.SetFocus
Else
If Me.AddNewRecOUT.Enabled = True And Len(Me.SentTo & vbNullString) = 0
Then
MsgBox conMESSAGE, vbExclamation, "ENTER SENT TO DETAILS BEFORE
CONTINUING"
Cancel = True
Me.SentTo.SetFocus
Else
If Me.AddNewRecOUT.Enabled = True And Len(Me.DateSent & vbNullString) =
0 Then
MsgBox conMESSAGE, vbExclamation, "ENTER DATE AS DD/MM/YYYY BEFORE
CONTINUING"
Cancel = True
Me.DateSent.SetFocus
Else
If Me.EditRec.Enabled = True And Len(Me.ReasonsforEdit) < 12 Then
Cancel = True
MsgBox "REASONS FOR EDIT MUST BE AT LEAST 12 CHARACTERS"
End If


'Delete Data Validation


If Me.ConfirmRecforDel.Enabled = True And Len(Me.DeleteDate &
vbNullString) = 0 Then
MsgBox conMESSAGE, vbExclamation, "DELETION DATE MUST BE COMPLETED
BEFORE CONTINUING"
Cancel = True
Me.DeleteDate.SetFocus
Else
If Me.ConfirmRecforDel.Enabled = True And Len(Me.PersonReqDel &
vbNullString) = 0 Then
MsgBox conMESSAGE, vbExclamation, "PERSONS REQUIRING THE DELETION
MUST BE COMPLETED BEFORE CONTINUING"
Cancel = True
Me.PersonReqDel.SetFocus
Else
If Me.ConfirmRecforDel.Enabled = True And Len(Me.DeptReqDel &
vbNullString) = 0 Then
MsgBox conMESSAGE, vbExclamation, "DEPARTMENT REQUIRING THE DELETION
MUST BE COMPLETED BEFORE CONTINUING"
Cancel = True
Me.DeptReqDel.SetFocus
Else
If Me.ConfirmRecforDel.Enabled = True And Len(Me.ReasonforDel &
vbNullString) = 0 Then
MsgBox conMESSAGE, vbExclamation, "REASONS FOR DELETION MUST BE
COMPLETED BEFORE CONTINUING"
Cancel = True
Me.ReasonforDel.SetFocus

End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Data Validation from Delete Command Button 1
Making a macro only work on certain files 2
Data Validation & Cancel Exit Command 5
Match style 0
VBA Coding Help for Beginner 0
Prompt For Replace in Selection And More... 0
Delete Button 2
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0

Top