Validation on form

[Egg 'n' Bacon]

Here's something I'm hoping someone will be able to help me with;

I have a form based on a table populated from one of our servers. This leaves some fields blank, which is fine. What I need is to have 2 of the blank fields mandatory, but ONLY if certain other fields have any values entered.

i.e. if a user puts a value in the [NCMR] field, then the [Name] & [Date] fields cannot be left blank. Likewise on certain other fields.

Any ideas?

 

[BarryMK]

Thanks to whoever coded this, I'm sure it came from this forum.


Enter "Required" minus parentheses in the Tag property of the field/s you want to enforce then add this code to the BeforeUpdate Event of the form

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Enforce required fields on a form

Dim ctl As Control
For Each ctl In Me
If ctl.Tag = "Required" Then
If IsNull(ctl) Or ctl = "" Then
MsgBox "You must complete all required fields to continue", vbCritical, "Required Field"
ctl.SetFocus
Exit Sub
End If
End If
Next
Set ctl = Nothing

End Sub

 

[Egg 'n' Bacon]

Ah nearly, unfortunately this doesn't quite do the trick.

It's a little difficult to explain but here goes; any of the other fields can remain blank, but if a user enters a value in any one of these, then the [name] & [date] fields must also have values.

 

[BarryMK]

Ah nearly, unfortunately this doesn't quite do the trick.

It's a little difficult to explain but here goes; any of the other fields can remain blank, but if a user enters a value in any one of these, then the [name] & [date] fields must also have values.

Have you considered a Select Statement?

 

[Egg 'n' Bacon]

Hmm, never used Select in this manner, could you give me an example please?

 

[BarryMK]

Not now I'm off home! I'll try to suggest something tomorrow if no-one else does. Cheers

 

[Rabbie]

Try

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Enforce required fields on a form
Dim booRequired as boolean
Dim ctl As Control
booreQuired = false
For Each ctl In Me
If (IsNull(ctl) Or ctl = "") and (ctl.name <> DateName or ctl.name <> NameName) Then
else
boorequired=true
end if
Next
if booRequired = True
if me![DateName] = "" or isNull(me![DateName]) then
MsgBox "You must complete the Datefield to continue", vbCritical, "Required Field"
ctl.SetFocus
Exit Sub
End If

if me![NameName] = "" or isNull(me![NameName]) then
MsgBox "You must complete the Namefield to continue", vbCritical, "Required Field"
ctl.SetFocus
Exit Sub
End If
end if
set ctl = Nothing

End Sub

 

[Egg 'n' Bacon]

Well that looks about right (not that I can really tell ), but the line;
if booRequired = True

is showing up in red & I'm getting a syntax error.

 

[CEH]

Try this...... if booRequired = "True"

The other code above would probably work by changing
If IsNull(ctl) Or ctl = "" Then
to
If Not IsNull(ctl) Or ctl = "" Then

 

[Rabbie]

Try this...... if booRequired = "True"

The other code above would probably work by changing
If IsNull(ctl) Or ctl = "" Then
to
If Not IsNull(ctl) Or ctl = "" Then

The logic was to find the cases where Ctl was either Null or an empty string.

The line
if boorequired = true

should have a Then at the end


ie

if booRequired = true then


Sorry abou thetypos

Rob

 

[CEH]

My bad....... see that now.....

 

[Rabbie]

No probs. The syntax error was the missing Then. We wil just have to see if the logic is correct.

 

[Egg 'n' Bacon]

Morning, been away, so I've only just got back to this.

Well the previous issue (the missing 'Then') seems OK, but now geting a runtime error, 438; ;"Object doesn't support this property or method". The line of code that gets highlighted on debug is;
If (IsNull(ctl) Or ctl = "") And (ctl.Name <> DateName Or ctl.Name <> NameName) Then

 

[Egg 'n' Bacon]

Any ideas anyone?

 

[Rabbie]

Problem is with ctl.name. it seems it isn't a valid property. i don't know if it will work with ctl!name. Could be worth a try.

 

[Egg 'n' Bacon]

I get the feeling there's something else I might be missing here.

The name part of ctl.name, is this something I should change to reflect a control or field or something?

 

[Rabbie]

I get the feeling there's something else I might be missing here.

The name part of ctl.name, is this something I should change to reflect a control or field or something?

When I drafted the code I assumed that since there is a Name field associated with every control I assummed(stupid I know) that I could test for the controls that were't the Datefield or the Namefield so I could see if those fields would be required or not. Since ctl doesnt have a name property I am not sure how to progress from here. Apologies for getting it wrong

 

[Egg 'n' Bacon]

Ah the joys of trying to explain things by text, without the benefit of a sample

I'll try to put this down politely (off to a bad start!!!), but there is only the one [Name] field in the table, likewise only one [Date] field. The challenge is to have some kind of validation when a user changes any of the other fields. Some of which have 0 (zero) as a default value, the others are null.

I hope this makes this do-able(?)