I need some help please!

S

scubadiver

I hope I explain this better than I did before.

I have a subform in which all the records may not necessarily be used all
the time but, I emphasise, it depends on the information that is being
entered. So the "required" property for every field is set to "yes" and the
default values are "N/A" (not applicable) then fields can be changed.

In a combo box I have a list of word documents and the next text box is used
for entering document points. There is a BUT to this! I need to ensure that
if a word document is required then the text field is updated.

thanks
 
D

Douglas J. Steele

You've never explained why you're making the default value N/A. If you don't
want (or need) a value, you shouldn't have a default value assigned.

If what you're trying to do is help the users, take a look at the "cueing
banners" stuff in my February, 2004 "Access Answers" column in Pinnacle
Publication's "Smart Access" (and/or Chris Weber's alternate approach in my
October, 2004 column). You can download the columns (and sample databases)
for free at http://www.accessmvp.com/djsteele/SmartAccess.html

To validate whether the text field needs to have a value, check in the
form's BeforeUpdate event to see whether a document has been chosen or not.
 
S

scubadiver

I have a subform in which all the FIELDS may not necessarily be used all the
time
 
S

scubadiver

If I explain it the other way it may clarify. If all the fields are left
blank but the
"required" property for each field is set to "yes" what do I put in the text
box if no information is required.

If I set the "required" property to "no" how can I be guaranteed that the
information will be entered correctly?
 
K

Keith Wilby

scubadiver said:
I hope I explain this better than I did before.

I have a subform in which all the records may not necessarily be used all
the time but, I emphasise, it depends on the information that is being
entered. So the "required" property for every field is set to "yes" and
the
default values are "N/A" (not applicable) then fields can be changed.

In a combo box I have a list of word documents and the next text box is
used
for entering document points. There is a BUT to this! I need to ensure
that
if a word document is required then the text field is updated.

thanks
Click to expand...

If I'm understanding you correctly then you want a method of verifying that
all fields have been considered. If that's the case then remove the default
values and loop through the controls in the form's Before Update event. If
any are found empty or null then cancel the event and put up an appropriate
user-message along the lines of "Enter some data or 'N/A' in the whatever
field".

HTH - Keith.
www.keithwilby.com
 
D

Douglas J. Steele

If the field in the table doesn't actually require a value, its Required
property shouldn't be set to Yes.

As I already said, to check whether all of the required information has been
provided, put logic in the BeforeUpdate event of your form.
 
S

scubadiver

Looping is something I haven't considered doing but I don't know how to do
(or where to put it)

thanks.
 
J

John W. Vinson

If I set the "required" property to "no" how can I be guaranteed that the
information will be entered correctly?
Click to expand...

By using the Form's BeforeUpdate event to check.

Since the field is only required under certain conditions, I'd make it *not*
required in the Table.

Instead, check in the Form's BeforeUpdate event to see if it is required, and
then if it's required, whether it's there:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me!ThisControl = "Word Document" Then ' or whatever condition makes
' the field obligatory
If IsNull(Me!ThatControl) Then
MsgBox "You must fill in ThatControl", vbOKOnly
Cancel = True ' cancel the record addition
Me!ThatControl.SetFocus
End If
End If
End Sub

John W. Vinson [MVP]
 
S

scubadiver

thanks.

I knew I could use the "before update" event but I wasn't sure how. Although
your code looked correct I had an "End if without block if" error so I
deleted one of the "end if" statements and now it seems to be working.

But I do need a bit of extra help. I also have three other combo boxes that
need to have the same code. The 2nd and 3rd have to be updated only if the
1st is updated.

The following works okay.

If Me!Act_Dept <> Null Then DoCmd.GoToControl Me.Act_Subd
If IsNull(Me!Act_Subd) Then
MsgBox "You must fill in the subdepartment and user name", vbOKOnly
Cancel = True ' cancel the record addition
Me!Act_Subd.SetFocus
End If


But I also have the following:

If Me!Act_Subd <> Null Then DoCmd.GoToControl Me.Act_User
If IsNull(Me!Act_User) Then
MsgBox "You must fill in the user name", vbOKOnly
Cancel = True ' cancel the record addition
Me!Act_User.SetFocus
End If

If "act_dept" and "act_subd" are updated then the second message is
displayed. If only "act_dept" is updated then both messages are displayed and
the cursor goes to "act_user".

What I need is for *only* the first message to be displayed when the 2nd and
3rd combos are null.

thanks
 
S

scubadiver

Mr Vinson has helped me out so with some code but I am still a bit stuck.
Can you help?
 
S

scubadiver

I've decided to leave it as it is.



scubadiver said:
thanks.

I knew I could use the "before update" event but I wasn't sure how. Although
your code looked correct I had an "End if without block if" error so I
deleted one of the "end if" statements and now it seems to be working.

But I do need a bit of extra help. I also have three other combo boxes that
need to have the same code. The 2nd and 3rd have to be updated only if the
1st is updated.

The following works okay.

If Me!Act_Dept <> Null Then DoCmd.GoToControl Me.Act_Subd
If IsNull(Me!Act_Subd) Then
MsgBox "You must fill in the subdepartment and user name", vbOKOnly
Cancel = True ' cancel the record addition
Me!Act_Subd.SetFocus
End If


But I also have the following:

If Me!Act_Subd <> Null Then DoCmd.GoToControl Me.Act_User
If IsNull(Me!Act_User) Then
MsgBox "You must fill in the user name", vbOKOnly
Cancel = True ' cancel the record addition
Me!Act_User.SetFocus
End If

If "act_dept" and "act_subd" are updated then the second message is
displayed. If only "act_dept" is updated then both messages are displayed and
the cursor goes to "act_user".

What I need is for *only* the first message to be displayed when the 2nd and
3rd combos are null.

thanks
Click to expand...
 
D

Douglas J. Steele

Your code sample could not possibly work okay, since it's syntactically
incorrect. Due to its special nature, you cannot use equals or any
inequality operators with Null: you must use the IsNull function. Rather
than

If Me!Act_Dept <> Null Then

you must use

If IsNull(Me!Act_Dept) = False Then

The way I typically do error checking is to build up the string:

Dim strMessage As String

If IsNull(Me!Combo1) = True Then
strMessage = strMessage & "You must choose a value from combo one" &
vbCrLf
End If

If IsNull(Me!Combo2) = True Then
strMessage = strMessage & "You must choose a value from combo two" &
vbCrLf
End If

If IsNull(Me!Combo3) = True Then
strMessage = strMessage & "You must choose a value from combo three" &
vbCrLf
End If

If Len(strMessage) > 0 Then
MsgBox strMessage
End If



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
J

John W. Vinson

thanks.

I knew I could use the "before update" event but I wasn't sure how. Although
your code looked correct I had an "End if without block if" error so I
deleted one of the "end if" statements and now it seems to be working.

But I do need a bit of extra help. I also have three other combo boxes that
need to have the same code. The 2nd and 3rd have to be updated only if the
1st is updated.
Click to expand...

I guess I don't understand your logic here. The above sentence is ambiguous,
since I don't know which combo box is the "2nd" or the "3rd".

Please lay out the dependencies for me.

If ActDept is Null then ActSubd must be Null
If ActDept is not Null then ActSubd must be filled in

and so on.

John W. Vinson [MVP]
 
Top