Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Location
    London, UK
    Posts
    1

    Unanswered: Problems with populating combo boxes.

    Hey all,

    Having some problems with a database I'm currently working on in Access 2007.

    Basically its a database for handling orders, customers and servers for dedicated server sales. I have created a table which contains all of the available IP's on a network, from which the IP selection combo boxes on the orders form are populated (through a query, coming to that in a minute).

    At the moment, this IP table has 3 fields, an autonumber just to avoid duplicates and keep them organised, the IP address field itself and a yes/no checkbox stating whether or not the IP is currently in use.

    I then have a query which finds only the IP's in use, by setting the IPInUse criteria to 0. This is then looked-up in the orders table to fill out the IP selection combo boxes. The orders form has 16 of these combo boxes, as an ordered server can have up to 16 IP addresses assigned.


    What I would like is to have the IP's I select immediately taken out of the drop down box, meaning if I drop down combo box 1 and the first value is 123.123.123.111, the first in the second (provided I have selected 123.123.123.111 in the first) is 123.123.123.112.

    It get more complex however. Each order has a status, active and inactive. If an order is set to inactive after the contract has expired, I'd like the IP's assigned to the order to re-enter the selection boxes on new orders.


    Am I asking too much of Access here? Or am I going about this in completely the wrong way?

    Advice would be great, thanks.

  2. #2
    Join Date
    Jul 2009
    Posts
    9

    Smile Drop down combo box

    First, you are not asking too much of Access here.
    Second, it has been my experience that auto numbers enable the creation of duplicate data not the avoidance.
    Third, once you use an IPs address you need to refresh the form or run the query that creates the drop down combo box. You will just need to set a trigger for this.

  3. #3
    Join Date
    Jul 2009
    Posts
    9

    Smile Drop down combo box

    First, you are not asking too much of Access here.
    Second, it has been my experience that auto numbers enable the creation of duplicate data not the avoidance.
    Third, once you use an IPs address you need to refresh the form or run the query that creates the drop down combo box. You will just need to set a trigger for this.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I won't comment the previous anwser in detail, it's not worth it. Let's just say that using autonumbers is an effective way to avoid duplicate rows (you can achieve the same result by applying a UNIQUE constraint on an indexed column), and that triggers do not exist in Access.

    There are two problems actually:

    1) Updating the second combo when the user changes the value of the first one is easy. You just need some code in the AfterUpdate event. If you want to update several combos, you can change their values in the AfterUpdate event of the first one:

    Code:
    Sub Combo1_AfterUpdate()
        Me.Combo2.Value = <some updated value>
        Me.Combo3.Value = <some updated value>
        ' ... etc.
    End Sub
    Note that you must explicitly change the values for all combos because even if some code exists in the AfterUpdate event of, say Combo2, this code will not be called when you programatically change the value of Combo2.

    A solution to this is to call the AfterUpdate event of each combo in the AfterUpdate code of the preceding one:

    Code:
    Sub Combo1_AfterUpdate()
        Me.Combo2.Value = <some updated value>
        Call Combo2_AfterUpdate
    End Sub
    
    Sub Combo2_AfterUpdate()
        Me.Combo3.Value = <some updated value>
        Call Combo3_AfterUpdate
    End Sub
    
    Sub Combo3_AfterUpdate() ' ... etc.
    The other problem is that you want to update the next combo with the next IP address. In this case a simple comparison will not work because IP addresses are stored as Text in the database. Such a comparison will yield 123.123.123.10 < 123.123.123.2, not what you are looking for.

    I've written a function that will format the IP adresses before comparing them, in this way: 123.123.123.10 becomes 123.123.123.010, 123.123.123.2 becomes 123.123.123.002 etc. Now the comparison will work the way we want it.

    Note that this function must be Public, it then must be in a standard module, not in the form's module.

    Here is the solution I came with, but there are several others, some of them probably better:

    1) I have a table [Tbl_IPAddesses] with a Text type column [IP_Address].
    2) I have a form [Form_IPCombos] with 3 combos: [Combo_1], [Combo_2], and [Combo_3].
    3) The RowSource property of each combo is:
    Code:
    SELECT IP_Address FROM Tbl_IPAddesses;
    4) I named the standard module [Mod_FormatIP].

    Code:
    '
    ' Class Module [Form_Form_IPCombos]
    '
    Option Compare Database
    Option Explicit
    
    Private Sub Combo_1_AfterUpdate()
    
        If Not IsNull(Me.Combo_1.Value) Then
            Me.Combo_2.Value = DLookup("IP_Address", "Tbl_IPAddesses", "FormatIPAddress(IP_Address) > '" & FormatIPAddress(Me.Combo_1.Value) & "'")
        Else
            Me.Combo_2.Value = Null
        End If
        Call Combo_2_AfterUpdate
        
    End Sub
    
    Private Sub Combo_2_AfterUpdate()
    
        If Not IsNull(Me.Combo_2.Value) Then
            Me.Combo_3.Value = DLookup("IP_Address", "Tbl_IPAddesses", "FormatIPAddress(IP_Address) > '" & FormatIPAddress(Me.Combo_2.Value) & "'")
        Else
            Me.Combo_3.Value = Null
        End If
        '
        ' Go on chaining Combos AfterUpdate events
        '
        ' Combo_3_AfterUpdate
    
    End Sub
    
    '
    ' Standard module [Mod_FormatIP]
    '
    Option Compare Database
    Option Explicit
    
    Public Function FormatIPAddress(IPAddress As Variant) As String
    
        Dim varSegments As Variant
        Dim strRetVal As String
        Dim i As Integer
        
        If IsNull(IPAddress) Then Exit Function
        varSegments = Split(IPAddress, ".")
        If IsArray(varSegments) Then
            If UBound(varSegments) = 3 Then
                For i = 0 To 3
                    If Len(strRetVal) Then strRetVal = strRetVal & "."
                    strRetVal = strRetVal & Format(varSegments(i), "000")
                Next i
            End If
        End If
        FormatIPAddress = strRetVal
            
    End Function
    Have a nice day!

Posting Permissions

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