Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2012
    Posts
    9

    Unanswered: Help needed - how to link a combo box to a subform

    Hi,

    Please can someone help?! I am trying to link a combobox in a main form to a subform. So, when a particular option in the combobox is selected the relevant subform will open within the main form.

    I have never written code before, but appropriated the code pasted below, which I have linked to the combobox in Properties > Event > On click > [Event Procedure]. However it won't work! Please can you take a look at it and see where there could be an error?

    Many thanks! :-)

    ________________

    Option Compare Database

    Private Sub Combo93_Click()

    End Sub

    Sub ShowSubform()

    'Save unsaved changes to currently open subform
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    'Display appropriate subform based on ProjectType chosen
    If ProjectType = "Audit" Then
    SubFormAudit.Visible = True
    SubFormServEvalPN.Visible = False
    SubFormServEvalQIS.Visible = False

    ElseIf ProjectType = "Fac UK" Then
    SubFormAudit.Visible = False
    SubFormServEvalPN.Visible = False
    SubFormServEvalQIS.Visible = True

    ElseIf ProjectType = "Obs Overseas" Then
    SubFormAudit.Visible = False
    SubFormServEvalPN.Visible = True
    SubFormServEvalQIS.Visible = False

    Else
    SubFormServEvalQIS.Visible = False
    SubFormAudit.Visible = False
    SubFormServEvalPN.Visible = False

    End If

    End Sub

    Private Sub cmdClose_Click()

    'Close form
    DoCmd.Close

    End Sub

    Private Sub Form_Current()

    'Call subroutine to display appropriate subform based on MediaType
    Call ShowSubform

    End Sub

    Private Sub ProjectType_AfterUpdate()

    'Call subroutine to display appropriate subform based on MediaType
    Call ShowSubform

    End Sub
    ________________

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm moving your thread (question) from the Database Design forum to the Microsoft Access forum. It will probably get better answers there because your question is very product specific. If I've guessed wrong, please comment and one of the moderators will move your post to a more appropriate forum.

    I'm pretty sure that your code is Ok, but that you've attached it to the wrong event. I think that the On Change event would be more suitable for this code than the On Click event is. I assume that you want the subform to appear when the value changes instead of when the user clicks on the control that contains the subform text.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2012
    Posts
    9
    Thanks Pat.

    Yes, you guessed right :-) Does that mean I need to change anything that say "click" to "change"?

    I was wondering whether I have to also created a macro to make it work? As at the moment it either comes up with a debug message or asks for a macro, depending on how I fiddle with it!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what is ProjectType
    Where is it set
    what isn't working as you'd expect
    where is showsubform invoked/called

    if ProjectType is a combo box then its default property is the value thats been selected
    ..but you'd need to prove that

    I'd suggest you try to debug the code

    Ideally you should develop some debugging skills, the Access IDE has a fantastci debugger. but if you are short on time

    msgbox('ProjectType is:' & ProjectType)
    will tell you what it is.

    Code:
    Option Explcit 'alwasy do this forces variable declaration can stop typo's and obscure problems with undeclared variables
    Option Compare Database
    
    Sub ShowSubform()
    'Save unsaved changes to currently open subform
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    'set all forms hidden
    SubFormServEvalQIS.Visible = False
    SubFormAudit.Visible = False
    SubFormServEvalPN.Visible = False
    'Display appropriate subform based on ProjectType chosen
    If ProjectType = "Audit" Then
      SubFormAudit.Visible = True
    ElseIf ProjectType = "Fac UK" Then
      SubFormServEvalQIS.Visible = True
    ElseIf ProjectType = "Obs Overseas" Then
      SubFormServEvalPN.Visible = True
    'Else 'DO NOTHING
    End If
    End Sub
    
    Private Sub Form_Current()
    'Call subroutine to display appropriate subform based on MediaType
    Call ShowSubform
    End Sub
    
    Private Sub ProjectType_AfterUpdate()
    'personally I prefer to use on click event for combo boxes
    'Call subroutine to display appropriate subform based on MediaType
    Call ShowSubform
    end sub
    if ProjectType is not a combo/listbox then you'd need to call showsubform in the combo
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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