Loop thru fields in Current record

[PaddyIrishMan]

Hi all,
What I'd like to do is:
In the after update event of a certain field on my form, I'd like to validate other fields on the form before allowing the user continue.

I don't want to go down the if [field].text = "" etc road as ther are a lot of fields on the form.

What I'd like to do is temporarily set a RecordSource variable to the current record, & loop thru the fields in the recordset identifying the empty fields.

The problem I'm having is identifying the current record without having to loop thru all records - Does anyone know if this is possible?

I.e. MyRecSet = me.CurrentRecord (Obviously this doesnt work)

Thanks for any help,
Patrick.

 

[ColinEssex]

If you don't want the user to leave empty fields, why not make them "required" then they'll have to put something in.

Col

 

[PaddyIrishMan]

Hi Colin,
thanks for the reply, the thing is the fields aren't Necessarily "Required" it's moreso to aknowledge the fields that are left blank & perform an action if necessary (such as inserting a value programatically). Also I really don't like those nasty Access messages you get when you don't fill in required fields - this is for an in-house project so I don't want to annoy users too much - I have to think of my own safety!!

 

[ColinEssex]

Hi

You'll need to use the "control" method I think. I'm not too sure of the exact code, I'm sure someone who is more familiar with it will help but its a bit like this.

Dim ctl as Control

For each ctl in frm.Controls

With ctl

Then there's an If statement I think.......

Sorry to be a bit vague but I'll see if I can find where I used it.

Col

 

[PaddyIrishMan]

Thanks Colin, I'll check this out. I was trying it earlier today though & was getting strange results whereby the loop was incremented way beyond the amount of controls that there should have been in the collection??

If this is the way to go though so be it!!

Thanks for the advise,
Patrick.

 

[PaddyIrishMan]

FYI

This is how it's done if anybody needs it.
There's a list of predefined constants for the control types in the VBA Help.

For Each Control In Me.Controls
'Validate the data in the controls - ensure that all are filled in.
If Control.ControlType = acTextBox Or Control.ControlType = acComboBox Then

Select Case Control.Name
Case Is = "cmbfind", "cmbdevstatus", "cmbdevreassignedto", "fixedin"
'Do Nothing
Case Else
If IsNull(Control.Value) = True Or Control.Value = "" Then
MsgBox (Control.Name & " must be completed.."), vbInformation
End If
End Select

End If

Next