Code Issue for Form Validation

[gls]

Hi

I'm using the code below to check the form for validation once the user moves to another record or the current record has lost focus. This is doing want i want, except for the staff_Name field. When the message box pops up to indicate that you need to enter a field in Staff_Name and you hit ok, it comes up with error. This is the only error in the validation process.

I have highlighted the row of code that highlights yellow with the sad red face. I'm guessing that this line of code gives the focus back to the field that has no data entered. The Staff_Name is a combo box, so i tried to change it to a txt box, this made no difference, so i could iliminate the possiblity of the field being the combo box causing the error.

Can anyone help?

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty = True Then
Dim Strg As String
Strg = AreAllFieldsFilled
If Strg <> "" Then
MsgBox "Before this Record can be Saved, Data for Field" & vbCr & _
"'" & Replace(Strg, "_", " ") & "' must be supplied.", vbInformation, _
"Data Required..."
Cancel = True
Me.Control(Strg).SetFocus [COLOR=red][B](this is where the error is occuring)[/B][/COLOR]
Exit Sub
End If
End If
End Sub
 
 
 
 
 
 
Private Sub Close_Form_Click()
On Error GoTo Err_Close_Form_Click
If Me.Dirty = True Then
If MsgBox("You have supplied or modified Data within this Record." & vbCr & _
"Do you want to Save this Record?", vbExclamation + vbYesNo, _
"Save Record?") <> vbYes Then
Me.Undo
Else
Dim Strg As String
Strg = AreAllFieldsFilled
If Strg <> "" Then
MsgBox "Before this Record can be Saved, Data for Field" & vbCr & _
"'" & Replace(Strg, "_", " ") & "' must be supplied.", vbInformation, _
"Data Required..."
Me.Controls(Strg).SetFocus
Exit Sub
End If
DoCmd.RunCommand acCmdSaveRecord
End If
Else
Me.Undo
End If
 
DoCmd.Close acForm, "frmNon_Compliances"
Exit_Close_Form_Click:
Exit Sub
Err_Close_Form_Click:
MsgBox Err.Description
Resume Exit_Close_Form_Click
 
End Sub
 
 
 
 
 
Private Function AreAllFieldsFilled() As String
AreAllFieldsFilled = ""
If IsNull(Me.Work_Order_Number) = True Then AreAllFieldsFilled = "Work_Order_Number"
If IsNull(Me.Staff_Name) = True Then AreAllFieldsFilled = "CIN Raising Officer"
'If IsNull(Me.Staff_Contact) = True Then AreAllFieldsFilled = "Staff_Contact"
If IsNull(Me.Work_Order_Type) = True Then AreAllFieldsFilled = "Work_Order_Type"
If IsNull(Me.Cin_Type) = True Then AreAllFieldsFilled = "CIN_Type"
If IsNull(Me.Inspection_Date) = True Then AreAllFieldsFilled = "Inspection_Date"
If IsNull(Me.CIN_Date) = True Then AreAllFieldsFilled = "CIN_Date"
If IsNull(Me.Response_Due_Date) = True Then AreAllFieldsFilled = "Response_Due_Date"
If IsNull(Me.Issue) = True Then AreAllFieldsFilled = "Issue"
If IsNull(Me.CIN_Status) = True Then AreAllFieldsFilled = "CIN_Status"

 

[Galaxiom]

It is best to post your code inside code tags.
Among other advantages, this allows the code to be indented, making it much easier to read.

In Advanced view, highlight your code setion and click the # button on the toolbar.
You can edit your original post.

 

[gls]

It is best to post your code inside code tags.
Among other advantages, this allows the code to be indented, making it much easier to read.

In Advanced view, highlight your code setion and click the # button on the toolbar.
You can edit your original post.

Thankyou, i fixed that.

 

[Galaxiom]

You have ommitted the s in Controls
Me.Controls(Strg)

 

[gls]

You have ommitted the s in Controls
Me.Controls(Strg)

Thank you, but even with the S on the end of control, it still stops at the same row of code.

 

[Galaxiom]

You appear to have a control on the form and a function both called AreAllFieldsFilled. Try changing the name of one of them.

 

[khawar]

What is the description of error

 

[gls]

What is the description of error

The error is

Run time error '438'
Object doesnt support this property or method

This code works fine on all other fields, both storing numbers and text, but only falls over on this field.

 

[gls]

You appear to have a control on the form and a function both called AreAllFieldsFilled. Try changing the name of one of them.

Thank you, there are no controls on the form called AreallFieldsFilled. I only have the function in the code.

 

[Galaxiom]

Thank you, there are no controls on the form called AreallFieldsFilled. I only have the function in the code.

Sorry, didn't read your code closely enough.

I had a similar problem a while ago where I could not set focus on a control. I don't think the message was quite the same though. More like "can't set focus at present".

Unfortunately I never got to the bottom of it but I think it went away after I copied everything onto a new form.