Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Building a list from a list box selection

Discussion in 'Information Technology' started by thusnani@gmail.com, Jul 28, 2006.

  1. thusnani@gmail.com

    thusnani@gmail.com
    Expand Collapse
    Guest

    Hopefully someone can help me out with this situation. Im working with
    a form that inputs the hours an employee works on certain projects
    and general admin categories per week. Right now i have two list boxes
    that display the projects and general admin categories, each of which
    have an extended multi select. I want to make some sort of button
    which generates just a list of projects and general admin categories
    selected with a corresponding
    'text boxes' beside the area. I dont want to make some subform pop up
    in my form do to other reasonings so that is out of the question. I
    already know how to retrieve values from text boxes and combo boxes
    (i.e hours, projects) that have been entered/selected by pressing a
    process button which will input the data into the proper tables.
    Im just looking to know how to generate or build these 'text
    boxes'.

    Here is a the exact situation of what i want the form to do:
    In the report someone is to choose a date which is going to be a
    week ending on the friday and there name. Then they choose the
    project and general admin categories that they were involved in that
    week. Select them and generate some sort of list below. Then the
    person is suppose to fill in the appropiate hours in some sort of
    box beside the corresponding list of projects and general admin
    categories. These hours will be totalled on the bottom and if they
    equal 35 then the process button will allow the data to be inputted
    in the corresponding tables.
     
  2. Loading...

    Similar Threads Forum Date
    Sikh Some Bridge Building Sikh Sikhi Sikhism May 10, 2016
    Interfaith Building A Sikh Paradigm For Interfaith Work: Part 2 Interfaith Dialogues Apr 29, 2016
    USA Houston Sikh woman’s rights possibly violated by ban on Kirpans in IRS building Breaking News Nov 22, 2013
    India Cash crunch: Punjab to mortgage buildings Breaking News Aug 25, 2013
    India Deadly Building Collapse near Mumbai Breaking News Apr 5, 2013

  3. strive4peace

    strive4peace
    Expand Collapse
    Guest

    For this example, I will assume the following data structure:

    *Projects*
    ProjectID, autonumber
    Project, text
    StartDate, date
    EndDate, date

    *Employees*
    EmpID, autonumber
    Lastname, text
    Firstname, text
    HireDate, date
    TermDate, date

    *Cats*
    CatID, autonumber
    Category, text

    *Periods*
    PeriodID, autonumber
    PerStart, date
    PerEnd, date

    *TimeLog*
    LogID, autonumber
    EmpID, long integer
    PeriodID, long integer
    ProjectID, long integer
    CatID, long integer
    Hours, currency*

    * even though hours is not money, using the currency data
    type is more accurate than single or double if no more than
    4 decimal places are needed. Use the FORMAT property to
    display the numbers without a currency symbol.

    In the TimeLog table, make a unique index on the combination
    od EmpID, PeriodID, ProjectID, and CatID


    *** to make a unique index in the table on a multi-field
    combination ***

    From the table design, Turn on the Indexes window (from the
    menu: View, Indexes)

    click on the first field in the table design and set the
    Index property to
    Yes (duplicates ok)

    that will add a line to the Indexes window

    In the row just below the index you just made, in the 2nd
    column of the indexes window, click in the fieldname column
    and choose the 2nd fieldname
    In the row below

    If you have another field to add, click on the fieldname
    column in the next row down, then on the drop-down arrow,
    and choose the 3rd fieldname

    Now, Click on top row of that Index, the first fieldname
    (row) -- set
    Unique --> Yes


    Having a unique index will disallow duplicate combinations
    into the table (this will be important for the APPEND
    queries that create records to work right)

    ~~~

    make a form with the following controls:

    ~~~
    listbox:

    Name --> PickProjectIDs

    RowSource -->
    SELECT ProjectID, Project
    FROM Projects
    WHERE (EndDate >= (Date-7))
    OR (EndDate Is Null)
    ORDER BY Project;

    BoundColumn --> 1
    ColumnCount --> 2
    ColumnWidths --> 0;2
    ListWidth --> 2

    Multi-select --> Extended (or Simple)

    ~~~
    listbox:

    Name --> PickCatIDs

    RowSource -->
    SELECT CatID, Category
    FROM Cats
    ORDER BY Category;

    BoundColumn --> 1
    ColumnCount --> 2
    ColumnWidths --> 0;2
    ListWidth --> 2

    Multi-select --> Extended (or Simple)

    ~~~
    combobox or listbox:

    Name --> PickEmpID

    RowSource -->
    SELECT EmpID, Lastname, Firstname
    FROM Employees
    WHERE (TermDate >= (Date-7))
    OR (TermDate Is Null)
    ORDER BY Lastname, Firstname;

    BoundColumn --> 1
    ColumnCount --> 3
    ColumnWidths --> 0;1.25;1.25
    ListWidth --> 2.5

    ~~~
    combobox or listbox:

    Name --> PickPeriodID

    RowSource -->
    SELECT PeriodID, PerStart, PerEnd
    FROM Periods
    ORDER BY PerStart;

    BoundColumn --> 1
    ColumnCount --> 3
    ColumnWidths --> 0;1.25;1.25
    ListWidth --> 2.5

    ~~~

    command button:

    Name --> ResetData
    Caption --> Reset Data

    Onclick -->
    [Event Procedure]

    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ResetData_Click
    ClearList Me.PickProjectIDs
    ClearList Me.PickCatIDs
    Me.PickEmpID = null
    Me.PickPeriodID = null
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    ~~~


    command button:

    Name --> MakeLogRecords
    Caption --> Make Time Log Records

    Onclick -->
    [Event Procedure]

    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ MakeLogRecords_Click

    On Error GoTo Proc_Err

    If Me.PickProjectIDs.ItemsSelected.Count = 0 Then
    MsgBox "No projects selected" _
    , , "Missing Data"

    Exit Sub
    End If

    If Me.PickCatIDs.ItemsSelected.Count = 0 Then
    MsgBox "No categories selected" _
    , , "Missing Data"

    Exit Sub
    End If

    If IsNull(Me.PickEmpID) Then
    MsgBox "No employee selected" _
    , , "Missing Data"

    Exit Sub
    End If

    If IsNull(Me.PickPeriodID ) Then
    MsgBox "No period selected" _
    , , "Missing Data"

    Exit Sub
    End If

    dim s as string
    Dim varProject As Variant
    Dim varCat As Variant

    For Each varProject In _
    me.PickProjectIDs.ItemsSelected

    For Each varCat In _
    me.PickCatIDs.ItemsSelected

    s = "INSERT INTO TimeLog " _
    & " (ProjectID, CatID, _
    & " EmpID, PeriodID) " _
    & " SELECT " _
    & me.PickProjectIDs.ItemData(varProject) _
    & ", " _
    & me.PickCatIDs.ItemData(varCat ) _
    & ", " _
    & Me.EmpID & ", " _
    & Me.PeriodID & ";"
    debug.print s
    currentdb.execute s
    next varCat
    next varProject

    currentdb.tabledefs,refresh
    DoEvents

    me.subformcontrolname.form.requery


    Proc_Exit:

    Exit Sub

    Proc_Err:
    MsgBox Err.Description, , _
    "ERROR " & Err.Number & " MakeLogRecords"

    'press F8 to step through code and debug
    'remove next line after debugged
    Stop: Resume
    Resume Proc_Exit
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


    also put this function in the code behind the form:


    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ClearList

    Private Function ClearList(pControlname As String)
    Dim varItem As Variant
    For Each varItem In Me(pControlname).ItemsSelected
    Me(pControlname).Selected(varItem) = False
    Next varItem
    End Function
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~





    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    thusnani@gmail.com wrote:
    > Hopefully someone can help me out with this situation. Im working with
    > a form that inputs the hours an employee works on certain projects
    > and general admin categories per week. Right now i have two list boxes
    > that display the projects and general admin categories, each of which
    > have an extended multi select. I want to make some sort of button
    > which generates just a list of projects and general admin categories
    > selected with a corresponding
    > 'text boxes' beside the area. I dont want to make some subform pop up
    > in my form do to other reasonings so that is out of the question. I
    > already know how to retrieve values from text boxes and combo boxes
    > (i.e hours, projects) that have been entered/selected by pressing a
    > process button which will input the data into the proper tables.
    > Im just looking to know how to generate or build these 'text
    > boxes'.
    >
    > Here is a the exact situation of what i want the form to do:
    > In the report someone is to choose a date which is going to be a
    > week ending on the friday and there name. Then they choose the
    > project and general admin categories that they were involved in that
    > week. Select them and generate some sort of list below. Then the
    > person is suppose to fill in the appropiate hours in some sort of
    > box beside the corresponding list of projects and general admin
    > categories. These hours will be totalled on the bottom and if they
    > equal 35 then the process button will allow the data to be inputted
    > in the corresponding tables.
    >
     

Share This Page