Question re: Saving Records and Empty Comboboxes

[Badnapp]

Hello everybody,

I'm an access beginner, bare with me.

I have a database that I've created for a number of clerks under me to use to track their daily tasks. I had the help of some other members of this forum when I was building the project 8 months ago.

When clerks are entering their tasks, they do so through a number of combo boxes on a form (Assigned by:, Division of Law:, Subcategory:, Casename etc). When all of the combo boxes have been filled, the employee can click a button in order to save the record to the back end of the database and open a new blank record.

Currently, if an employee tries to enter the wrong data in a combo box and save the record, they will be prompted to enter data and not allowed to save the record; the problem is that if they leave all fields blank they can still save the record - thus creating useless data for reporting purposes. For example, an employee could enter their name and the date of record but leave every other combo box blank and still save the record. I want to lock the form such that they cannot save the record unless all of the combo boxes on the form are populated to reduce user error.

How can I do this simply?

Regards,
-Joel

 

[jdraw]

You could check that each of the controls/text boxes is non blank. You could adjust your table design to make all such fields "required".

Normally with combos you would have "acceptable choices" and not be creating new values. That is to say, the user would select an existing entry in a combo, then move to the next control and do similar activity.
It isn't common for the user to enter new data into a combo.

Perhaps you could post a copy of your db and readers could see the issue first hand.

 

[Badnapp]

I think I'm getting down to the issue.

As you mentioned, they cannot just write things in my comboboxes. All 6 are populated by tables which are stored on my network drive as a separate "back end" file. The clerks have each been distributed a front end file .accde with which to enter their tasks.

My problem as mentioned, is that they can open a form, fill out any one combobox (ie date), leave the rest of the combo boxes blank, and save the record.

I have tried setting the validation rule for my comboboxes to IS NOT NULL but this does not do anything, I believe because the database is split into two parts?

What I am trying now, is to change the properties for the underlying tables so that the fields which relate to my comboboxes are "required" fields. I am hoping this will work.

The only downside is that I cannot edit the back end while clerks are connecting to it via the front end, so I have to wait until they aren't in the office as far as I am aware.

 

[jdraw]

A few options:

-kick out current users for maintenance if it's urgent
-schedule a down time to do your maintenance and inform the clerks beforehand

When do you normally do maintenance?
Whatever you do to prevent the "half-empty" records, you should be testing thoroughly before releasing to operations. You may gain some support and some training if your testing involves the clerks.

Do you have a test database to do some experimenting?

 

[Badnapp]

Unfortunately I'm not very high up at this company. They sort of asked the guy on the low end of the totem pole with no access experience to create a database that's used by 27 people daily, lol. But hey, that's Canadian government right?

Anyways, I don't have a "maintenance period" per say. I usually make my changes in the front end and then "roll out" a new version to the clerks. This is the first time I've had to make design changes to the back end so I have to work out the logistics.

For now I copied the back end onto my desktop and changed all of the relevant fields to "required," but I will have to wait until all of the clerks go home to paste this version into their shared drive over the existing back end so that I can test it's efficiency.

I'm hoping that by setting all of the fields to required (which the combo boxes are based off) the clerks will not be able to leave them blank when they save records.

 

[Badnapp]

I still can't quite figure this out.

I'm at a point where if the user puts his cursor in a combo box, he is forced to make a selection. However, I have a button in the top right corner of the form to save the current record and create a new one, and if the user clicks this button it bypasses this whole forced to make a selection thing.

Here is the code for the form.

Private Sub btnSaveandNew_Click()
DoCmd.GoToRecord , , acNewRec
End Sub

Private Sub cboRecordDate_Exit(Cancel As Integer)
If IsNull(Me![cboRecordDate]) Then
MsgBox "Please ensure no fields are left blank."
Cancel = True
End If
End Sub

Private Sub cboDivision_Exit(Cancel As Integer)
If IsNull(Me![cboDivision]) Then
MsgBox "Please ensure no fields are left blank."
Cancel = True
End If
End Sub

Private Sub cboSubcategory_Exit(Cancel As Integer)
If IsNull(Me![cboSubcategory]) Then
MsgBox "Please ensure no fields are left blank."
Cancel = True
End If
End Sub

Private Sub WorkAssignedBy_Exit(Cancel As Integer)
If IsNull(Me![WorkAssignedBy]) Then
MsgBox "Please ensure no fields are left blank."
Cancel = True
End If
End Sub

Private Sub cboNatureofWork_Exit(Cancel As Integer)
If IsNull(Me![cboNatureofWork]) Then
MsgBox "Please ensure no fields are left blank."
Cancel = True
End If
End Sub

Private Sub txtNameofAssignment_Exit(Cancel As Integer)
If IsNull(Me![txtNameofAssignment]) Then
MsgBox "Please ensure no fields are left blank."
Cancel = True
End If
End Sub

Private Sub txtTimeSpent_Exit(Cancel As Integer)
If IsNull(Me![txtTimeSpent]) Then
MsgBox "Please ensure no fields are left blank."
Cancel = True
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not (Me.NewRecord) Then
If MsgBox("Would You Like To Save The Changes To This Record?", vbQuestion + vbYesNo + vbClose, "Save Changes to Record ???") = vbNo Then
Me.Undo
End If
Else
If MsgBox("Would You Like To Save This New Record?", vbQuestion + vbYesNo + vbClose, "Save This Record ???") = vbNo Then
Me.Undo
End If
End If
End Sub

 

[Badnapp]

The user clicks a button to save the record and create a new one.

Can I add code to the button that ensures all of the combo boxes on the form DO NOT have a null value before it will save the record?

 

[jdraw]

Please tell me a little more about the database and which department you are working for (I spent several years in Cdn govt setting up Database and Data Mgmt ).
If, as you say, the "low man on the pole" designs and develops database apps for multiple users; has no training; and is told what to do, then there is a serious management issue(s) including in part:
- human relations - communication, supervision, instructions, feedback
- lack of knowledge of data management and database principles
- false economy to not train technical people
- no established work environment


It s not uncommon for management to be sold software and then expect databases to "magically happen". The magic means 100% perfection; no design flaws; no misunderstanding of any requirement; no programming errors; no typos; no machine failures.....(Many posts on these forums have this as the basis of problems/issues).

Anyway, there are ways to keep a user on a control until the control is populated or an option to start again without saving a partial record. As I recall, there is a situation where a user can back-tab/backspace out of a control that may be an issue.

I'd like to see a copy of your FE and BE. In the mean time I'll do a little more searching -- I know Pat Hartman has written on this subject.

Good luck.

 

[Badnapp]

I work for Ministry of Attorney General, in the Supreme Court of Justice.

The reason that Judicial Information Technology Office has not designed this DB, is because it is not an extremely important project, so I offered to take a shot at it.

It is simply a database that the law clerks in Ontario use to track tasks that they complete each day.

I have attached a screenshot of the form in question so you have a better idea of what I'm trying to say. I feel like there is a breakdown of communication here.

The database has been split into two parts, with all forms populated by data stored in the tables in the back end. The back end stays on the shared drive that the clerks use, while each clerk gets a copy of the front end to enter their tasks.

As you can see on the form, the clerk has 10 controls to fill out. 6 of these are comboboxes, 4 of them are textboxes (1 of these is formatted for the date). 2 of the comboboxes (Name+region) are automatically populated when the user logs into the database, and cannot be changed. When the user clicks the button labelled "Save and New Record," a yes/no window prompts up asking if the user would like to save the record, if they click yes a record is saved and the comboboxes are cleared as a new record is created.

Here is what I want:

When the user clicks this button "Save and New Record," a script runs that checks all of the comboboxes and text boxes for null values, if any of the boxes are blank I want a window to pop up saying "All fields must be filled" or something to that effect. I do not want law clerks to be able to save records that are missing data.

With the current SQL + a set tab order, the user must populate a combo box once they change it, however if they simply do not put any data in and press the button "Save and New Record" it will save a blank record, which again is useless.

The SQL code driving the form is as follows:

Private Sub btnSaveandNew_Click()
DoCmd.GoToRecord , , acNewRec
End Sub

Private Sub cboRecordDate_Exit(Cancel As Integer)
If IsNull(Me![cboRecordDate]) Then
MsgBox "Please ensure no fields are left blank."
Cancel = True
End If
End Sub

Private Sub cboDivision_Exit(Cancel As Integer)
If IsNull(Me![cboDivision]) Then
MsgBox "Please ensure no fields are left blank."
Cancel = True
End If
End Sub

Private Sub cboSubcategory_Exit(Cancel As Integer)
If IsNull(Me![cboSubcategory]) Then
MsgBox "Please ensure no fields are left blank."
Cancel = True
End If
End Sub

Private Sub WorkAssignedBy_Exit(Cancel As Integer)
If IsNull(Me![WorkAssignedBy]) Then
MsgBox "Please ensure no fields are left blank."
Cancel = True
End If
End Sub

Private Sub cboNatureofWork_Exit(Cancel As Integer)
If IsNull(Me![cboNatureofWork]) Then
MsgBox "Please ensure no fields are left blank."
Cancel = True
End If
End Sub

Private Sub txtNameofAssignment_Exit(Cancel As Integer)
If IsNull(Me![txtNameofAssignment]) Then
MsgBox "Please ensure no fields are left blank."
Cancel = True
End If
End Sub

Private Sub txtTimeSpent_Exit(Cancel As Integer)
If IsNull(Me![txtTimeSpent]) Then
MsgBox "Please ensure no fields are left blank."
Cancel = True
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not (Me.NewRecord) Then
If MsgBox("Would You Like To Save The Changes To This Record?", vbQuestion + vbYesNo + vbClose, "Save Changes to Record ???") = vbNo Then
Me.Undo
End If
Else
If MsgBox("Would You Like To Save This New Record?", vbQuestion + vbYesNo + vbClose, "Save This Record ???") = vbNo Then
Me.Undo
End If
End If
End Sub

I do not believe I need to alter the back end in order to make the changes I have requested, if I do need to access the back end that is fine as well.

Regards,

 

[jdraw]

The subject matter seemed a little familiar, I remember some of this from this and related posts.

I have just put together a few thoughts re Form validation, which I'm attaching. Material is from posts etc.

Good luck.

I'd like to see a jpg of your tables and relationships to see how things have evolved.

 

[Badnapp]

Oh hey again!

I didn't realize I was speaking to somebody who had already helped me with this database lol.

I will upload some jpegs tomorrow. Just for your own curiosity, the clerks have used it without any issues ever since I rolled it out to them. The only thing that has become an issue is directly related to this thread; because the clerks can leave dropboxes blank, there are many records that have been saved with null data depending on which dropbox they left blank. ie We could have a lot of tasks that Dave has entered but we're not sure which judge assigned them to him because he left that dropbox blank when saving the record.

Aside from this minor issue, everything has gone smoothly. The database has been running much smoother since I went through all of that troubleshooting with you re: tips to improve the speed of an access database and setting a persistent link to the front end so multiple users can access it simultaneously.

The overall feedback from the 2013-2014 clerks and their manager was positive.

I just have to iron out this one little kink for the 2014-2015 clerks

 

[jdraw]

Glad to hear it's been a positive roll out.

Check out the various tips/thoughts on Form validation.

 

[Badnapp]

I read your tips on form validation, while helpful I'm still not sure what to do.

1) I went into the back end and changed all of the relevant columns to required: yes allowzls: no. This almost fixes the problem, as the user cannot save a record unless all of the comboboxes are filled out. Actually this is exactly what I want except for some glitchy behavior. ie If a user fills out half of the combo boxes, and then presses save record, they will not be allowed to save it (which is good). However, if the user continues filling out the rest of the combo boxes, they will still not be allowed to save the record, and may get an #error message in a couple of the combo boxes. The only successful way to save a record is to fill in all of the combo boxes before you press the "save" button. So, I guess this is what I want, except for the wonky behavior. ie If a clerk fills out 5/6 fields, accidentally presses save, realizes their error, and fills in the last field, they should then be able to save without starting the record from scratch. I guess not a big deal.

2) I'm trying to write something into the BeforeUpdate event to check the comboboxes for null or zls but I'm not sure what I want to happen if it finds them, or how to word it per say. For example:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Me.cboDivision) = 0 Then ?Cancel?

So I guess what this code says is: Before the form updates itself run this->
If the length of the characters in combo box "Division" is 0 then do not allow the record to be saved?

I would like the code to do something like: Check the values of all combo boxes on the form, if the value of any of them is zero display a message "all fields must be filled" and then allow the user to continue filling out the fields. If all combo boxes are filled the record is saved. The vba code I have on my "save and new" button basically does two things, it checks if the current record is new or has any changes to it, if it is not a new record it asks "would you like to save this record" if it is a new record, then the save button does nothing. So it would make sense, that this save button would also be the trigger that checks the combo boxes before saving said record and creating a new one.

Summary: I click on button, button checks if the record is new or not (ie blank or not) if the record is not blank then all of the combo boxes are checked for null or zls. If any of the combo boxes are empty, a message box appears "please fill in all fields" and the record is not saved. If all of the combo boxes are filled in, the record is saved and a new record is created, leaving the combo boxes blank again.

 

[jdraw]

There is a section in the tip

--If you don't want to require records to be complete when they are saved, you need an additional data field in the table to indicate complete/incomplete and a button on your form. Unless the user presses the button, the validation rules don't run. Only if the validation is successful is the complete flag set. Since the complete flag isn't accessible via the interface (if you display it on the form, you MUST lock it), the user can't change the value. The value is only changed by your validation code.

You could try that. Then the issue would be that an Incomplete record would not have all required fields, but has been saved with a flag indicating incomplete, so you would have to respect the complete/incomplete when doing queries and/or reports etc.

You really have to identify --again in business terms -- what exactly should happen, that is what exactly is required.

The concept here is to not lose any keyed in data, but not accepted as"official/useful" until the field is updated to Complete --then you have another "officiail/acceptable" record wth all mandatory fields populated and vetted.

You could have another form/procedure to check all "incomplete" records, and based on some rules, cause those records to be presented to the clerk for update/input to get them to "Completed" status.

It all depends on what the Business Rules say.

The secret here is - don't guess and just change some code. Figure out what should happen; confirm it with those involved; then adjust your
code/editing/input procedures... accordingly. Test and monitor.

Good luck.