>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
>
http://I.Am/DougSteele> (no private e-mails, please)
>
>
> "Graham R Seach" <gseach[ at ]accessmvp_REMOVE.com> wrote in message
> news:uvtD75ZrGHA.4892[ at ]TK2MSFTNGP05.phx.gbl...
>> 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" <SueWilkes[ at ]discussions.microsoft.com> wrote in message
>> news:CA49EFCE-5D8C-401C-9FA3-29FBA61E912F[ at ]microsoft.com...
>>> 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" wrote:
>>>
>>>> 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" <SueWilkes[ at ]discussions.microsoft.com> wrote in message
>>>> news:0426BB00-168E-443B-8292-7719B56760F4[ at ]microsoft.com...
>>>> > 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" wrote:
>>>> >
>>>> >> 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" <SueWilkes[ at ]discussions.microsoft.com> wrote in message
>>>> >> news:0B956EBD-9918-4B9A-AD0B-3666BCF6B795[ at ]microsoft.com...
>>>> >> >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
>>>> >>
>>>> >>
>>>> >>
>>>>
>>>>
>>>>
>>
>>
>
>