Solved

How to create a form with mutliple drop down menus in access 2010.

Posted on 2014-04-24
25
266 Views
Last Modified: 2014-04-28
I am building a client access DB for myself so i could manage my IT Clients.

I need to create a single form with 2 initial dropdown boxes on it.

One for Client Name
One for Access Type (access type means how would you like to remote into the clients network) The options in the drop down for option type should be:

RDP
ESXI
Gateway Router

if RDP is selected it should display the following field values on the form:

RDP IP Address
Network Domain Name
Domain Administrator Username
Domain Administrator Password


If ESXI is selected it should display the following field values on the form:

ESXI Host Server 1 Internal IP Address
ESXI Host Server 1 External IP Address
ESXI Host Server 1 Username
ESXI Host Server 1 Password
ESXI Host Server 2 Internal IP Address
ESXI Host Server 2 External IP Address
ESXI Host Server 2 Username
ESXI Host Server 2 Password
ESXI Host Server 3 Internal IP Address
ESXI Host Server 3 External IP Address
ESXI Host Server 3 Username
ESXI Host Server 3 Password


If Gateway Router is selected it should display the following field values on the form:

WAN Gateway Router IP Address
WAN Gateway Router Username
WAN Gateway Router Password


So far i have created the excel spreadsheet with all the form field names on it along with some example records and imported it into Access but i need some help splitting the tables out from the main imported table to get the result that i need and I wondered if someone could guide me in the correct direction?

I have attached my sample DB which is in access 2010.

Thanks ahead of time for any guidance you could give me.
AccessDB.accdb
0
Comment
Question by:IT_Field_Technician
  • 12
  • 9
  • 4
25 Comments
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
EDIT:
My previous answer is wrong, I didn't notice what the question was.

Create a form with all the fields, then you can toggle their .Visible property based on which value is selected in the combobox.
0
 
LVL 84
Comment Utility
I personally think you'd be better off storing the information about the different connection types in separate tables. For example, RDP information would be stored in tblRDP, ESXI would be in tblESXI, and so on. You'd also have a table for Client info, and you'd store the PK of the Client in those tables when a new record is added to those connection type tables

Then create 3 separate forms based on each table - for example, frmRDP and frmESXI

Then create a form with your combo (the one where the user can choose RDP, ESXI, or Gateway Router. Add a Subform control named "sfmConnectType". base that form on your Client table.

When the user makes a selection in the combo, use code like this to show the correct subform:

Select Case Me.YourCombo.Column(0)
  Case "RDP"
    Me.sfmConnectType.SourceObject = "frmRDP"
  Case "ESXI"
    Me.sfmConnectType.SourceObject = "frmESXI"
  etc etc
End Select

If each of the forms includes the ClientName field (from the connect type tables), then set the Master/Child links for the subform to the names of the relevant fields in the tables - for example, if the ID field of your Client table is named "ClientName", and the Foreign Key fiel in tblRDP is "ClientName", then set the Master/Child link fields of the Subform control to those values.
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
I like this idea Scott, I have created the following 4 tables as requested:


tblclientinfo
tblRDP
tblESXI
tblGATEWAYROUTER

I have also pulled the fields and data out of the main table and into these new tables. But I am unsure how to "store the PK of the Client in those tables when a new record is added to those connection type tables"

I have attached the new DB with the changes suggested above. Could you please show me what you mean and send back the updated DB file?

Thanks for all your help!
AccessDB.accdb
0
 
LVL 84
Comment Utility
First: Does each client have multiple types of connections? Or do they all only have a single type? If they have a single type, then add a column to tblClientInfo named "ConnectionType", and set that to the appropriate value (like "RDP" or "ESXI")

Next:

1. Add a "ClientID" field to the RDP, ESXI and GatewayRouter tables.
2. Create forms based on those tables.
3. Create a form based on tblClientInfo (named something like "frmClients")
4. On frmClients, add a Subform control (named something like sfmConnectionType)

In the Current event of frmClients, add code like the above:

Select Case Me.ConnectionType
  Case "RDP"
    Me.sfmConnectType.SourceObject = "frmRDP"
  Case "ESXI"
    Me.sfmConnectType.SourceObject = "frmESXI"
  Case "GatewayRouter"
    Me.sfmConnectiontype.SourceObject = "frmGatewayRouter"
End Select
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
Yes all clients have multiple types of connections.
0
 
LVL 84
Comment Utility
Then I'd suggest a form with 3 subforms, and you can still use the techniques above - just be sure to add the ClientID field to all your connection tables, and then set the Master/Child links for each to insure they show the correct information.
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
Thanks Scott but i am new to access and i need some more detailed guidance on how to do these things.

Can you explain it a little more detailed?

Thank you!
0
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
I'd suggest toggling the .Visible or .Enabled property based on the combobox selection. In this sample I gave each group a three letter prefix, now we just loop thru all textbox controls on the form, disabling the relevant ones.
AccessDB.accdb
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
Hi Macroshadow, In your example there needs to first be a selection of the client name from a dropdown box. can you send an example with that updated?

Also i am not sure what you mean can you please explain in a little more detail what you did and how you did it and what i need to do next?

thanks for your help.
0
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
Is the effect in my sample what you are after? If it does what you want I will explain how it works.

btw, you don't need a combo box for the client name, you can use the navigation buttons on the bottom of the form to navigate between records.

Notes:
The attached file is for illustration purposes only, in real life you would:
1. organize the controls in a more orderly fashion
2. give the controls a meaningful name using a naming convention
AccessDB.accdb
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
I am unsure what you mean as every-time i make a selection in either drop-down i get this error:

error
I believe initially it would need to be a blank form with the 2 drop-downs on it and once you select the company name and the connection type there would be another action button called "Fetch" and it would display the requested connection in a subform i guess?

Make sense?

Another thing to note: is that there needs to be an addition to the connection type drop-down called "all" - if this is selected it will show all connection type values but if a specific connection type is selected it would only show that specific connection types values.
0
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
Sorry about that, it was caused because the labels were in a foreign language.

This should do it.
AccessDB.accdb
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:IT_Field_Technician
Comment Utility
No worries but i am still getting the same error.
0
 
LVL 26

Accepted Solution

by:
MacroShadow earned 500 total points
Comment Utility
Tested and working in English version of Access.
AccessDB.accdb
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
Yes its working for me as well this time - thanks!

How do I get it so that all the other connection type fields are not just greyed out but are invisible when a specific connection type is made?
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
another question is how did you do this with just one table? can you explain?
0
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
Sure.

When the fields are disabled (meaning you cannot change them) they are grey.

If you want them to disappear instead change both occurrences of ctl.Enabled to ctl.Visible.

As for the explanation:
This is the code you're interested in, I added comments in the code to help you understand what's going on, if you need further clarification, just ask:
Option Compare Database ' Deafault but not necessary in most cases
Option Explicit         ' Force variable dimensioning - HIGHLY RECOMMENDED!!!

' This event will fire whenever the access method combobox value is changed
Private Sub cboAccessMethod_Change()

    ' In these two lines we dimension the variables, in other words we create storage space
    ' on the computer for the data being procssed
    
    ' This variable holds the preface to the access method
    ' we will use this preface to determine which textboxes to show
    Dim strControlNamePreface As String
    ' This variable holds the current control as we loop thru all controls on the form
    Dim ctl As Control
    
    ' Populate the strControlNamePreface based on the selection in the cboAccessMethod combobox
    Select Case Me.cboAccessMethod
        Case Is = "RDP"
            strControlNamePreface = "RDP"
        Case Is = "ESXI"
            strControlNamePreface = "ESX"
        Case Is = "Gateway Router"
            strControlNamePreface = "WAN"
        Case Is = "ALL"
            strControlNamePreface = "ALL"
    End Select

    ' Loop thru all controls (any element on a form is a control) on the form
    For Each ctl In Me.Controls
        ' Preform the required action only on textboxes
        If ctl.ControlType = acTextBox Then
            ' The form also houses controls we want to show regardless
            ' of the selected access method, so we set the tab index of
            ' those controls higher than 20
            ' Now preform the required action only on textboxes with a
            ' tab index lower than 20
            If ctl.TabIndex < 20 Then
                ' If 'ALL' was selected from the access method combobox
                If strControlNamePreface = "ALL" Then
                    ' Set all textboxe's Enabled (or Visible) property to true
                    ctl.Enabled = True
                ' If any other access method is selected
                Else
                    ' Toggle all textboxe's Enabled (or Visible) property
                    ' if the 3 leftmost characters = the value held in strControlNamePreface
                    ' then Enable (or show) the control, othewise disable (or hide) the control
                    ctl.Enabled = Left(ctl.Name, 3) = strControlNamePreface
                End If
            End If
        End If
    ' Move to next control
    Next
    
End Sub

Open in new window


This code filters the form so it shows the value selected in the combobox, it is fairly advanced so I won't bother you with the explanation:
Private Sub Client_Name_AfterUpdate()
    If IsNull(Me![Client Name]) Then Exit Sub

    With Me.RecordsetClone
      .FindFirst "[Client Name] = '" & Me![Client Name] & "'"
      If Not .NoMatch Then
         If Me.Dirty Then Me.Dirty = False
         Me.Bookmark = .Bookmark
      Else
         ' put your not found code here, but you really shouldn't need it
      End If
    End With
End Sub

Open in new window

0
 

Author Comment

by:IT_Field_Technician
Comment Utility
Understood Macroshadow and thanks - I will play with the invisible aspect in a bit.

I do have one question:

you were able to do this with one table but is this the 'Proper" way it should be done?

Isn't it suppose to be broken up into separate tables with primary keys assigned and such?

I ask because i wish to build my access db from here and keep adding features and fields as necessary.

I realize you did exactly what i asked for but i want to be sure that i am starting out doing it the proper way so I wont have to totally redesign it in the future if i want to add new functions/features/fields...

Thanks so much for your help and taking the time to explain!
0
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
I don't see a problem using one table.

You may want to ask a new question so you can get other expert's attention too.
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
Ok thanks!
0
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
My pleasure.
0
 
LVL 84
Comment Utility
I think the different connection types are distinct enough to warrant their own tables. So you'd have ESXI tables, and RDP tables, etc etc.

Or you could go one step further, and have a "Connection" table, and an "Attribute" table. That sort of setup could get very complex, however, and doesn't really make much sense if you're only dealing with a small set of connection "types".
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
Hi Scott, Thanks for responding, I was wondering could you please continue where you and i left off as i am concerned about keeping everything in one table as macroshadow kindly showed me.

I would like to start out properly and i feel its better to have the tables split out the way we were going about it keeping in mind the possibility of future development of the DB.

I have build one one access 2003 Db in the past and that was quite the struggle for me especially when it came to database structure and preshared keys so i need someone who is strong in this area to guide me in the correct direction.
0
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
Being as your initial question was answered it's only fair to ask a new question.
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
Understood and will do.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now