Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2012
    Posts
    6

    Unanswered: Need help with VBA.

    Hi,

    I need help with some VBA coding, I'm fairly new to VBA so here it goes:


    I have a form called FileNumber

    I need help with two things:

    1) There is control called FileExtension in the form of a combo box.
    When the user selects an option for the first time, I want a message box to pop up with a message.
    If the User clicks yes, I want the field to be disabled but re-enabled if the user double clicks the field.
    Once the form is saved and reopened, I want the control to stay disabled.

    I have:
    i) created the message box
    ii) disabled the control if the user clicks yes

    I need the control to stay disabled if the form is reopened unless it is double clicked.

    I currently have this code running:

    Code:
    Private Sub FileExtension_AfterUpdate()
    '''This will ask the user for a check if the data entered is correct
    '''and disable the file extension field
    
    Dim intFileNumber As Integer
    Dim strFileExt As String
    
    intFileNumber = Me.FIleNumber.Value
    strFileExt = Me.FileExtension.Value
    
    If MsgBox("Please check if the File Number and Extension are correct." & intFileNumber & strFileExt & "Please confirm the File Number and Extension", _
                vbYesNo, "File Number Confirmation") = vbYes Then
                
            Me.FileExtension.Enabled = False
            
     Else
     
     Me.FileExtension.Enabled = True
     End If
     Me.Department = Me.FileExtension.Column(2)
    End Sub
    2) I have a button which opens up a form depending on what extension is selected. This is why it is vital for the file extension to remain unchanged.

    What I need the button to do:
    1) Open a different form depending on what extension is picked
    2) Open the form where [FileNumber] = [FileNumber] if a related record exists, if it doesn't then I need to create a new record.

    I have been able to do part 1 and a bit of part two:

    Code:
    Private Sub Command87_Click()
    '''This button opens up the relevant time recording sheet
    '''depending on what department is picked.
    
    Dim strwhere As String
    strwhere = "[filenumber]=" & Me!FIleNumber
    
    Select Case Department
    
        Case "Civil"
        DoCmd.OpenForm "frmcivil"
    
        Case "Crime"
        DoCmd.OpenForm "frmcrime"
    
        Case "Immigration"
        DoCmd.OpenForm "frmimmigration"
    
        Case "Family"
        DoCmd.OpenForm "frmfamily"
        
        End Select
    
    End Sub
    I would be grateful if someone can guide me or help me.

    Please feel free to ask for any further information.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by raka View Post
    Hi,

    I need help with two things:

    1) There is control called FileExtension in the form of a combo box.
    When the user selects an option for the first time, I want a message box to pop up with a message.
    If the User clicks yes, I want the field to be disabled but re-enabled if the user double clicks the field.
    Once the form is saved and reopened, I want the control to stay disabled.
    Over 100 members have viewed your thread and no one has responded, probably because you're asking two unrelated questions, each of which involves quite a bit of code.

    I'll address the first question here, and would suggest that you start a separate thread for the second question, about opening the appropriate Form, dependent on the Value in the Combobox named FileExtension .

    First off, you obviously haven't done much testing, of your current code, or you would have found out that you cannot Enable/Disable a Control while it has Focus, as you're trying to do. For this reason I'd recommend that you use the Locked Property, instead. It will keep users from utilizing the Combobox, when appropriate, and you can Lock/Unlock a Control while it has Focus.

    Secondly, in order for the Combobox to retain its Value, when you move between Records or when you close and re-open your Form, it must be Bound to a Field in your underlying Table.

    Lastly, in order for the Combobox to remain either usable or non-usable, after you Close and then Re-open your Form, you have to have a Control on your Form that is Bound to a Field in your underlying Table; the purpose of this Field is to indicate whether the Combobox should be usable or non-usable. Also, you cannot Double-Click a Control that is either Locked or Disabled, so, in order to allow the Locked Combobox to be used, the users will have to un-tick this new Field, which will be a Checkbox.

    So, you need to
    • Bind your FileExtension to a Field in your underlying Table (create a new Field, if need be)
    • Create, in your Table, a Yes/No Field
    • Enter Disable_CBO as the Name for this Field
    • Create a Checkbox on your Form
    • Set the Control Source for the Checkbox to the Disable_CBO Field
    Now, place the following Subs into your Form's Code Module.
    Code:
    Private Sub FileExtension_AfterUpdate()
     
    Dim intFileNumber As Integer
    Dim strFileExt As String
    
    intFileNumber = Me.FileNumber.Value
    strFileExt = Me.FileExtension.Value
    
    If MsgBox("Please check if the File Number and Extension are correct." & intFileNumber & strFileExt & "Please confirm the File Number and Extension", _
                vbYesNo, "File Number Confirmation") = vbYes Then
                
      Me.FileExtension.Locked = True
      Me.Disable_CBO = -1
     Else
      Me.FileExtension.Locked = False
      Me.Disable_CBO = 0
     End If
     
     Me.Department = Me.FileExtension.Column(2)
     
     End Sub
    
    
    Private Sub Form_Current()
    If Me.Disable_CBO = -1 Then
      FileExtension.Locked = True
     Else
      FileExtension.Locked = False
     End If
    End Sub
    
    
    Private Sub Disable_CBO_AfterUpdate()
     If Me.Disable_CBO = -1 Then
      FileExtension.Locked = True
     Else
      FileExtension.Locked = False
     End If
    End Sub


    When the user makes a Selection from the FileExtension Combobox, and answers 'Yes' to the Messagbox that pops up, the Disable_CBO Checkbox will automatically be ticked.

    Thereafter, in order for the user to utilize the Combobox, he or she will have to un-tick the Checkbox. Once a new Selection has been made and affirmed, it will once again be re-ticked.

    Note that I've included your bits of code
    Code:
    Dim intFileNumber As Integer
    Dim strFileExt As String
    ...................................
    intFileNumber = Me.FileNumber.Value
    strFileExt = Me.FileExtension.Value
    ..................................
     Me.Department = Me.FileExtension.Column(2)

    but have no way to verify whether or not it is valid, not having your file in front of me.

    Linq ;0)>
    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
    Jul 2012
    Posts
    6
    Missinglinq,

    Firstly, thank-you for the help. I really appreciate it

    Over 100 members have viewed your thread and no one has responded, probably because you're asking two unrelated questions, each of which involves quite a bit of code.
    I am fairly new to posting to the forum, I did not know that. I thought it would be better to put it in one rather than bugging people again. But thank-you, I now post separately in the future if I need help again!
    As for the second question, I am trying to build on what I currently have and see if I can get the results myself! I have had a few more ideas.


    First off, you obviously haven't done much testing, of your current code, or you would have found out that you cannot Enable/Disable a Control while it has Focus, as you're trying to do. For this reason I'd recommend that you use the Locked Property, instead. It will keep users from utilizing the Combobox, when appropriate, and you can Lock/Unlock a Control while it has Focus.
    I just thought that my code was just bad, this is my first VBA project and I am self teaching myself VBA so please do bear with me.

    but have no way to verify whether or not it is valid, not having your file in front of me.
    The solution worked great. However I am having a bit of trouble with concatenation, I should be getting a number and a string extension. However I am getting a random number. I should be getting, for the records I am working with, 1-CR or 1-IM depending on the extension chosen. However, I am getting numbers such as 11,13,14

    Code:
     
    If MsgBox("Please check if the File Number and Extension are correct." & intFileNumber & strFileExt & "Please confirm the File Number and Extension", _
                vbYesNo, "File Number Confirmation") = vbYes Then
    I have attached the database to this post. The form in question is frmfilenumber.

    Once again, I would like to thank-you for helping me.
    Attached Files Attached Files

Posting Permissions

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