none
How to resolve syntax to get out of a control

    Question

  • The following code prevents an incorrect value from being selected.

    Private Sub MaintenanceFee_Exit(Cancel As Integer)
        Dim ctrl As Control
        Set ctrl = Me.MaintenanceFee
            If ctrl.Column(5) = "False" Then
                MsgBox "Not an Active Fee, Please choose an Active Fee.", vbExclamation, "Invalid Fee Selection"
                Cancel = True
                ctrl.Undo
            End If
    End Sub

    Additional Info: Historical files show Inactive Fees as it should since it was selected prior to the Fee being InActivated.

    The problem: If a user selects the Fee by mistake "just to see it better" then they are stuck.

    • Can the code be modified to account for this OR
    • Can additional code be added elsewhere to account for this OR
    • Is there a better method than I am using?
    • Something I haven't thought about?

    Thank you for your help!


    Chris Ward


    • Edited by KCDW Tuesday, December 11, 2012 4:56 PM
    Tuesday, December 11, 2012 4:55 PM

All replies

  • The following code prevents an incorrect value from being selected.

    Private Sub MaintenanceFee_Exit(Cancel As Integer)
        Dim ctrl As Control
        Set ctrl = Me.MaintenanceFee
            If ctrl.Column(5) = "False" Then
                MsgBox "Not an Active Fee, Please choose an Active Fee.", vbExclamation, "Invalid Fee Selection"
                Cancel = True
                ctrl.Undo
            End If
    End Sub

    Additional Info: Historical files show Inactive Fees as it should since it was selected prior to the Fee being InActivated.

    The problem: If a user selects the Fee by mistake "just to see it better" then they are stuck.

    • Can the code be modified to account for this OR
    • Can additional code be added elsewhere to account for this OR
    • Is there a better method than I am using?
    • Something I haven't thought about?

    Hi Chris,

    If MaintenanceFee is a listbox, why not display only those rows [Inactive Fees] - or column(5) - are False.

    It is my "internal standard" to only show items that are "possible", and not also the items that are disabled. It makes coding (and life) a little easier.

    Imb.

    Tuesday, December 11, 2012 5:04 PM
  • thanks for your reply Imb!

    As I mentioned in the post if someone is looking at a historical file it displays correctly, If I hide inactive Fees in the RowSource then the FeeID displays in the Form instead of the Fee Description. Is there another way to hide them in the Form from selection but still show for older records? That was my first Idea in another post but could not get that to work. It was suggested to use the above approach to make them viewable but not allow the selection of them. I have them sorted to the end of the list in the Multi-Select ComboBox, most users don't travel that far down in the list to pick the Fees. At least this way they show as needed for older records and are not selectable for newer records. Just if a user decides they need to click on it for some reason...They get stuck. All they can do is close the db and reopen.

    Any other ideas?

    Thank you!!


    Chris Ward

    Tuesday, December 11, 2012 5:17 PM
  • thanks for your reply Imb!

    As I mentioned in the post if someone is looking at a historical file it displays correctly, If I hide inactive Fees in the RowSource then the FeeID displays in the Form instead of the Fee Description. Is there another way to hide them in the Form from selection but still show for older records? That was my first Idea in another post but could not get that to work. It was suggested to use the above approach to make them viewable but not allow the selection of them. I have them sorted to the end of the list in the Multi-Select ComboBox, most users don't travel that far down in the list to pick the Fees. At least this way they show as needed for older records and are not selectable for newer records. Just if a user decides they need to click on it for some reason...They get stuck. All they can do is close the db and reopen.

    Any other ideas?

    Thank you!!


    Chris Ward

    Hi Chris,

    On every continuous form (but because they are all the same there is in fact only one) I have the possibility to add one or more command buttons on top of the form.
    Pressing such a command button manipulates the RecordSource of the form by adding an additional condition. In case one or more of these command buttons are displayed, then I also have a command button to show all the records without any restriction.

    This works very for switching between active - inactive - all - or whatever preselections you wan to make.

    Imb.

    Tuesday, December 11, 2012 5:32 PM
  • I don't think you should use the Exit event for this.  Most likely you should be using the BeforeUpdate event.
    Tuesday, December 11, 2012 5:35 PM
  • Thanks again Imb!

    I think this is unique to your proposed solution, perhaps I don't fully understand. Records are not necessarily Inactive but the Fee for that record may be. If on the Form I select only show Active Records, I will still have an issue with the Inactive Fee for a record that is still active.

    Our setup is after fees are paid that were correct at the time of payment a record can be open for decades and the fees may change several times in the course of the record being active. So I can't hide the record by selecting only show active records.

    Now I thought about changing the Rowsource for the control when Adding New Records or Editing Records, but I don't see how that would work in this case. Also the Forms in this db are set to single record only.

    Still, I am open to suggestions!

    Thank you!!


    Chris Ward

    Tuesday, December 11, 2012 5:46 PM
  • Thanks Marshall!

    I first used the Before Update Event but had issues where using the Before Update Event for the Control simply did nothing to enforce not selecting the inactive item and the Before Update Event for the Record errored out on the code line .Dirty = False. So switching to the OnExit Event seemed the way to go as the code functioned and did not Error just in the unlikely event someone chose to select in Inactive Fee they were stuck. Here is the link to the other thread that led to this thread.

    Thank you for your help!!


    Chris Ward

    Tuesday, December 11, 2012 5:54 PM
  • Thanks again Imb!

    I think this is unique to your proposed solution, perhaps I don't fully understand. Records are not necessarily Inactive but the Fee for that record may be. If on the Form I select only show Active Records, I will still have an issue with the Inactive Fee for a record that is still active.

    Our setup is after fees are paid that were correct at the time of payment a record can be open for decades and the fees may change several times in the course of the record being active. So I can't hide the record by selecting only show active records.

    Now I thought about changing the Rowsource for the control when Adding New Records or Editing Records, but I don't see how that would work in this case. Also the Forms in this db are set to single record only.

    Still, I am open to suggestions!

    Thank you!!


    Chris Ward

    Hi Chris,

    I was a little too hasty too read your problem thoroughly, and then to answer properly, because I had to run for my running activity.

    The terms "active" or "inactive" were only used as names to characterize some preselection of recordsets, and did not refer to control in your example.

    In your original post a user could SELECT a fee by mistake. It was this selection process that I was referring to. The selection can be done in the RecordSet of a form, or in the RowSource of a listbox. In both cases you can play around with the sql-string that will be assigned to RecordSource or RowSource respectively. This means that with such a construction the users can only select between valid choices. It does not mean that the user can not make a wrong valid choice, but it prevents that the user makes a nonvalid choice, without telling afterwards that is was a wrong choice.

    These SELECTIONS are made both single records forms and continuous forms. To do these selections I do not use list boxes. Instead I use "normal" continuous forms, with far more flexibility and possibilities then listboxes. This is especially very handy if you have, for example, to decide if the current "John Smith" among the many "John Smith"-es, is the one you were looking for.

    I hope this explains a little the background of my answer.

    Imb.

    Tuesday, December 11, 2012 10:16 PM
  • Thanks Imb!

    Hope you had a good run!!

    I understand you have a special way of handling many things but I am limited by constraints placed on me.

    The Terms Active or Iactive directly correlate to the Field "Active" in the Multi-Select ComboBox in the sense thatif CheckMarked it is True and if Not CheckMarked it is False. Active = True and Inactive = False.

    In the original post a user could try selecting a False or Inactive Fee and try to move on they will recieve a popup message telling them it cannot be used so pick another and the selection is removed. If they select a True or Active Fee then life goes on. The problem arises when they visit an older (historical yet still current) record where the fee is no longer True or Active and if the user happens to accidentally click that fee on that record then try moving on, they get that same popup message but the record cannot corrected by the code "Undo" or Esc Key and you cannot leave the record.

    I can not use a Continuous Form as you suggest. This is a constraint I must abide. I would like to hear your explaination of the recordsource/rowsource play for the multi-select combo box very much. This may be the part I can use.

    Thank you for your continued help.


    Chris Ward

    Tuesday, December 11, 2012 10:46 PM
  • Hope you had a good run!!

    I understand you have a special way of handling many things but I am limited by constraints  The problem arises when they visit an older (historical yet still current) record where the fee is no longer True or Active and if the user happens to accidentally click that fee on that record then try moving on, they get that same popup message but the record cannot corrected by the code "Undo" or Esc Key and you cannot leave the record.

    Hi Chris,

    I like the running. It is a good annealing process for my software problems, and very effective anti RSI. Besides it keep you young, or at least vital - I need that at my age.

    With your explanation - and probably the after run effect? - I now understand what you mean. In all my applications I have the possibility to control the editability on form level, on record level AND on control level. If a control may not be changed (any more), than the control will be disabled. This disabling of a control is dependant on the context in which it is used, and further on the user authorisation level of that control. So, when such a disabled control MUST be changed, it can be done after gaining some more authorisation.

    I also understand that you have your limitations in what you can use, but perhaps it gives some alternative approaches.

    About the "multi-select combo box", please explain a little more. I have hardly experience with combo boxes, and not at all with multi-selects. But I am sure I have an alternate way of working to get a same result.

    Imb.

    Tuesday, December 11, 2012 11:14 PM
  • Not sure I followed all that somewhat convoluted logic of what you want to allow and not allow, but if you want to prevent used from using the combo box when an inactive item was previously selected, then why not just disable the combo box?

    In single view, use the Current event:

       Me.MaintenanceFee.Enabled = (Me.MaintenanceFee. = "False"

    In Continuous view, add a hidden text box (named txtActive) with the expression  =MaintenanceFee.Column(5)  and use ConditionalFormatting to enable/disable the combo box:

        [txtActive] <> "False"

    Wednesday, December 12, 2012 12:20 AM
  • Just a thought.

    Have you tried the After Update event?

    With something like:

            If Me.MaintenanceFee.Column(5) = "False" Then
                MsgBox
    "Not an Active Fee, Please choose an Active Fee.", vbExclamation, "Invalid Fee Selection"
                Me.MaintenanceFee = Me.MaintenanceFee.OldValue

            End If

    Wednesday, December 12, 2012 12:30 AM
  • Not sure I followed all that somewhat convoluted logic of what you want to allow and not allow, but if you want to prevent used from using the combo box when an inactive item was previously selected, then why not just disable the combo box?

    Hi Marshall,

    If you have the situation that the value "must be changed sometimes, but mostly not", you get the same convoluted logic. And in my opinion ConditionalFormatting is then not the solution.

    Imb.

    Wednesday, December 12, 2012 8:00 AM
  • Well, it seems to me that it is difficult to automate logic for "must be changed sometimes, but mostly not".  So, if that can be simplified to "can't be changed", the problem is a lot easier.

    I still say that the BeforeUpdate event is where a change needs to be allowed/disallowed.  But there is something that I just do not understand about saving the record immediately if a change will be allowed, but before checking to see if the change can be allowed ???

    Wednesday, December 12, 2012 3:46 PM
  • Well, it seems to me that it is difficult to automate logic for "must be changed sometimes, but mostly not".  So, if that can be simplified to "can't be changed", the problem is a lot easier.

    I still say that the BeforeUpdate event is where a change needs to be allowed/disallowed.  But there is something that I just do not understand about saving the record immediately if a change will be allowed, but before checking to see if the change can be allowed ???

    Hi Marshall,

    The goal is not to automate that kind of logic, the goal is to handle situations that can occur.

    After a valid "Inactivation" circumstances have changed, and it is necessary to "Activate" again. It is a little bit too simple to say: "can't be changed". It is evenly not good to do the change directly in the data table, so there must be a provision to handle this. That is what I mean.

    But I agree that checking must be done before saving data.

    Imb.

    Wednesday, December 12, 2012 7:18 PM
  • Sorry for being away today. Thank you for your responses.

    In a nutshell

    A fee was selected in January for $500.00 to test material. That test was completed. A new set of Tests are scheduled to begin on the same material. The new tests total $1500.00. New suppliers want their products tested. Meanwhile the pricing has changed for the original test of materials. The new cost is $600.00. The old price is inactivated and a new record in the pricing table is created for the new price.

    Now a person can select the new price in the list that displays within the combobox (set up as multi-select). The problem is if a user needs to review records and see the Fees, the original problem was that inactive fees would show the ID instead of the Fee name. So I made the Fees show again and popped in a code so that the inactive Fees could not be selected even though they appear in the list within the multi-select combobox. So the problem is if someone clicks the fee by mistake in an old record that should have that value and don't want it changed because it shouldn't be, then I want them to be able to click the OK button on the message box and get on with their lives. Instead they are stuck and have to close the db and reopen.

    Thank you for taking the time to read and post here. I really do appreciate your time and efforts!


    Chris Ward

    Wednesday, December 12, 2012 10:56 PM
  • I've had this on the back burner for a while but I still am looking for a solution to this. I'd appreciate anyone who can enlighten me in the solution.

    Thank you!


    Chris Ward

    Friday, March 01, 2013 4:58 PM
  • I've had this on the back burner for a while but I still am looking for a solution to this. I'd appreciate anyone who can enlighten me in the solution.

    Thank you!


    Chris Ward

    Hi Chris,

    I do not know if I understand your problem completely. I can understand why users may only choose active Fees, but not why you have to include the inactive Fees. Is this to correct already chosen Fees, that only occur very occasionally?

    In that case I would only show the active Fees for normal use, and the total list after some kind of privileged action.

    Or did I miss the problem?

    Imb.

    Friday, March 01, 2013 7:53 PM
  • You know I am sitting here looking at this and thinking the issue I need resolving is really limited to one thing and that is keeping the database rolling after the code runs. So I think I really just need to insert something to say 'go' but I don't know what it should be. Maybe set focus back onto the Form might be the issue. but here is the code that says no you can't choose that inactive fee.

    Private Sub MaintenanceFee_Exit(Cancel As Integer)
        Dim ctrl As Control
        Set ctrl = Me.MaintenanceFee
            If ctrl.Column(5) = "False" Then
                MsgBox "Not an Active Fee, Please choose an Active Fee.", vbExclamation, "Invalid Fee Selection"
                Cancel = True
                ctrl.Undo
            End If
    End Sub

    Maybe add the following?

    Private Sub MaintenanceFee_Exit(Cancel As Integer)
        Dim ctrl As Control
        Set ctrl = Me.MaintenanceFee
            If ctrl.Column(5) = "False" Then
                MsgBox "Not an Active Fee, Please choose an Active Fee.", vbExclamation, "Invalid Fee Selection"
                Cancel = True
                ctrl.Undo
            End If
        Me.MaintenanceFee.SetFocus
    End Sub

    Any thoughts?

    Chris Ward

    Friday, March 01, 2013 8:31 PM
  • You know I am sitting here looking at this and thinking the issue I need resolving is really limited to one thing and that is keeping the database rolling after the code runs. So I think I really just need to insert something to say 'go' but I don't know what it should be. Maybe set focus back onto the Form might be the issue. but here is the code that says no you can't choose that inactive fee.

    Hi Chris,

    Still wondering why the user can choose an inactive fee, after which you get the message that it is forbidden with all the problems afterwards.

    My situation is probably a little different form yours. I never use comboboxes, so I am not too good in understanding their behaviour. Instead I use a Selection Form.
    After the selection (of a record) from the Selection Form, the relevant value is assigned to the reveiving control. Then in the BeforeUpdate of the receiving control I do all the checking if the value is acceptable in the current context. If not, a message is generated, cancel = true, so that one can not leave that control.

    Perhaps it gives you a clue.

    Imb.

    Friday, March 01, 2013 11:04 PM
  • 1) wondering why the user can choose an inactive fee

    2) After the selection (of a record) from the Selection Form, the relevant value is assigned to the receiving control. Then in the BeforeUpdate of the receiving control I do all the checking if the value is acceptable in the current context. If not, a message is generated, cancel = true, so that one can not leave that control.

    Perhaps it gives you a clue.

    Imb.

    1) In a single Form I use the multi-select cbo for the selection of the fees. Over time, fees become inactive but still need to display on the Form for a record in which it was originally chosen. It still displays in the list but has been moved to the end as an InActive Fee. Since it was chosen originally, when you retrieve the record that value is what is displayed in the control. In new records nothing is displayed until you use the cbo dropdown to display choices. Once you checkmark values they are displayed as comma delimited values in the reduced control.

    I thought about using a separate textbox on top of the multi-select cbo and having a d-lookup to the record to display the values but it seems like it should be workable in the after-update event to simply check if the value is inactive in the cbo and if it is then popup a message saying so, Undo the control and require a selection be made.

    Now in reviewing the comments over the past couple of months, I see I was remiss in considering the response of Alphonse      Me.MaintenanceFee = Me.MaintenanceFee.OldValue   

    There may be merit I will have to test out when I get back to work.

    But keep in mind this is a control update and not a record update. You can update the control without updating the record. Once the record is updated the value in the cbo is committed to the record.


    Chris Ward

    Monday, March 04, 2013 2:14 AM
  • Things Learned today,
    Using Me.MaintenanceFee = Me.MaintenanceFee.OldValue
    In Before UpDate, AfterUpdate & On Exit would not raise the flag.

    Also learned the original code does not work if multiple selections are made.
    I need to treat this as a list and look for the value in list

    If Field contains 0 Then Raise the message.


    Chris Ward

    Thursday, March 07, 2013 10:52 PM