Form doesn't recognize that a field is not Null

[lmmoorewi]

I have a form that allows users to enter their tasks and hours.
The fields are:

Task (list box for users to select a task)
Hours for each day of the week (allows the users to enter the amount of hours with associated task – HoursSun, HoursMon, HoursTue, etc.,)
The form is a Multiple items form where the user can start a new record by selecting another task field (below the current record on the form)

I have created a trigger and Msgbox that will not allow the users to save a task without entering hours and also will not allow a user to save hours without entering a task. I want to avoid incomplete records.

I am having a user when the user tries to save a record with only the hours entered and not an associated task. The Msgbox appears to inform the user that a task and hours are required to save the record.

After the Msgbox, when the user selects a task from the list box, the form doesn’t recognize that a task is in the field. The user continues to get the Msgbox that a task is missing. The user has to select the next record on the form in order for the form to recognize that the task field is not null.

How can I get the form to recognize that data is actually in that field so the user will not receive the Msgbox?

 

[kjv1611]

Questions:
1. Version of Access?
2. Is the listbox a bound or unbound control
3. When does the form check to see whether both items are completed?
4. What is your trigger? Is it VBA, a Macro, what? If it's VBA, paste the code here between the [ignore]

[/ignore] tags. You can click the Process TGML link below the message box here to see how to enter that, if you need help there.

 

[lmmoorewi]

Hi Kjv1611!

Here are my answers:
1. Access 2007
2. It's actually a bound combobox
3. It's checks to see if both items are completed when the users clicks on a Save botton.
4. The Trigger is VB Code:
Private Sub cmdSaveTechWorksheet_Click()

If IsNull(Forms!frmworksheets.subfrmHours.Form.Task) And ((Forms!frmworksheets.subfrmHours.Form.Hours) > 0) Then
MsgBox "You can not save Hours without a Task. Please select a Task."

Else
If Not IsNull(Forms!frmworksheets.subfrmHours.Form.Task) And ((Forms!frmworksheets.subfrmHours.Form.Hours) >= 0) Then
MsgBox "You can not save Tasks without Hours. Please enter Hours."

Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

End If
End If
End Sub

 

[Locoman]

I don't think you can use IsNull to compare to a variable in the way you have, i.e. YourExpression >0.

Null only checks for True or False:

Quote from Access Help:

"Important Use the IsNull function to determine whether an expression contains a Null value. Expressions that you might expect to evaluate to True under some circumstances, such as If Var = Null and If Var <> Null, are always False. This is because any expression containing a Null is itself Null and, therefore, False."

Both your conditions will trigger the message since you don't have a "do nothing" condition or test.

I think it should be more like

If IsNull(YourExpression1) = True Then
MsgBox ....1

ElseIf IsNull(YourExpression2) = True Then
MsgBox .....2

Endif

 

[kjv1611]

Give this a shot for your code - I've not tested it, but tried moving some things around. Also, I think your piece about "You can not save Tasks without Hours..." was incorrect, b/c you had >=, probably forgot to delete the >...

Private Sub cmdSaveTechWorksheet_Click()
	If IsNull(Forms!frmworksheets.subfrmHours.Form.Task) Then
		If Forms!frmworksheets.subfrmHours.Form.Hours > 0 Then
			MsgBox "You can not save Hours without a Task. Please select a Task."
		End If
	Else
		If Forms!frmworksheets.subfrmHours.Form.Hours) = 0 Then
			MsgBox "You can not save Tasks without Hours. Please enter Hours."
		Else
			DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
		End If
	End If
End Sub

 

[lmmoorewi]

Hi -

Thanks for the modified code. I tried kjv1611's solution first since I was reading the thread from the bottom up.

I changed my code to what you suggested, but here is the weird thing that is still happening.

Once the user receives the Msgbox and tries to correct the problem (enter Task or Hours whatever is missing), the user has to select the next record on the multiple items form for the form to recognize a change was made. If the user remains in the changed field (Task or Hours), the Msgbox still appears even if the missing data is now present.

 

[kjv1611]

At first glance, it sounds to me like when they are editing the data, they are not then "saving" the data by moving away from that control before clicking the Save button... just a guess anyway.

So, maybe you could try activating another control in the form first, and then running the saving code?

 

[lmmoorewi]

Thanks kjv1611 and Locoman for responding!

Last night, I just made the fields 'required' and customized the system message to inform the users to populate the required fields. This method is working.

Thanks again!