Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Combo box Open Data entry form

    Hi all I need to create but a combo box on the switchboard that will open of Forms. So when the user clicks on the drop down and sees the the name of the form they are looking like (TanfParticipants_frm), the form pops up and the user just starts entering the data. so instead of a command button how can I make it so that its a Combo Box instead or a list box?

    Can anyone help pls

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    A Switchboard is simply a specialized form, so you can add a combobox to it like you would a form, I would think. This routine will do what you want:

    Place a combobox on your form
    When the Wizard pops up, hit Cancel
    Goto the combobox Property Sheet and make sure under Row Source Type it says Table/Query
    In the Row Source property paste this in

    Code:
    SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
    (MSysObjects.Type)=-32768 ORDER BY MSysObjects.Name;
    Now, in the combobox’s AfterUpdate event, you can do one of two things, depending on your particular situation.

    If none of your forms need to be opened in Datasheet View, you can simply use:

    Code:
    Private Sub FormOpenCombo_AfterUpdate()
      stDocName = Me.FormOpenCombo
      DoCmd.OpenForm stDocName
    End Sub
    If all of your forms need to opened in Datasheet View, you can use:

    Code:
    Private Sub FormOpenCombo_AfterUpdate()
      stDocName = Me.FormOpenCombo
      DoCmd.OpenForm stDocName, acFormDS
    End Sub
    If you have a mixed bag, some forms in Single View or Continuous View and some in Datasheet View, it becomes a little more complicated and you'll have to modify the code on a form by form basis.

    Having said this about Switchboards being specialized forms, Access can be picky when you try to modify them outside of the box, as it were. I would strongly recomend substituting a regular form for your Switchboard.
    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
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Well the Switchboard is actually a home made one, its just a form with command buttons that lead to the acutual data entry forms. I only have one subform thats in DAtasheet mode, will that matter?? because the Main form its attached to is a single form. btw thank you so much for your help

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As I indicated, a 'home grown' switchboard, like yours, is actually preferable! It just allows so much more flexibility than the standard Access Switchboards. If all the forms you'll be calling from the combobox are Single View forms, then the code

    Code:
    Private Sub FormOpenCombo_AfterUpdate()
      stDocName = Me.FormOpenCombo
      DoCmd.OpenForm stDocName
    End Sub
    will work fine, even if one or more of those forms have datasheet subforms.

    As for the help, you're certainly welcome! That's why we're all here!
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Thank hou Missingling will it be the same if its another database??

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    If you're asking if you can use identical code in another Access database the answer is yes. You'd simply need to replace the combobox name FormOpenCombo with the actual name of the combobox.

    If you mean something else you'll have to given an explanation.
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    ok so the Form that I am trying to open is the Parent_frm, so would I put that parent form in where it say OpenForm. Where in the code do I tell it which form to open since there is about 5 of them??


    Private Sub Combo46_AfterUpdate()
    stDocName = Me.FormOpenCombo
    DoCmd.Parent_frm stDocName
    End Sub

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Assuming that the RowSource Property for Combo46 is

    Code:
    SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND (MSysObjects.Type)=-32768 ORDER BY MSysObjects.Name;
    as instructed, you don't need to do anything to "tell" Access which form to open except to select a form from the combobox. The selection you make from the combobox "tells it" which form to open using this code:

    Code:
    Private Sub Combo46_AfterUpdate()
      stDocName = Me.Combo46
      DoCmd.OpenForm stDocName
    End Sub
    Do not change anything in either of these code examples!
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Ok I did exactly as you said to do and didnt change anything and this is the message I got.
    Left$ is not a recognized built-in function name

  10. #10
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Never Mind Missingling I got it

    this is what I used

    Private Sub Combo62_AfterUpdate()
    DoCmd.OpenForm Me!Combo62
    End Sub



    I made the combo box's list the form names and made the bound column the form name column . Then AfterUpdate event procedure can be like:


    thank you for your help and patience
    DoCmd.OpenForm Me!cbxForms

  11. #11
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad you got it working!
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

Posting Permissions

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