Check for missing field in edit form

G

Guest

I have a form (RegEditForm) linked to a control source table (HYInReg) In the
table the field Hyperlink1 is set Yes for required. If a user makes a change
and accidently leaves any of the other fields blank I can get a message to
appear using the beforeupdate event procedure as follows.
Private Sub ForwardedTo_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[ForwardedTo]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "FORWARDED TO DETAILS MUST BE COMPLETED BEFORE SAVING IS PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub
However, if the user deletes the hyperlink that was there I am unable to get
the same code to recognise that the field is now empty and display an error
message. I have tried using the same code in the controls other events but
no luck. Any help would be greatly appreciated I'm at a loss what to try
next. Many thanks
 
G

Graham R Seach

Sue,

The easiest way is to check the field's length.
If Len("" & Me.ForwardedTo) = 0 Then
'The field is empty
End If

I always like to trim the value too:
If Len("" & Trim(Me.ForwardedTo)) = 0 Then
'The field is empty
End If

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
G

Guest

Thank you Graham the code does display the message however when I right click
on the hyperlink control and follow the menu down to 'edit hyperlink' it
keeps looping the error message and I cannot continue. I've tried setting
focus with 'Me.[Hyperlink1].SetFocus but this also gives an error message run
time 2108 any help is appreciated for this green newbie. many thanks
Sue

Graham R Seach said:
Sue,

The easiest way is to check the field's length.
If Len("" & Me.ForwardedTo) = 0 Then
'The field is empty
End If

I always like to trim the value too:
If Len("" & Trim(Me.ForwardedTo)) = 0 Then
'The field is empty
End If

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Sue Wilkes said:
I have a form (RegEditForm) linked to a control source table (HYInReg) In
the
table the field Hyperlink1 is set Yes for required. If a user makes a
change
and accidently leaves any of the other fields blank I can get a message to
appear using the beforeupdate event procedure as follows.
Private Sub ForwardedTo_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[ForwardedTo]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "FORWARDED TO DETAILS MUST BE COMPLETED BEFORE SAVING IS
PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub
However, if the user deletes the hyperlink that was there I am unable to
get
the same code to recognise that the field is now empty and display an
error
message. I have tried using the same code in the controls other events
but
no luck. Any help would be greatly appreciated I'm at a loss what to try
next. Many thanks
Click to expand...
Click to expand...
 
G

Graham R Seach

Sue,

I can't reproduce the behaviour you're experiencing. Can you post the exact
code you have, and the steps you take to reproduce it (from the time you
open the form)?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Sue Wilkes said:
Thank you Graham the code does display the message however when I right
click
on the hyperlink control and follow the menu down to 'edit hyperlink' it
keeps looping the error message and I cannot continue. I've tried setting
focus with 'Me.[Hyperlink1].SetFocus but this also gives an error message
run
time 2108 any help is appreciated for this green newbie. many thanks
Sue

Graham R Seach said:
Sue,

The easiest way is to check the field's length.
If Len("" & Me.ForwardedTo) = 0 Then
'The field is empty
End If

I always like to trim the value too:
If Len("" & Trim(Me.ForwardedTo)) = 0 Then
'The field is empty
End If

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Sue Wilkes said:
I have a form (RegEditForm) linked to a control source table (HYInReg)
In
the
table the field Hyperlink1 is set Yes for required. If a user makes a
change
and accidently leaves any of the other fields blank I can get a message
to
appear using the beforeupdate event procedure as follows.
Private Sub ForwardedTo_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[ForwardedTo]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "FORWARDED TO DETAILS MUST BE COMPLETED BEFORE SAVING IS
PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub
However, if the user deletes the hyperlink that was there I am unable
to
get
the same code to recognise that the field is now empty and display an
error
message. I have tried using the same code in the controls other events
but
no luck. Any help would be greatly appreciated I'm at a loss what to
try
next. Many thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Graham, I have managed to stop the looping but now can get the message to
appear? I tried posting you code in many of the events on control
'Hyperlink1' and even tried adding to the command button 28 again no luck.
If I click on the cmdPrint button it gives the error 3314 as the required
table field is set to Yes, other than that I cannot get any error message to
appear regarding Hyperlink1, any thoughts. I have included below the coding
used on the form in the hope that it helps. I have noticed that when I use
tab to move throught the fields it always and only misses out the Hyperlink1
field, is this what is affecting the error coding. Many thanks for sticking
with this.
Regards, Sue

Option Compare Database

Dim bWasNewRecord As Boolean

Private Sub Combo18_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "RegisterNumber = " & Str(Nz(Me![Combo18], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo25_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[IDNo] = " & Str(Nz(Me![Combo25], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Private Sub cmdPrint_Click()

Dim StrWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"


Else
StrWhere = "[RegisterNumber] = """ & Me.[RegisterNumber] & """"
DoCmd.OpenReport "EditFormRpt", acViewPreview, , StrWhere
End If

End Sub



Private Sub Combo29_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[IDNo] = " & Str(Nz(Me![Combo29], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Command28_Click()

If IsNull(Me.[Reason(s)forEdit]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "REASONS FOR EDITING MUST BE COMPLETED BEFORE SAVING IS
PERMITTED"
Cancel = True 'wont let the user continue
Me.[Reason(s)forEdit].SetFocus
End If

'I always like to trim the value too:
If Len("" & Trim(Me.ForwardedTo)) = 0 Then
'The field is empty????????
End If

End Sub

Private Sub CompanyName_s__BeforeUpdate(Cancel As Integer)
If IsNull(Me.[CompanyName(s)]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "COMPANY NAME DETAILS MUST BE COMPLETED BEFORE SAVING IS PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub

Private Sub Subject_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[Subject]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "SUBJECT DETAILS MUST BE COMPLETED BEFORE SAVING IS PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub

Private Sub ForwardedTo_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[ForwardedTo]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "FORWARDED TO DETAILS MUST BE COMPLETED BEFORE SAVING IS PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub

Private Sub ReceivedFrom_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[ReceivedFrom]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "RECEIVED FROM DETAILS MUST BE COMPLETED BEFORE SAVING IS PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
Call AuditDelEnd("audTmpHYInReg", "audHYInReg", Status)
End Sub

Private Sub Form_AfterUpdate()
Call AuditEditEnd("HYInReg", "audTmpHYInReg", "audHYInReg", "IDNo",
Nz(Me!IDNo, 0), bWasNewRecord)
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

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

End Sub

Private Sub Form_Delete(Cancel As Integer)
Call AuditDelBegin("HYInReg", "audTmpHYInReg", "IDNo", Nz(Me.IDNo, 0))
End Sub

Private Sub Form_Load()
DoCmd.Maximize
End Sub


Private Sub Command32_Click()
On Error GoTo Err_Command32_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "RegEditForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command32_Click:
Exit Sub

Err_Command32_Click:
MsgBox Err.Description
Resume Exit_Command32_Click

End Sub

Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "RegEntryForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click

End Sub











Graham R Seach said:
Sue,

I can't reproduce the behaviour you're experiencing. Can you post the exact
code you have, and the steps you take to reproduce it (from the time you
open the form)?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Sue Wilkes said:
Thank you Graham the code does display the message however when I right
click
on the hyperlink control and follow the menu down to 'edit hyperlink' it
keeps looping the error message and I cannot continue. I've tried setting
focus with 'Me.[Hyperlink1].SetFocus but this also gives an error message
run
time 2108 any help is appreciated for this green newbie. many thanks
Sue

Graham R Seach said:
Sue,

The easiest way is to check the field's length.
If Len("" & Me.ForwardedTo) = 0 Then
'The field is empty
End If

I always like to trim the value too:
If Len("" & Trim(Me.ForwardedTo)) = 0 Then
'The field is empty
End If

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a form (RegEditForm) linked to a control source table (HYInReg)
In
the
table the field Hyperlink1 is set Yes for required. If a user makes a
change
and accidently leaves any of the other fields blank I can get a message
to
appear using the beforeupdate event procedure as follows.
Private Sub ForwardedTo_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[ForwardedTo]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "FORWARDED TO DETAILS MUST BE COMPLETED BEFORE SAVING IS
PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub
However, if the user deletes the hyperlink that was there I am unable
to
get
the same code to recognise that the field is now empty and display an
error
message. I have tried using the same code in the controls other events
but
no luck. Any help would be greatly appreciated I'm at a loss what to
try
next. Many thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Graham R Seach

Sue,

Rather than checking the values after clicking Command28 (which I assume is
the Save button), I'd disable the button by default, and only enable it if
all the conditions are met.

Create a Sub to validate the data, and to set the button's Enabled property
only if the data is valid. Call this Sub during the form's Current event,
and in the AfterUpdate event for each of the relevent controls.

Private Sub ValidateData()
If Len("" & Trim(Me.[Reason(s)forEdit])) And Len("" &
Trim(Me.[RegisterNumber])) > 0 Then
Me.Command28.Enabled = False
Goto Proc_Exit
Else
Me.Command28.Enabled = True
End If

'Add as many conditions as you like...
If some_other_condition_is_met Then
Me.Command28.Enabled = False
Goto Proc_Exit
Else
Me.Command28.Enabled = True
End If

Proc_Exit:
End Sub

In this way, you don't need to keep repeating the same thing over and over,
AND you never have to display a MsgBox, because the user can never execute
an invalid action. This is a fundamental principle in user interface design,
which states that all actions available to the user must be valid (legal)
ones.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Sue Wilkes said:
Hi Graham, I have managed to stop the looping but now can get the message
to
appear? I tried posting you code in many of the events on control
'Hyperlink1' and even tried adding to the command button 28 again no luck.
If I click on the cmdPrint button it gives the error 3314 as the required
table field is set to Yes, other than that I cannot get any error message
to
appear regarding Hyperlink1, any thoughts. I have included below the
coding
used on the form in the hope that it helps. I have noticed that when I use
tab to move throught the fields it always and only misses out the
Hyperlink1
field, is this what is affecting the error coding. Many thanks for
sticking
with this.
Regards, Sue

Option Compare Database

Dim bWasNewRecord As Boolean

Private Sub Combo18_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "RegisterNumber = " & Str(Nz(Me![Combo18], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo25_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[IDNo] = " & Str(Nz(Me![Combo25], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Private Sub cmdPrint_Click()

Dim StrWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"


Else
StrWhere = "[RegisterNumber] = """ & Me.[RegisterNumber] & """"
DoCmd.OpenReport "EditFormRpt", acViewPreview, , StrWhere
End If

End Sub



Private Sub Combo29_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[IDNo] = " & Str(Nz(Me![Combo29], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Command28_Click()

If IsNull(Me.[Reason(s)forEdit]) And Not IsNull(Me.[RegisterNumber])
Then
MsgBox "REASONS FOR EDITING MUST BE COMPLETED BEFORE SAVING IS
PERMITTED"
Cancel = True 'wont let the user continue
Me.[Reason(s)forEdit].SetFocus
End If

'I always like to trim the value too:
If Len("" & Trim(Me.ForwardedTo)) = 0 Then
'The field is empty????????
End If

End Sub

Private Sub CompanyName_s__BeforeUpdate(Cancel As Integer)
If IsNull(Me.[CompanyName(s)]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "COMPANY NAME DETAILS MUST BE COMPLETED BEFORE SAVING IS
PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub

Private Sub Subject_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[Subject]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "SUBJECT DETAILS MUST BE COMPLETED BEFORE SAVING IS PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub

Private Sub ForwardedTo_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[ForwardedTo]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "FORWARDED TO DETAILS MUST BE COMPLETED BEFORE SAVING IS
PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub

Private Sub ReceivedFrom_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[ReceivedFrom]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "RECEIVED FROM DETAILS MUST BE COMPLETED BEFORE SAVING IS
PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
Call AuditDelEnd("audTmpHYInReg", "audHYInReg", Status)
End Sub

Private Sub Form_AfterUpdate()
Call AuditEditEnd("HYInReg", "audTmpHYInReg", "audHYInReg", "IDNo",
Nz(Me!IDNo, 0), bWasNewRecord)
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

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

End Sub

Private Sub Form_Delete(Cancel As Integer)
Call AuditDelBegin("HYInReg", "audTmpHYInReg", "IDNo", Nz(Me.IDNo, 0))
End Sub

Private Sub Form_Load()
DoCmd.Maximize
End Sub


Private Sub Command32_Click()
On Error GoTo Err_Command32_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "RegEditForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command32_Click:
Exit Sub

Err_Command32_Click:
MsgBox Err.Description
Resume Exit_Command32_Click

End Sub

Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "RegEntryForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click

End Sub











Graham R Seach said:
Sue,

I can't reproduce the behaviour you're experiencing. Can you post the
exact
code you have, and the steps you take to reproduce it (from the time you
open the form)?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Sue Wilkes said:
Thank you Graham the code does display the message however when I right
click
on the hyperlink control and follow the menu down to 'edit hyperlink'
it
keeps looping the error message and I cannot continue. I've tried
setting
focus with 'Me.[Hyperlink1].SetFocus but this also gives an error
message
run
time 2108 any help is appreciated for this green newbie. many thanks
Sue

:

Sue,

The easiest way is to check the field's length.
If Len("" & Me.ForwardedTo) = 0 Then
'The field is empty
End If

I always like to trim the value too:
If Len("" & Trim(Me.ForwardedTo)) = 0 Then
'The field is empty
End If

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a form (RegEditForm) linked to a control source table
(HYInReg)
In
the
table the field Hyperlink1 is set Yes for required. If a user makes
a
change
and accidently leaves any of the other fields blank I can get a
message
to
appear using the beforeupdate event procedure as follows.
Private Sub ForwardedTo_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[ForwardedTo]) And Not IsNull(Me.[RegisterNumber])
Then
MsgBox "FORWARDED TO DETAILS MUST BE COMPLETED BEFORE SAVING IS
PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub
However, if the user deletes the hyperlink that was there I am
unable
to
get
the same code to recognise that the field is now empty and display
an
error
message. I have tried using the same code in the controls other
events
but
no luck. Any help would be greatly appreciated I'm at a loss what to
try
next. Many thanks
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

I think you left out a > 0 there, Graham (or else put an extra one in <g>)

If Len("" & Trim(Me.[Reason(s)forEdit])) > 0 And Len("" &
Trim(Me.[RegisterNumber])) > 0 Then

Of course, what you suggested will work, since as non-zero value is treated
as True. In other words, the following would also work:

If Len("" & Trim(Me.[Reason(s)forEdit])) And Len("" &
Trim(Me.[RegisterNumber])) Then

However, I thought it might confuse some readers.

You could also put the Trim outside of the concatenation:

If Len(Trim(Me.[Reason(s)forEdit] & "")) > 0 And
Len(Trim(Me.[RegisterNumber] & "")) > 0 Then


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Graham R Seach said:
Sue,

Rather than checking the values after clicking Command28 (which I assume
is the Save button), I'd disable the button by default, and only enable it
if all the conditions are met.

Create a Sub to validate the data, and to set the button's Enabled
property only if the data is valid. Call this Sub during the form's
Current event, and in the AfterUpdate event for each of the relevent
controls.

Private Sub ValidateData()
If Len("" & Trim(Me.[Reason(s)forEdit])) And Len("" &
Trim(Me.[RegisterNumber])) > 0 Then
Me.Command28.Enabled = False
Goto Proc_Exit
Else
Me.Command28.Enabled = True
End If

'Add as many conditions as you like...
If some_other_condition_is_met Then
Me.Command28.Enabled = False
Goto Proc_Exit
Else
Me.Command28.Enabled = True
End If

Proc_Exit:
End Sub

In this way, you don't need to keep repeating the same thing over and
over, AND you never have to display a MsgBox, because the user can never
execute an invalid action. This is a fundamental principle in user
interface design, which states that all actions available to the user must
be valid (legal) ones.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Sue Wilkes said:
Hi Graham, I have managed to stop the looping but now can get the
message to
appear? I tried posting you code in many of the events on control
'Hyperlink1' and even tried adding to the command button 28 again no
luck.
If I click on the cmdPrint button it gives the error 3314 as the required
table field is set to Yes, other than that I cannot get any error message
to
appear regarding Hyperlink1, any thoughts. I have included below the
coding
used on the form in the hope that it helps. I have noticed that when I
use
tab to move throught the fields it always and only misses out the
Hyperlink1
field, is this what is affecting the error coding. Many thanks for
sticking
with this.
Regards, Sue

Option Compare Database

Dim bWasNewRecord As Boolean

Private Sub Combo18_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "RegisterNumber = " & Str(Nz(Me![Combo18], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo25_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[IDNo] = " & Str(Nz(Me![Combo25], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Private Sub cmdPrint_Click()

Dim StrWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"


Else
StrWhere = "[RegisterNumber] = """ & Me.[RegisterNumber] & """"
DoCmd.OpenReport "EditFormRpt", acViewPreview, , StrWhere
End If

End Sub



Private Sub Combo29_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[IDNo] = " & Str(Nz(Me![Combo29], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Command28_Click()

If IsNull(Me.[Reason(s)forEdit]) And Not IsNull(Me.[RegisterNumber])
Then
MsgBox "REASONS FOR EDITING MUST BE COMPLETED BEFORE SAVING IS
PERMITTED"
Cancel = True 'wont let the user continue
Me.[Reason(s)forEdit].SetFocus
End If

'I always like to trim the value too:
If Len("" & Trim(Me.ForwardedTo)) = 0 Then
'The field is empty????????
End If

End Sub

Private Sub CompanyName_s__BeforeUpdate(Cancel As Integer)
If IsNull(Me.[CompanyName(s)]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "COMPANY NAME DETAILS MUST BE COMPLETED BEFORE SAVING IS
PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub

Private Sub Subject_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[Subject]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "SUBJECT DETAILS MUST BE COMPLETED BEFORE SAVING IS PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub

Private Sub ForwardedTo_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[ForwardedTo]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "FORWARDED TO DETAILS MUST BE COMPLETED BEFORE SAVING IS
PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub

Private Sub ReceivedFrom_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[ReceivedFrom]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "RECEIVED FROM DETAILS MUST BE COMPLETED BEFORE SAVING IS
PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
Call AuditDelEnd("audTmpHYInReg", "audHYInReg", Status)
End Sub

Private Sub Form_AfterUpdate()
Call AuditEditEnd("HYInReg", "audTmpHYInReg", "audHYInReg", "IDNo",
Nz(Me!IDNo, 0), bWasNewRecord)
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

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

End Sub

Private Sub Form_Delete(Cancel As Integer)
Call AuditDelBegin("HYInReg", "audTmpHYInReg", "IDNo", Nz(Me.IDNo, 0))
End Sub

Private Sub Form_Load()
DoCmd.Maximize
End Sub


Private Sub Command32_Click()
On Error GoTo Err_Command32_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "RegEditForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command32_Click:
Exit Sub

Err_Command32_Click:
MsgBox Err.Description
Resume Exit_Command32_Click

End Sub

Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "RegEntryForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click

End Sub











Graham R Seach said:
Sue,

I can't reproduce the behaviour you're experiencing. Can you post the
exact
code you have, and the steps you take to reproduce it (from the time you
open the form)?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Thank you Graham the code does display the message however when I
right
click
on the hyperlink control and follow the menu down to 'edit hyperlink'
it
keeps looping the error message and I cannot continue. I've tried
setting
focus with 'Me.[Hyperlink1].SetFocus but this also gives an error
message
run
time 2108 any help is appreciated for this green newbie. many thanks
Sue

:

Sue,

The easiest way is to check the field's length.
If Len("" & Me.ForwardedTo) = 0 Then
'The field is empty
End If

I always like to trim the value too:
If Len("" & Trim(Me.ForwardedTo)) = 0 Then
'The field is empty
End If

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a form (RegEditForm) linked to a control source table
(HYInReg)
In
the
table the field Hyperlink1 is set Yes for required. If a user
makes a
change
and accidently leaves any of the other fields blank I can get a
message
to
appear using the beforeupdate event procedure as follows.
Private Sub ForwardedTo_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[ForwardedTo]) And Not IsNull(Me.[RegisterNumber])
Then
MsgBox "FORWARDED TO DETAILS MUST BE COMPLETED BEFORE SAVING IS
PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub
However, if the user deletes the hyperlink that was there I am
unable
to
get
the same code to recognise that the field is now empty and display
an
error
message. I have tried using the same code in the controls other
events
but
no luck. Any help would be greatly appreciated I'm at a loss what
to
try
next. Many thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thank you Guys for all your help it now works wonderful, my sanity is now
restored.

Douglas J. Steele said:
I think you left out a > 0 there, Graham (or else put an extra one in <g>)

If Len("" & Trim(Me.[Reason(s)forEdit])) > 0 And Len("" &
Trim(Me.[RegisterNumber])) > 0 Then

Of course, what you suggested will work, since as non-zero value is treated
as True. In other words, the following would also work:

If Len("" & Trim(Me.[Reason(s)forEdit])) And Len("" &
Trim(Me.[RegisterNumber])) Then

However, I thought it might confuse some readers.

You could also put the Trim outside of the concatenation:

If Len(Trim(Me.[Reason(s)forEdit] & "")) > 0 And
Len(Trim(Me.[RegisterNumber] & "")) > 0 Then


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Graham R Seach said:
Sue,

Rather than checking the values after clicking Command28 (which I assume
is the Save button), I'd disable the button by default, and only enable it
if all the conditions are met.

Create a Sub to validate the data, and to set the button's Enabled
property only if the data is valid. Call this Sub during the form's
Current event, and in the AfterUpdate event for each of the relevent
controls.

Private Sub ValidateData()
If Len("" & Trim(Me.[Reason(s)forEdit])) And Len("" &
Trim(Me.[RegisterNumber])) > 0 Then
Me.Command28.Enabled = False
Goto Proc_Exit
Else
Me.Command28.Enabled = True
End If

'Add as many conditions as you like...
If some_other_condition_is_met Then
Me.Command28.Enabled = False
Goto Proc_Exit
Else
Me.Command28.Enabled = True
End If

Proc_Exit:
End Sub

In this way, you don't need to keep repeating the same thing over and
over, AND you never have to display a MsgBox, because the user can never
execute an invalid action. This is a fundamental principle in user
interface design, which states that all actions available to the user must
be valid (legal) ones.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Sue Wilkes said:
Hi Graham, I have managed to stop the looping but now can get the
message to
appear? I tried posting you code in many of the events on control
'Hyperlink1' and even tried adding to the command button 28 again no
luck.
If I click on the cmdPrint button it gives the error 3314 as the required
table field is set to Yes, other than that I cannot get any error message
to
appear regarding Hyperlink1, any thoughts. I have included below the
coding
used on the form in the hope that it helps. I have noticed that when I
use
tab to move throught the fields it always and only misses out the
Hyperlink1
field, is this what is affecting the error coding. Many thanks for
sticking
with this.
Regards, Sue

Option Compare Database

Dim bWasNewRecord As Boolean

Private Sub Combo18_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "RegisterNumber = " & Str(Nz(Me![Combo18], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo25_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[IDNo] = " & Str(Nz(Me![Combo25], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Private Sub cmdPrint_Click()

Dim StrWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"


Else
StrWhere = "[RegisterNumber] = """ & Me.[RegisterNumber] & """"
DoCmd.OpenReport "EditFormRpt", acViewPreview, , StrWhere
End If

End Sub



Private Sub Combo29_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[IDNo] = " & Str(Nz(Me![Combo29], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Command28_Click()

If IsNull(Me.[Reason(s)forEdit]) And Not IsNull(Me.[RegisterNumber])
Then
MsgBox "REASONS FOR EDITING MUST BE COMPLETED BEFORE SAVING IS
PERMITTED"
Cancel = True 'wont let the user continue
Me.[Reason(s)forEdit].SetFocus
End If

'I always like to trim the value too:
If Len("" & Trim(Me.ForwardedTo)) = 0 Then
'The field is empty????????
End If

End Sub

Private Sub CompanyName_s__BeforeUpdate(Cancel As Integer)
If IsNull(Me.[CompanyName(s)]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "COMPANY NAME DETAILS MUST BE COMPLETED BEFORE SAVING IS
PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub

Private Sub Subject_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[Subject]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "SUBJECT DETAILS MUST BE COMPLETED BEFORE SAVING IS PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub

Private Sub ForwardedTo_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[ForwardedTo]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "FORWARDED TO DETAILS MUST BE COMPLETED BEFORE SAVING IS
PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub

Private Sub ReceivedFrom_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[ReceivedFrom]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "RECEIVED FROM DETAILS MUST BE COMPLETED BEFORE SAVING IS
PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
Call AuditDelEnd("audTmpHYInReg", "audHYInReg", Status)
End Sub

Private Sub Form_AfterUpdate()
Call AuditEditEnd("HYInReg", "audTmpHYInReg", "audHYInReg", "IDNo",
Nz(Me!IDNo, 0), bWasNewRecord)
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

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

End Sub

Private Sub Form_Delete(Cancel As Integer)
Call AuditDelBegin("HYInReg", "audTmpHYInReg", "IDNo", Nz(Me.IDNo, 0))
End Sub

Private Sub Form_Load()
DoCmd.Maximize
End Sub


Private Sub Command32_Click()
On Error GoTo Err_Command32_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "RegEditForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command32_Click:
Exit Sub

Err_Command32_Click:
MsgBox Err.Description
Resume Exit_Command32_Click

End Sub

Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "RegEntryForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click

End Sub











:

Sue,

I can't reproduce the behaviour you're experiencing. Can you post the
exact
code you have, and the steps you take to reproduce it (from the time you
open the form)?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Thank you Graham the code does display the message however when I
right
click
on the hyperlink control and follow the menu down to 'edit hyperlink'
it
keeps looping the error message and I cannot continue. I've tried
setting
focus with 'Me.[Hyperlink1].SetFocus but this also gives an error
message
run
time 2108 any help is appreciated for this green newbie. many thanks
Sue

:
Click to expand...
Click to expand...
Click to expand...
 
G

Graham R Seach

Thanks for spotting that Doug. Studying late into the night these days
(exams looming), so I'm not getting too much sleep (except on the job).

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Douglas J. Steele said:
I think you left out a > 0 there, Graham (or else put an extra one in <g>)

If Len("" & Trim(Me.[Reason(s)forEdit])) > 0 And Len("" &
Trim(Me.[RegisterNumber])) > 0 Then

Of course, what you suggested will work, since as non-zero value is
treated as True. In other words, the following would also work:

If Len("" & Trim(Me.[Reason(s)forEdit])) And Len("" &
Trim(Me.[RegisterNumber])) Then

However, I thought it might confuse some readers.

You could also put the Trim outside of the concatenation:

If Len(Trim(Me.[Reason(s)forEdit] & "")) > 0 And
Len(Trim(Me.[RegisterNumber] & "")) > 0 Then


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Graham R Seach said:
Sue,

Rather than checking the values after clicking Command28 (which I assume
is the Save button), I'd disable the button by default, and only enable
it if all the conditions are met.

Create a Sub to validate the data, and to set the button's Enabled
property only if the data is valid. Call this Sub during the form's
Current event, and in the AfterUpdate event for each of the relevent
controls.

Private Sub ValidateData()
If Len("" & Trim(Me.[Reason(s)forEdit])) And Len("" &
Trim(Me.[RegisterNumber])) > 0 Then
Me.Command28.Enabled = False
Goto Proc_Exit
Else
Me.Command28.Enabled = True
End If

'Add as many conditions as you like...
If some_other_condition_is_met Then
Me.Command28.Enabled = False
Goto Proc_Exit
Else
Me.Command28.Enabled = True
End If

Proc_Exit:
End Sub

In this way, you don't need to keep repeating the same thing over and
over, AND you never have to display a MsgBox, because the user can never
execute an invalid action. This is a fundamental principle in user
interface design, which states that all actions available to the user
must be valid (legal) ones.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Sue Wilkes said:
Hi Graham, I have managed to stop the looping but now can get the
message to
appear? I tried posting you code in many of the events on control
'Hyperlink1' and even tried adding to the command button 28 again no
luck.
If I click on the cmdPrint button it gives the error 3314 as the
required
table field is set to Yes, other than that I cannot get any error
message to
appear regarding Hyperlink1, any thoughts. I have included below the
coding
used on the form in the hope that it helps. I have noticed that when I
use
tab to move throught the fields it always and only misses out the
Hyperlink1
field, is this what is affecting the error coding. Many thanks for
sticking
with this.
Regards, Sue

Option Compare Database

Dim bWasNewRecord As Boolean

Private Sub Combo18_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "RegisterNumber = " & Str(Nz(Me![Combo18], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo25_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[IDNo] = " & Str(Nz(Me![Combo25], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Private Sub cmdPrint_Click()

Dim StrWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"


Else
StrWhere = "[RegisterNumber] = """ & Me.[RegisterNumber] & """"
DoCmd.OpenReport "EditFormRpt", acViewPreview, , StrWhere
End If

End Sub



Private Sub Combo29_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[IDNo] = " & Str(Nz(Me![Combo29], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Command28_Click()

If IsNull(Me.[Reason(s)forEdit]) And Not IsNull(Me.[RegisterNumber])
Then
MsgBox "REASONS FOR EDITING MUST BE COMPLETED BEFORE SAVING IS
PERMITTED"
Cancel = True 'wont let the user continue
Me.[Reason(s)forEdit].SetFocus
End If

'I always like to trim the value too:
If Len("" & Trim(Me.ForwardedTo)) = 0 Then
'The field is empty????????
End If

End Sub

Private Sub CompanyName_s__BeforeUpdate(Cancel As Integer)
If IsNull(Me.[CompanyName(s)]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "COMPANY NAME DETAILS MUST BE COMPLETED BEFORE SAVING IS
PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub

Private Sub Subject_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[Subject]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "SUBJECT DETAILS MUST BE COMPLETED BEFORE SAVING IS PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub

Private Sub ForwardedTo_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[ForwardedTo]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "FORWARDED TO DETAILS MUST BE COMPLETED BEFORE SAVING IS
PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub

Private Sub ReceivedFrom_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[ReceivedFrom]) And Not IsNull(Me.[RegisterNumber]) Then
MsgBox "RECEIVED FROM DETAILS MUST BE COMPLETED BEFORE SAVING IS
PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
Call AuditDelEnd("audTmpHYInReg", "audHYInReg", Status)
End Sub

Private Sub Form_AfterUpdate()
Call AuditEditEnd("HYInReg", "audTmpHYInReg", "audHYInReg", "IDNo",
Nz(Me!IDNo, 0), bWasNewRecord)
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

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

End Sub

Private Sub Form_Delete(Cancel As Integer)
Call AuditDelBegin("HYInReg", "audTmpHYInReg", "IDNo", Nz(Me.IDNo, 0))
End Sub

Private Sub Form_Load()
DoCmd.Maximize
End Sub


Private Sub Command32_Click()
On Error GoTo Err_Command32_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "RegEditForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command32_Click:
Exit Sub

Err_Command32_Click:
MsgBox Err.Description
Resume Exit_Command32_Click

End Sub

Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "RegEntryForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click

End Sub











:

Sue,

I can't reproduce the behaviour you're experiencing. Can you post the
exact
code you have, and the steps you take to reproduce it (from the time
you
open the form)?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Thank you Graham the code does display the message however when I
right
click
on the hyperlink control and follow the menu down to 'edit hyperlink'
it
keeps looping the error message and I cannot continue. I've tried
setting
focus with 'Me.[Hyperlink1].SetFocus but this also gives an error
message
run
time 2108 any help is appreciated for this green newbie. many thanks
Sue

:

Sue,

The easiest way is to check the field's length.
If Len("" & Me.ForwardedTo) = 0 Then
'The field is empty
End If

I always like to trim the value too:
If Len("" & Trim(Me.ForwardedTo)) = 0 Then
'The field is empty
End If

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a form (RegEditForm) linked to a control source table
(HYInReg)
In
the
table the field Hyperlink1 is set Yes for required. If a user
makes a
change
and accidently leaves any of the other fields blank I can get a
message
to
appear using the beforeupdate event procedure as follows.
Private Sub ForwardedTo_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[ForwardedTo]) And Not IsNull(Me.[RegisterNumber])
Then
MsgBox "FORWARDED TO DETAILS MUST BE COMPLETED BEFORE SAVING IS
PERMITTED"
Cancel = True 'wont let the user continue
End If
End Sub
However, if the user deletes the hyperlink that was there I am
unable
to
get
the same code to recognise that the field is now empty and display
an
error
message. I have tried using the same code in the controls other
events
but
no luck. Any help would be greatly appreciated I'm at a loss what
to
try
next. Many thanks
Click to expand...
Click to expand...
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

Run Time Error 3314 1
1 Form Two Tables 1 ID 1
Main Form, Sub-Form 3
Access How to check that the value of a field in a new record is not present in 2 fields of the table? 0
Fill fields automatically on Form 4
Back to Basics 1
Data Validation from Delete Command Button 1
Access Passing data from a form to another form! 0

Top