Building a list from a list box selection

[thusnani]

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.

 

[strive4peace]

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

*