Prevent user from skipping records in continuous form during data entry

[Liz A]

I have a main form with several continuous subforms. Each subform consists of several listbox controls. I would like to require the user to select an item from the listbox before being allowed to move to the next record in the subform, and upon reaching the last record in that continuous subform, to require an entry there in order to move to the first record in the next cont. subform.

As an added bonus, it would be nice, though not necessary, to automatically jump the focus from one record to the next after data is entered. But my basic goal is to avoid skipping records.

Caveat: I cannot use the "required" option in the field to which the control is bound because that field has a default value previously entered using an append query. (The default value basically means "not yet entered" and is not one of the options in the listbox. I am using this because this field is a foreign key in the table, thus it must have a value in order to have a record with which to populate the subform.)

My apologies if this has been asked and answered elsewhere, but I've searched and, while I found a few related threads, they don't quite answer my question. Here they are for reference:

http://www.access-programmers.co.uk/forums/showthread.php?t=38364
http://www.access-programmers.co.uk/forums/showthread.php?t=69047
http://www.access-programmers.co.uk/forums/showthread.php?t=103176

Thanks for any help! Also, if you are going to recommend VB code, could you be specific about how and where I should use it? I'm not afraid of code, but I'm definitely new to it...

 

[gemma-the-husky]

one obvious thing you could do is to set the properties in the continuous records, other, cycle to current record. That stops the tab button taking the user to another row.

now in the beforeupdate event for the subform, you can test the validity of all the boxes on that subform, before accepting the input. you can do this for individual boxes as well if you want.

a different method would be to you could lock (or gray) every control at the start, and then just unlock them as you go through the various options in turn. You could even make them all hidden. It really depends on what you think looks best.

You could also use some global control flags to manage moving between subforms.

Its hard to visualize what sort of app you have that needs multiple subforms being managed in the way you describe, but you can certainly get it!

 

[Liz A]

Thanks Gemma,

To clarify, I am working with survey/questionnaire data entry where each subform represents a given survey and each record is a question in that survey. I'll start playing around with some of your suggestions and post again with the inevitable follow-up questions...

 

[mic907]

one obvious thing you could do is to set the properties in the continuous records, other, cycle to current record. That stops the tab button taking the user to another row.

now in the beforeupdate event for the subform, you can test the validity of all the boxes on that subform, before accepting the input. you can do this for individual boxes as well if you want.

a different method would be to you could lock (or gray) every control at the start, and then just unlock them as you go through the various options in turn. You could even make them all hidden. It really depends on what you think looks best.

You could also use some global control flags to manage moving between subforms.

Its hard to visualize what sort of app you have that needs multiple subforms being managed in the way you describe, but you can certainly get it!

well if thats not what the OP needs, it would be great if you could explain how to do all the stuff you mentioned anyway as I'm very interested in learning how to do those things

 

[Liz A]

OK, I've made some progress with this, but still have questions. I've used the following code to get a message to appear when the user skips the listbox without entering data.

Private Sub lstResponseCodeID_LostFocus()
If Me.lstResponseCodeID = 66 [my code for "not yet entered"] Then
    MsgBox "Select a value from the list.", vbOKOnly, "No Response Selected"
    Me.lstResponseCodeID.SetFocus
Exit Sub
End If
End Sub

(The value of 66 is from an autonumber PK for response codes. It means "not yet entered" and serves as a placeholder in the table bound to this form.)

This works in the listbox's LostFocus event, but not in the BeforeUpdate event (maybe because there is no update occurring when nothing is selected from the listbox?).

If I use this code and have the subform's cycle property set to Current Record, I can almost prevent someone from skipping a question (they can still click on another question). So, what I'd like to do now is automate the navigation to the next record in the continuous subform, so that when the user does select a valid item from the list, the focus would automatically go to the same listbox in the next record. To do that, can I add an Else to the If statement above, or do I need to enter this code in a different event? And what would the code be?

 

[Liz A]

Just found this post for a MUCH simpler and more flexible way of determining whether an item is selected:

http://www.access-programmers.co.uk/forums/showthread.php?t=115652

Don't know why I didn't find this earlier...

 

[Liz A]

OK, so I have figured out how to make this work, based in large part on what I've learned from other threads on this forum, but have encountered a new wrinkle. The code that's working (using generic identifiers for the controls) is:

Private Sub MyListBoxControlName_Exit(Cancel As Integer)
If Me.MyListBoxControlName.ListIndex = -1 Then
    MsgBox "Select a value from the list.", vbOKOnly, "No Response Selected"
    Me.MyListBoxControlName.SetFocus
ElseIf Me.MyListBoxControlName.ListIndex > -1 And Me.CurrentRecord < Me.RecordsetClone.RecordCount Then
    DoCmd.GoToRecord , , acNext
ElseIf Me.MyListBoxControlName.ListIndex > -1 And Me.CurrentRecord = Me.RecordsetClone.RecordCount Then
    [Forms]![MyMainFormName]![MySecondSubFormName].SetFocus
    [Forms]![MyMainFormName]![MySecondSubFormName].[Form]![MyListBoxControlName].SetFocus
Exit Sub
End If
End Sub

The subform's cycle property is set to current record and I have the record selectors off so that the user can only navigate from one question to the next (one record to the next on my subform) without skipping any, and when they reach the last record in that subform, the focus jumps to the next subform on the same tab control. Works fine BUT here's the new wrinkle:

After adding this code to the listbox's exit event on subform 1, the entire subform has stopped "refreshing" (or requerying, not sure what the correct term is) when a new record is selected on the main form. Instead, it continues to show the subform records that are associated with the first record on the main form. I have the master and child fields correctly linked and the other subforms associated with this main form continue to work correctly. Also, when I delete the code from subform 1, the new problem goes away, i.e., the appropriate records correctly load for the chosen record on the main form. Anyone have any idea why this is? I have read that I should not have to requery with code if my master and child fields are correctly linked and they are.

Although I'm thoroughly enjoying the dialogue I've been having with myself here, any suggestions would be immensely appreciated... Thanks!

 

[mic907]

sorry just wanted to revive this thread; think it would be very helpful not only to me and Liz A, but everyone else as it seems useful for all db's