Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2011
    Posts
    20

    Unanswered: Afterupdate on combobox

    I am trying to write some code for a combobox on a form which does two things. Firstly it looks to the afterupdate value of the cmbo and gives a message and then moves focus to another field. This works fine but then I need to alert the user that the status has been changed so that someone else can be alerted to this (x)(in red on the code below). I have the following code;
    Code:
    Private Sub Status_AfterUpdate()
     Select Case Me.Status.Value
     Case 3
     MsgBox "You must enter a reason. If no reason so far please select no reason"
    
    If Me.Status.BeforeUpdate = 1 And Me.Status.AfterUpdate = 3 Then
    MsgBox "Contact x"
    End If
    Me.cmboReason.SetFocus
    Case 6
    MsgBox "You must enter a reason. If no reason so far please select no reason"
    Me.cmboReason.SetFocus
    Case 7
    MsgBox "You must enter a reason. If no reason so far please select no reason"
    Me.cmboReason.SetFocus
    
    End Select
    this gives me a data mismatch when the code runs which is probably true as I'm not entirely sure what I'm doing with this code. The case statement is based on the afterupdate of the combobox (Status) that bit works in isolation but its the extra part I've added which is obviously the problem and I know it is more complicated than I was hoping. There will be a lot of different options for this code based on what is changed from what to what value but this is just one to test. I'm probably going totally the wrong way about it but I'm trying to learn. Thanks in advance to anyone who can help me.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The line

    If Me.Status.BeforeUpdate = 1 And Me.Status.AfterUpdate = 3 Then

    makes absolutely no sense! You cannot use the BeforeUpdate and AfterUpdate events in this manner.Are you trying to compare the Value in Status before and after a selection is made? If so you would need to use the OldValue property for the first and Value property for the latter.

    If Me.Status.OldValue = 1 And Me.Status.Value = 3 Then

    To be honest, this kind of Validation is normally be done in the Form_BeforeUpdate event, checking to see if cmbReason is empty and only popping the MessageBox if it is so, rather than doing it whether or not the user has chosen a reason. If a reason has already been selected, the user will probably be irked by having to close the MessageBox after having done his/her job correctly; I certainly would be!

    Linq ;0)>
    Last edited by Missinglinq; 07-14-11 at 11:01.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Mar 2011
    Posts
    20
    Thanks I see what you mean about the messagebox and the irksomeness of it. I can't work out another way to do it though. The combobox is on a subform and is brought up when the user selects the + next to the selection on the datasheet. This has the status option which has its recordsource from a table. There are other places in the db to alter this status but I want to give the user other ways of changing the data. Basically the user can select 7 options from this depending on what they want to do. It concerns scheduling people onto events. The default is 1 (scheduled) because otherwise they would not be in the table. If a person declines (3) the invitation then the user can change this here but they need to have a reason (which is recorded for reporting purposes) and so the focus shifts to another combobox with reasons. This all works fine but the client now wants to be alerted to the fact that the delegate's status has changed and that they need to contact someone (x) to tell them this. With this in mind i don't think that I want it to check the reason on the form beforeupdate event as i only need to fire the event if something has changed in the status. Forgive me if you can't understand my ramblings it's been a long week so far lol.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •