All fields to be filled before adding new record 1

[CBAWFP]

Hi there
I'm pretty new to VBA, and I'm trying to wade my way through some complicated stuff (to me it is) to try and get a form to do what I want.

I have created a Quality Database to record evaluations of phone calls (for a call centre). To enter each evaluation, I have a form with 19 essential fields (and 4 subforms that calculate the score on the fly). For reasons of compliance I need to make sure that all fields are filled in with either a PASS or True/False response before either the form is closed or a new record is added.

I've searched through these forums and tried some of the suggestions
ie:

If IsNull(Me.FIELD01) or IsNull(Me.FIELD02) and IsNull(Me.FIELD03) then
Me.NEW_RECORD.Enabled = False
Else
Me.NEW_RECORD.Enabled = True
End If

I have this on the "On_Click" option of the "add new record" button that I have on the form.

The issue I have is that 1. Yes, it disables the button, but filling in the fields so that they are all filled in does not re-acitvate the button (ie it stays disabled).

I tried variations using msg boxes etc, and removing the focus from the button (seems you can't disable the button if clicking it puts it in focus).... But I've had no luck

can anyone help me?

thanks

 

[Andrzejek]

Try this:

Create a simple Sub and call it from Change event of your fields (assuming thery are test boxes, for example):

Private Sub FIELD01_Change()
    Call OKToInsert
End Sub

Private Sub OKToInsert()

If IsNull(Me.FIELD01) or _ 
    IsNull(Me.FIELD02) or _
    IsNull(Me.FIELD03) then
       Me.NEW_RECORD.Enabled = False
Else
    Me.NEW_RECORD.Enabled = True
End If

End Sub

Have fun.

---- Andy

 

[CBAWFP]

Hi Andy

I tried it, but it didn't work (had me excited there for a while).

I have the TAB order so that the user goes through each of the fields in a logical order.

I thought perhaps the problem was due to tabbing into a field and then tabbing out without putting anything in there, so I tried just selecting the fields and not touching one of them. Still nothing.

When I say nothing, I mean that the NEW_RECORD field still created a new record. It didn't disable the field.

I think the problem is that it doesn't recognise that the field is empty (how would that be possible)?

One thing I forgot to mention was that each of the key fields has a
DoCmd.RunCommand acCmdSaveRecord
command in the exit field

This is so that the 4 subforms I have on there can calculate the new score when someone enters something in the field.

 

[CBAWFP]

Sorry for the confusion.
I just realised that if I leave a field blank it will disable the button (fantastic).
However, if I go back and enter a value in the blank field the NEW_RECORD button is still disabled (ie it doesn't re-enable once I populate the fields).

Joe

 

[CBAWFP]

Hello again.

I managed to get it to work by putting the call OKToInsert in the On Exit part of each field.

Thank you for your help..I'm learning something new every day.

Joe

 

[Andrzejek]

My other idea was to pre-fill all fields (I hate the term 'fields', are they text boxes?) with default values (either FALSE or FAIL) and make user change them to the correct values.

Or instead of text boxes, have drop-down combos with FALSE and TRUE to select from, and have FALSE 'pre-chosen'. Or maybe option buttons:[tt]
O FALSE
O TRUE[/tt]
and FALSE is 'pre-selected'.

Have fun.

---- Andy

 

[amrizvi]

Try this.....

If IsNull(Me.FIELD01) = True then
Me.NEW_RECORD.Enabled = False
End if

If IsNull(Me.FIELD02) = True then
Me.NEW_RECORD.Enabled = False
End if

If IsNull(Me.FIELD03) = True then
Me.NEW_RECORD.Enabled = False
End if

Me.NEW_RECORD.Enabled = True

 

[amrizvi]

avoid earlier as I missed 'Exit Sub' in that

If IsNull(Me.FIELD01) = True then
Me.NEW_RECORD.Enabled = False
Exit Sub
End if

If IsNull(Me.FIELD02) = True then
Me.NEW_RECORD.Enabled = False
Exit Sub
End if

If IsNull(Me.FIELD03) = True then
Me.NEW_RECORD.Enabled = False
Exit Sub
End if

Me.NEW_RECORD.Enabled = True

 

[CBAWFP]

Hi guys. Thank you all for your input. I've managed to solve my problem using Andy's solution using the Exit field rather than change field parameter.
It works a treat.
thanks again.
Joseph

 

[Gammachaser]

You can put all of that code into one statement.

Me.NEW_RECORD_ENABLED = NOT Isnull(Me.FIELD01) AND NOT IsNull(Me.FIELD02) and NOT IsNull(Me.FIELD03)

Unlike some code it even makes sense if you read it out loud. Or maybe I am just getting too used to VBA code.

 

[Andrzejek]

Gammachaser, you may also like this approach:

Me.NEW_RECORD.Enabled = Len(Trim((Me.FIELD01 & "")) 
                      * Len(Trim((Me.FIELD02 & "")) 
                      * Len(Trim((Me.FIELD03 & ""))

so if any field is empty (NULL or Spaces) - entire calculation is 0 (FALSE)

Have fun.

---- Andy

 

[CBAWFP]

Hi guys...thanks for that. It looks really clean.

Just a question however. Where do I put this code? Do I create a new function, private sub or do I place it in one of the field actions (ie On Exit)..

 

[Gammachaser]

You need a reference to it in the 'AfterUpdate' event procedure for each control that is part of the required set (Field01, Field02, etc.) but I would make it a Fucntion that you can call rather than repeating the code three times. If it ever changes (i.e. you have new required information) you only have to change it once. Something like:

Function RecordReady() as Boolean
RecordReady = NOT Isnull(Me.FIELD01) AND NOT IsNull(Me.FIELD02) and NOT IsNull(Me.FIELD03)
End Function

Then in each AfterUpdate Event:

New_Record.Enabled = RecordReady

Andy's code used in the Function would work and would account for blank spaces, not just null values. But -- it would also accept any text entry as valid. Reading your original post, if you must have a "Pass", "True" or "False" response you need to modify the code to test for those three options:

Function RecordReady() as Boolean
Dim Counter as Integer

For Counter = 1 to 3
   Select Case Me.Controls("Field0" & Format(Counter))
      Case "True", "False", "Pass"
      Case Else
         Exit Function
   End Select
Next
RecordReady = True
End Function

Is one way to do it. There are a lot of other methods.

 

[Andrzejek]

A little modification to Gammachaser's code if you ask your user to type the responce::

Function RecordReady() as Boolean
Dim Counter as Integer
For Counter = 1 to 3   
  Select Case [blue]Trim(UCase([/blue]Me.Controls("Field0" & Format(Counter))[blue]))[/blue]
      Case [blue]"TRUE", "FALSE", "PASS"[/blue]
      Case Else         
        Exit Function   
  End Select
Next
RecordReady = True
End Function

If that's the case - typing 'True', 'False', 'Pass' - I would re-think my approach and either give the user drop-down combo or 3 sets of option buttons to select from. No typing, no evaluation what they typed.

Have fun.

---- Andy