Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Lewes, East Sussex, Sussex, United Kingdom
    Thanked 0 Times in 0 Posts

    Combo boxes & columns (A 2002 SP 3 (A97))

    Fellow loungers

    I hope someone can help wit hsome advice on an area that has been annoying/puzzling me for a while.

    On various forms I have combo boxes which have many hidden columns. In the AfterUpdate event code for these combos I need to refer to various columns. Sometimes there can be up to 15 columns, and it is very difficult to remember exactly which column number represents which data. If the column order of the underlying query is changed it makes it doubly difficult to follow.

    The best I have managed so far is to include comments like the following. This helps but is not ideal.
    'the column order of the combo box is as follows :
    '0 - charge_code
    '1 - charge_description
    '2 - include_on_invoice
    '3 - manual_entry
    '4 - basis_points
    '5 - invoice_based
    '6 - holding_based
    '7 - fund_based
    '8 - client_based
    '9 - pro_rata_charges
    '10 - allow_adjustments
    '11 - tiered_charges_allowed
    '12 - time_weighted
    '13 - pool_holdings
    '14 - location_id

    Is there an easier or better way to implement this ?

    Usual thanks for your expert help


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Combo boxes & columns (A 2002 SP 3 (A97))

    I don't see why you would want to change the column order in a query that acts as record source for a combo box in which most columns are hidden anyway. If I need the query for other purposes in which the column order is important, I'd create a copy of the query.

    You could use the following function (to be placed in a standard module):

    Public Function GetCol(cbo As ComboBox, strCol As String) As Integer
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim i As Integer

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(cbo.RowSource, dbOpenDynaset)
    GetCol = -1
    For i = 0 To rst.Fields.Count - 1
    If rst.Fields(i).Name = strCol Then
    GetCol = i
    Exit For
    End If
    Next i

    On Error Resume Next
    Set rst = Nothing
    Set dbs = Nothing
    Exit Function

    GetCol = -2
    Resume ExitHandler
    End Function

    This function will return the zero-based column number of the column whose field name is strCol in the combo box cbo. If there is no column with the specified name, the function returns -1, and if an error occurs, it returns -2.
    You can use it like this to fill the text box txtFundBased with the value of the column corresponding to Fund_Based in the combo box cboCharge:

    Me.txtFundBased = GetCol(Me.cboCharge, "Fund_Based")

    1) Since the code opens and closes a recordset, and loops through the fields of the recordset, using this function involves a lot of overhead compared with referring to the column index directly.
    2) The code requires a reference to the Microsoft DAO 3.6 Object Library to be set (3.53 for Access 97)

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Lewes, East Sussex, Sussex, United Kingdom
    Thanked 0 Times in 0 Posts

    Re: Combo boxes & columns (A 2002 SP 3 (A97))


    Sorry for the delay in replying.

    This is an application which has several developers wokring on it at the same time - while I agree that column orders should not be changed, it does happen occasionally.

    Your excellent solution will provide a way to avoid these problems.

    Thanks for the help


Posting Permissions

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