continuous form & query HELP!!!

[Guest]

I have a continuous form based on a query that lists 99 different projects.
There is one owner per project, and an owner can be listed on many projects.
This is basic access, but I can't figure it out

Project ID Project_Owner
1 Paula
2 Paula
3 Vickie
4 Paula
5 Bob
....

1) Instead of free-form text, I would like to have a drop-down to list the
project owners. When I attempt to put in a combo box, it pulls up 99 project
owners with duplicates...and when I switch it to be unique values only, it
locks the drop-down box and shows up the same in every continuous record.

How do I create a drop-down that pulls unique records from Project_Owner and
then assigns it to the Project_ID?

2) After I have the above problem solved, I would like to filter on Owner's
names. How do I create a drop-down in the header that will filter by project
owner name?

THANK YOU SO MUCH!!!

 

[Guest]

You need to create a separate ProjectOwners table which has just one row per
owner. Give it fields ProjectOwnerID (autonumber primary key), FirstName,
LastName (the last two text) plus any other fields you want to record
attributes of each owner. Note that the names are not used as the primary
key as names can be duplicated, and the name is split into first and last;
it’s a lot easier to tack two values together than to split one into two.
Create a relationship between the new table and your current one on the
ProjectOwnerID fields and enforce referential integrity in the Edit
Relationship dialogue. This will prevent any rows being added to the
existing table with a ProjectOwnerID value which doesn't exist in the
Projectowners table.

In your existing table create a new long integer number data type field
called ProjectOwnerID as a foreign key. On the form bind a combo box to this
field and set its RowSource property to:

SELECT ProjectOwnerID, (FirstName + " ") & LastName AS FullName
FROM ProjectOwners
ORDER BY LastName, FirstName;

Set the BoundColumn property of the combo box to 1, its ColumnCount property
to 2 and its ColumnWidths property to 0cm;8cm (or rough equivalent in inches,
but make the first dimension zero as this hides the first column so you just
see the names).

When you select a row from the combo box's list the concatenated first and
last name will show in the control, but its value will be the hidden
ProjectOwnerID for the selected owner. Once you are satisfied that its
working and that you have the correct owners in each record you can delete
the redundant Project_Owner field from the table.

To filter to an owner add copy and paste the same combo box into the header.
Delete its ControlSource and rename to something like cboFindOwner. In its
AfterUpdate event procedure put the following code:

Dim strFilter As String

strFilter = "ProjectOwnerID = " & Nz(Me.cboFindOwner,0)
Me.Filter = strFilter
Me.FilterOn = True

To clear the filter you can use the built in button on the toolbar or you
can add a 'Show All' button to your form with the following code in its Click
event procedure:

Me.FilterOn = False

A refinement you might want to add to keep the two combo boxes in sync if
you navigate from record to record via the navigation buttons rather than the
combo box is to put the following code in the form's Current event procedure:

Me.cboFindOwner = Me.ProjectOwnerID

Ken Sheridan
Stafford, England

 

[Guest]

You're awesome...thanks!!!!!

You need to create a separate ProjectOwners table which has just one row per
owner. Give it fields ProjectOwnerID (autonumber primary key), FirstName,
LastName (the last two text) plus any other fields you want to record
attributes of each owner. Note that the names are not used as the primary
key as names can be duplicated, and the name is split into first and last;
it’s a lot easier to tack two values together than to split one into two.
Create a relationship between the new table and your current one on the
ProjectOwnerID fields and enforce referential integrity in the Edit
Relationship dialogue. This will prevent any rows being added to the
existing table with a ProjectOwnerID value which doesn't exist in the
Projectowners table.

In your existing table create a new long integer number data type field
called ProjectOwnerID as a foreign key. On the form bind a combo box to this
field and set its RowSource property to:

SELECT ProjectOwnerID, (FirstName + " ") & LastName AS FullName
FROM ProjectOwners
ORDER BY LastName, FirstName;

Set the BoundColumn property of the combo box to 1, its ColumnCount property
to 2 and its ColumnWidths property to 0cm;8cm (or rough equivalent in inches,
but make the first dimension zero as this hides the first column so you just
see the names).

When you select a row from the combo box's list the concatenated first and
last name will show in the control, but its value will be the hidden
ProjectOwnerID for the selected owner. Once you are satisfied that its
working and that you have the correct owners in each record you can delete
the redundant Project_Owner field from the table.

To filter to an owner add copy and paste the same combo box into the header.
Delete its ControlSource and rename to something like cboFindOwner. In its
AfterUpdate event procedure put the following code:

Dim strFilter As String

strFilter = "ProjectOwnerID = " & Nz(Me.cboFindOwner,0)
Me.Filter = strFilter
Me.FilterOn = True

To clear the filter you can use the built in button on the toolbar or you
can add a 'Show All' button to your form with the following code in its Click
event procedure:

Me.FilterOn = False

A refinement you might want to add to keep the two combo boxes in sync if
you navigate from record to record via the navigation buttons rather than the
combo box is to put the following code in the form's Current event procedure:

Me.cboFindOwner = Me.ProjectOwnerID

Ken Sheridan
Stafford, England