Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2002
    Location
    Ann Arbor, MI
    Posts
    47

    Question Unanswered: Reset a value in a combo box on a form

    I have an unbound combo box on a form that is used to select the Status. I am trying to set up "rules" for when the status can be changed to "completed". Basicaly, a few check boxes must be selectd in order for this to happen IF some other conditions are met. If the conditions are not met, I jump to a macro. If the conditions ARE met and the boxes are not checked, I display a msgbox ... here is my code (forgive my non-elegance as I am fairly new to VB)

    Private Sub STATUS_AfterUpdate()


    'Sets rules for Closing an ECN
    'For Status to = "COMPLETED", the appropriate dispositions must be complete

    On Error GoTo err_STATUS_AfterUpdate
    Dim VarType
    Dim StrECN, StrStatus As String
    Dim BolInt As Boolean
    Dim BolExt As Boolean
    VarType = Me![ECN TYPE]
    StrECN = Me![ECN #]
    StrStatus = Me![STATUS]
    BolInt = Me![DispInt]
    BolExt = Me![DispExt]

    'Checks for new or old ECN
    If StrECN < 24200 Then
    GoTo Macro_STATUS_AfterUpdate
    End If

    'Is the ECN being closed?
    If StrStatus = "COMPLETED" Then
    GoTo test_STATUS_AfterUpdate
    Else
    GoTo Macro_STATUS_AfterUpdate
    End If

    'Is the ECN of type A? and are the Internal and External Dispositions completed?
    test_STATUS_AfterUpdate:
    If VarType = "A" & BolInt = True & BolExt = True Then
    DoCmd.Beep
    DoCmd.Beep
    GoTo Macro_STATUS_AfterUpdate
    Else:
    GoTo NoNo_STATUS_AfterUpdate
    End If

    Macro_STATUS_AfterUpdate:
    DoCmd.RunMacro "Change ECN Status.Update ECN #"
    Exit Sub

    NoNo_STATUS_AfterUpdate:
    DoCmd.Beep
    MsgBox ("You cannot set the Status to COMPLETED without first completing the dispositions")
    Exit Sub

    err_STATUS_AfterUpdate:
    MsgBox Err.DESCRIPTION
    End Sub


    What I want to do is AFTER the MsgBox pops up in NoNo_STATUS_AfterUpdate, I want to reset the value of the Combo Box to the original value (before the user changed it to "COMPLETED"

    I appreciate all the help I can get.

  2. #2
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Re: Reset a value in a combo box on a form

    Try this

    Dim tempStatus As String
    Before showing the messageBox : tempStatus = combobox.value
    After showing the messageBox : combobox.value = tempStatus

  3. #3
    Join Date
    Sep 2002
    Location
    Ann Arbor, MI
    Posts
    47

    Re: Reset a value in a combo box on a form

    Originally posted by artemide
    Try this

    Dim tempStatus As String
    Before showing the messageBox : tempStatus = combobox.value
    After showing the messageBox : combobox.value = tempStatus
    Question: If I grab the value within this event (AfterUpdate), won't the original value allready have been changed?

    Thanks

  4. #4
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Re: Reset a value in a combo box on a form

    The value which is shown in the combobox will be stored.
    So in the AfterUpdate-event it's already too late.
    But there should be a moment where you can grab the needed value, it doesn't matter if it's in another procedure.
    But remember to declare the value outside the procedures.

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i think what he meant was put

    tempStatus = combobox.value

    on the before_update Event of the combo box

    and

    combobox.value = tempStatus where ever you want teh result reseting

    thought

    dim tempstatus as variant might be needed if the combo isn't a string
    Last edited by m.timoney; 04-09-03 at 12:33.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Sep 2002
    Location
    Ann Arbor, MI
    Posts
    47

    AARG

    Originally posted by m.timoney
    i think what he meant was put

    tempStatus = combobox.value

    on the before_update Event of the combo box

    and

    combobox.value = tempStatus where ever you want teh result reseting

    thought

    dim tempstatus as variant might be needed if the combo isn't a string
    Thanks for your help. folks. This is the code I have now:




    Option Compare Database
    Option Explicit
    Dim VarTempStatus As Variant

    _________________________________

    Private Sub STATUS_AfterUpdate()


    'Sets rules for Closing an ECN
    'For Status to = "COMPLETED", the appropriate dispositions must be complete

    On Error GoTo err_STATUS_AfterUpdate
    Dim VarType
    Dim StrECN, StrStatus As String
    Dim BolInt As Boolean
    Dim BolExt As Boolean
    VarType = Me![ECN TYPE]
    StrECN = Me![ECN #]
    StrStatus = Me![STATUS]
    BolInt = Me![DispInt]
    BolExt = Me![DispExt]

    If StrECN < 24200 Then
    GoTo Macro_STATUS_AfterUpdate
    End If

    If StrStatus = "COMPLETED" Then
    GoTo test_STATUS_AfterUpdate
    Else
    GoTo Macro_STATUS_AfterUpdate
    End If




    'for A only

    test_STATUS_AfterUpdate:
    If VarType = "A" & BolInt = True & BolExt = True Then
    DoCmd.Beep
    DoCmd.Beep
    GoTo Macro_STATUS_AfterUpdate
    Else:
    GoTo NoNo_STATUS_AfterUpdate
    End If



    Macro_STATUS_AfterUpdate:
    DoCmd.RunMacro "Change ECN Status.Update ECN #"
    Exit Sub

    NoNo_STATUS_AfterUpdate:
    DoCmd.Beep
    MsgBox ("You cannot set the Status to COMPLETED without first completing the dispositions")
    StrStatus.Value = VarTempStatus
    Exit Sub

    err_STATUS_AfterUpdate:
    MsgBox Err.DESCRIPTION
    End Sub


    _____________________________________________

    Private Sub STATUS_BeforeUpdate(Cancel As Integer)

    VarTempStatus = Me![STATUS].Value

    End Sub




    When I run it I get a Compile Error: Invalid qualifier. It points to the line reading: StrStatus.Value = VarTempStatus.

    what am I missing??

  7. #7
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Re: AARG

    I'm not a specialist either so I can only tell you the way I did it.

    I have a combobox based on a query (or a table) : In your case for example tblStatus.

    tblStatus has 2 fields :
    - statusID --> nummeric
    - statusDescription --> text

    the combobox contains 2 columns :
    - statusID
    - statusDescription

    In the combobox-properties you set "Number of Columns = 2" and
    "Column width" = 0cm;4cm
    To set "column width" = 0cm, the corresponding column will not be shown.

    Because my first column is nummeric I can debug.print combobox.value and it gives me the statusID of the selected statusDescription.
    I store this value in an integer (intOriginalValue).
    I choose another description from the combobox.
    If I write 'combobox.value = intOriginalValue' it shows me the previous description.

    Hope this helps.

  8. #8
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820

    Re: AARG

    the code looks all right but them it's probably something suttle

    Email a copy of you DB to michael.timoney@uk.agimedia.com and i'll see if i can find the problem for you just make sure you zip it up other wise outlook will block it.

    Originally posted by NightZen
    Thanks for your help. folks. This is the code I have now:




    Option Compare Database
    Option Explicit
    Dim VarTempStatus As Variant

    _________________________________

    Private Sub STATUS_AfterUpdate()


    'Sets rules for Closing an ECN
    'For Status to = "COMPLETED", the appropriate dispositions must be complete

    On Error GoTo err_STATUS_AfterUpdate
    Dim VarType
    Dim StrECN, StrStatus As String
    Dim BolInt As Boolean
    Dim BolExt As Boolean
    VarType = Me![ECN TYPE]
    StrECN = Me![ECN #]
    StrStatus = Me![STATUS]
    BolInt = Me![DispInt]
    BolExt = Me![DispExt]

    If StrECN < 24200 Then
    GoTo Macro_STATUS_AfterUpdate
    End If

    If StrStatus = "COMPLETED" Then
    GoTo test_STATUS_AfterUpdate
    Else
    GoTo Macro_STATUS_AfterUpdate
    End If




    'for A only

    test_STATUS_AfterUpdate:
    If VarType = "A" & BolInt = True & BolExt = True Then
    DoCmd.Beep
    DoCmd.Beep
    GoTo Macro_STATUS_AfterUpdate
    Else:
    GoTo NoNo_STATUS_AfterUpdate
    End If



    Macro_STATUS_AfterUpdate:
    DoCmd.RunMacro "Change ECN Status.Update ECN #"
    Exit Sub

    NoNo_STATUS_AfterUpdate:
    DoCmd.Beep
    MsgBox ("You cannot set the Status to COMPLETED without first completing the dispositions")
    StrStatus.Value = VarTempStatus
    Exit Sub

    err_STATUS_AfterUpdate:
    MsgBox Err.DESCRIPTION
    End Sub


    _____________________________________________

    Private Sub STATUS_BeforeUpdate(Cancel As Integer)

    VarTempStatus = Me![STATUS].Value

    End Sub




    When I run it I get a Compile Error: Invalid qualifier. It points to the line reading: StrStatus.Value = VarTempStatus.

    what am I missing??
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  9. #9
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    found the problem

    it's right here

    Dim StrECN, StrStatus As String

    strstatus is a string and strings don't have a .value property

    i'll sent the db back with a couple of changes
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  10. #10
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Right the code now runs but only you can say if it works, anymore problems let me know
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  11. #11
    Join Date
    Sep 2002
    Location
    Ann Arbor, MI
    Posts
    47
    Originally posted by m.timoney
    Right the code now runs but only you can say if it works, anymore problems let me know
    That's got it! Thanks for yout time and help!

Posting Permissions

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