Full Version: Filtering Bound Control Box - With A Twist
UtterAccess Forums > Microsoft® Access > Access Forms
Squire4Hire
Here's the problem:
I have a datasheet subform with several fields, one of which is a control box with a long list of values that are essentially job numbers. Its values are from a separate table than the subform's source object.
In order to reduce user error, I'm trying to find a way to filter the control box to have the selection of only those jobs which are "open" and the problem comes when I attempt to view a record where the job is now "closed" - it comes up blank when the cbo is filtered.
Does anyone know of a way around this? Or perhaps come up with a viable solution? I've considered using two control boxes, one that would be locked or disabled to display all job numbers and the other to have as a user selection, but this seems redundant and inefficient.
Thoughts?
Jeff B.
I assume you're describing a combobox (i.e., a 'drop-down' control)...
It sounds like you want a way to see ALL of the records, but only be able to alter ... "active" ... ones. If that's a fair paraphrase, one approach might be to list them all in the combobox, but have the form disable-for-edit if the record that gets pulled up is marked "closed" (or some such).
Good luck!
Squire4Hire
Woops! Yes, I meant combo.
That's pretty much exactly what I want to do. The disable-for-edit... would that be written into a BeforeUpdate event?
Jeff B.
Actually, if the form is already attempting an Update, it's too late!
If I had a list of records in a combobox and selected one, I'd probably use the combobox's AfterUpdate event to "set up" the form.
Squire4Hire
I think I got it figured out Jeff. Thanks for the push in the right direction. Here's what I did in a BeforeUpdate Event...
CODE

Dim sStatus As String
Dim sResponse As String
iStatus = Me.cboCostCenter.Column(4)
If sStatus = "Closed" Then
sResponse = MsgBox("This Cost Center is closed. Continue?", vbOKCancel, "Attention!")
If sResponse = vbCancel Then
Me.Undo
End If
End If

Othank you.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.