Combo box closes when another control is clicked.

[Jon.N]

I have a database that is used to log working hours. There are 4 fields: Project, Task and the start and end time fields. The task and project fields are combo boxes.

I have made it so when someone selects a project only the relevent tasks for that project are available in the task combo box. so far so good.

Also, if someone fills in a complete entry and then changes their mind and selects a different project the tasks combo box opens for the user to re-select a task appropriate to the new project. The only problem is the combo box closes if the user selects another control or clicks the form background. I want to stop the combo box from closing when someone clicks away from it, thus forcing data entry. I do not want to make this a required field in the table properties. Any ideas as always will be greatly appreciated.

Jon.N

 

[Ally]

A combo box will always close when you click away from it. If you have it as a list box, you will have everything displayed, but I don't think that's what you're really getting at.

If it's just a case of forcing data entry into that field, then why not put some code on it - either on the OnClick event of the next field or on your close button / OnClose event: get it to check that the value is not null of the task combo, get a message box to say that a value must be entered and move the focus back to the task field. Along the lines of:

If IsNull([Task]) Then
     msgbox "Task field empty.  Please enter value." .. ...
     Me.TaskField.SetFocus

Else ... ... ... Close etc
End If

HTH

 

[Jon.N]

How about checking a record set

Many, many thanks for all your help. All useful answers that will be useful to me. My original question was about combo boxes. My full dilema is this.

I think for my database to fully comply with specs I need to make it check all entries when the days inputting is complete. The completion of a day is signified by ticking a check box on the form called completed. I tried to make the following code run when the check box is ticked. The query called DailyCheck looks for records that have empty or null fields, hence an empty recordset or no hits is a good result.

Once again any help in getting this code to work is greatly appreciated, here is the code:

Dim bytMsg As Byte

If Me.Completed = -1 Then

rst.Open "DailyCheck", CurrentProject.Connection

If Not rst.BOF And Not rst.EOF Then
bytMsg = MsgBox("all mandatory fields have been completed." & vbclrf & _
"Would you like to exit Timesheets?", vbInformation + vbYesNo, _
"CHECK COMPLETED")

If bytMsg = vbYes Then
Application.Quit
ElseIf bytMsg = vbNo Then
With DoCmd
.GoToControl "ProjecttaskSubform"
.GoToControl "Project"
End With

Else
Beep
Beep
bytMsg = MsgBox("Some mandatory fields have not been completed." & vbCrLf & _
"Please recheck to-day's entries.", vbCritical + vbOKOnly, "MISSING DATA!")

End If

End If

End If

 

[Rich]

As Pat has already told you, you have to use the Before Update event of the form to trap null entries, the record has been saved by opening the query.
There is nothing in your code to simply prevent users from closing the form anyway
I personally prefer to use a function and the Tag property of controls

 

[Jon.N]

Acknowledged, but this code is not trying to trap null entries. It is just looking for null entries and depending on whether or not there are null entries one of 2 message boxes appears.

The code works if I delete the line:
rst.Open "DailyCheck", CurrentProject.Connection
and replace the line:
If Not rst.BOF And Not rst.EOF Then
with:
If (DCount("[TaskID]", "DailyCheck") >= 1) Then

Unfortunately the DCount method is far too slow so I'm trying to create something that looks at a record set to determine whether or not DailyCheck has any finds.

Once again many thanks.

 

[Rich]

Now I'm confused, you have a message box that's telling users mandatory fields have not been completed but you don't want to trap them.
Either way the Before Update Event will handle this without the need for queries, DCount, etc