How to display a multi-option list

#11

franc sutherland wrote:

Quote:

On Mar 24, 5:08 pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:

franc sutherland wrote:

On Mar 23, 4:55 pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:

franc sutherland wrote:

Hi,

I'm using Access 2003 on Windows XP.

My client wants to classify his customers according to ten
classifications. Each customer can fall into one or more of these ten
classifications.

I want to display each of the possible ten classifications for each
customer record, and then highlight the ones that are applicable,
whilst keeping the others in place but not highlighted. The idea is
to have a standard layout that is the same for each customer, so that
the user can identify just by the position of the highlighted whether
this customer has the appropriate classification.

I have set up the data with a company table, a classifications table
and a x-ref (cross-reference) table which has this sort of structure
x-refID, customerID, classificationID so that I can record more than
one classification per customer.

Are there any in-built tools that can do this?

The only thing I can think of is to list the ten possible
classifications as unbound text boxes, and then using the OnCurrent
event, loop through a recordset of the classificationIDs which are in
the x-ref table for that customerID and then highlighting the
appropriate text box with bold or a color or similar.

Thanks in advance,

Franc.

One method. You could use a listbox. Set the .Selected property to
true or false for the item. Set the listbox as MultiSelect or Simple so
you can select more than 1 row.

Hi Salad,
Thanks for your reply. I have set up the list box as Simple and can
select more than 1 row.
What should it's data source be? Should it have a control source?
Thanks,
Franc.

I don't know. Are the customer classifications static? Or are the
classifications different based on a status type?

I might have a table of 10 classifications if static. Otherwise select
/present your classifications based on customer type. There you have a
list.

Then you'd have a table to store the selections. I might have a command
button to update the customer classifications once all classifications
are selected or create/update/delete a customer status record when the
row is selected/deselected.

'clears/sets all rows to unselected.
'ListType is name of control in example
Dim var As Variant
If Me.ListType.ItemsSelected.Count > 0 Then
For Each var In Me.ListType.ItemsSelected
Me.ListType.Selected(var) = False
Next
Endif

'selects the first row as true
Me.ListType.Selected(0) = True 'or set to false/unselected

You could enumerate the listbox. The following example will display all
rows in the listbox that are selected and at the end present a list of
all items in the listbox. It will select/display the second column (1
as the listbox is zero based) to the op. The listbox contains an ID
(col 0, hidden) and the value (col 1, visible).
Dim s As String
Dim i As Integer
For i = 0 To Me.ListType.ListCount - 1
If Me.ListType.Selected(i) Then
MsgBox Me.ListType.Column(1, i) & " is selected"
End If
s = s & Me.ListType.Column(1, i) & vbNewLine
Next
If s > "" Then MsgBox s

You could have a table with the customerid and customertype. Grab the
customer type from the listbox (col 0) and search the table for the type
and custid. If found, selected is true else false. Scan the list after
edit. If not selected, see if record exists and if so delete it. If
selected and rec now found, add it.

Using a listbox is one method in doing what you want.

Look at Selected, ItemsSelected, ListCount, Column in Help for examples
and clarification.

Hi Salad,

There are ten classifications in a table, tbl_classifications. There
is no additional segregation of the customers by, for instance, type.
Each of the companies will fit into at least one, and possibly, all
ten classifications.

These are linked to the specific companies with the table,
tbl_company_classifications, which has the fields
company_classification_id, company_id, classification_id.

I don't understand how to associate the data in
tbl_company_classifications with a 'simple' list box.
How would I update the data when the selections are changed?

Thanks for your help,

Franc.

Perhaps a command button to update the classifications. Then you read
each row in the classifications listboxs and get the key and look for it
in the table. If the row is selected and it exists, fine, otherwise add
it. If not selected then if found delete it or else fine.

I simply supplied a method of doing it. There might be a better way.

#12

On Mar 30, 8:50*am, franc sutherland <franc.sutherl... (AT) googlemail (DOT) com>
wrote:

Quote:

On Mar 24, 4:34*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:

On Mar 24, 6:09*am, franc sutherland <franc.sutherl... (AT) googlemail (DOT) com
wrote:

On Mar 23, 7:29*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:

On Mar 23, 10:38*am, franc sutherland

franc.sutherl... (AT) googlemail (DOT) com> wrote:
Hi,

I'm using Access 2003 on Windows XP.

My client wants to classify his customers according to ten
classifications. *Each customer can fall into one or more of these ten
classifications.

I want to display each of the possible ten classifications for each
customer record, and then highlight the ones that are applicable,
whilst keeping the others in place but not highlighted. *The idea is
to have a standard layout that is the same for each customer, so that
the user can identify just by the position of the highlighted whether
this customer has the appropriate classification.

I have set up the data with a company table, a classifications table
and a x-ref (cross-reference) table which has this sort of structure
x-refID, customerID, classificationID so that I can record more than
one classification per customer.

Are there any in-built tools that can do this?

The only thing I can think of is to list the ten possible
classifications as unbound text boxes, and then using the OnCurrent
event, loop through a recordset of the classificationIDs which are in
the x-ref table for that customerID and then highlighting the
appropriate text box with bold or a color or similar.

Thanks in advance,

Franc.

you could use a continuous subform to show the classifications, with a
checkbox indicating that a particular classification is *assignedto
the customer
you just need to create a cross-product query (no joins) that always
returns all classificationId's

Hi Roger,
Thanks for your reply,
How do I connect together the continuous forms showing the ten
classifications with the data in the x-ref table which lists the
classificationIDs against customerID?
Thanks,
Franc.- Hide quoted text -

- Show quoted text -

assuming we have three tables,
tblCompany
customerId
customerName

tblClassification
classificationId
classification

tblCompanyClassification
customerId
classificationId

qryAllCC - this query is a cross-product (ie. no joins),
giving us all combinations of customer / classification

select customerId
* * *, classificationId
* from tblCompany
* * *, tblClassification

qryCompanyClassification - *this query gives all
classifications by customer, flagged which are assigned

select a.customerId
* * *, a.classificationId
* * *, iif(nz(b.customerId, "") = "", 0, 1) as assigned
* from qryAllCC as a left join
* * * *tblCompanyClassification as b on a.customerId = b.customerId
* * * * * * * * * * * * * * * * * *and a.classificationId =
b.classificationId

Hi Roger,
Thanks for your explanation. *I have tried to create the queries as
you suggested using the names I had already set up, but I got a list
of all the companies and all the classifications with a 0 in the
assigned column.

Here is the SQL from my version of qryCompanyClassification.

SELECT qry_all_cc.company_id , qry_all_cc.classification_id,
IIf(nz(tbl_company_classification.company_classifi cation_company_id,"")="",
0,1) AS assigned
FROM qry_all_cc LEFT JOIN tbl_company_classification ON
(qry_all_cc.classification_id =
tbl_company_classification.company_classification_ classification_id)
AND (qry_all_cc.company_id =
tbl_company_classification.company_classification_ company_id);

Could you tell me where I've gone wrong, please?

Many thanks,

Franc.- Hide quoted text -

- Show quoted text -

I created a small MDB with tables / queries that outlined above and it
works as expected
as far as I can tell your query matches mine
how many customers ?
how many classifications ?
how many rows do qryAllCC return, should be customer *
classification ?

for a given customer,
how many rows in tbl_company_classification ?

#13

On Mar 30, 5:42*pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:

Quote:

franc sutherland wrote:
On Mar 24, 5:08 pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:

franc sutherland wrote:

On Mar 23, 4:55 pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:

franc sutherland wrote:

Hi,

I'm using Access 2003 on Windows XP.

My client wants to classify his customers according to ten
classifications. *Each customer can fall into one or more of theseten
classifications.

I want to display each of the possible ten classifications for each
customer record, and then highlight the ones that are applicable,
whilst keeping the others in place but not highlighted. *The idea is
to have a standard layout that is the same for each customer, so that
the user can identify just by the position of the highlighted whether
this customer has the appropriate classification.

I have set up the data with a company table, a classifications table
and a x-ref (cross-reference) table which has this sort of structure
x-refID, customerID, classificationID so that I can record more than
one classification per customer.

Are there any in-built tools that can do this?

The only thing I can think of is to list the ten possible
classifications as unbound text boxes, and then using the OnCurrent
event, loop through a recordset of the classificationIDs which are in
the x-ref table for that customerID and then highlighting the
appropriate text box with bold or a color or similar.

Thanks in advance,

Franc.

One method. *You could use a listbox. *Set the .Selected propertyto
true or false for the item. *Set the listbox as MultiSelect or Simple so
you can select more than 1 row.

Hi Salad,
Thanks for your reply. *I have set up the list box as Simple and can
select more than 1 row.
What should it's data source be? *Should it have a control source?
Thanks,
Franc.

I don't know. *Are the customer classifications static? *Or are the
classifications different based on a status type?

I might have a table of 10 classifications if static. *Otherwise select
/present your classifications based on customer type. *There you havea
list.

Then you'd have a table to store the selections. *I might have a command
button to update the customer classifications once all classifications
are selected or create/update/delete a customer status record when the
row is selected/deselected.

* * 'clears/sets all rows to unselected.
* * 'ListType is name of control in example
* * Dim var As Variant
* * If Me.ListType.ItemsSelected.Count > 0 Then
* * * *For Each var In Me.ListType.ItemsSelected
* * * * * * *Me.ListType.Selected(var) = False
* * * *Next
* * Endif * * *

* * 'selects the first row as true
* * Me.ListType.Selected(0) = True *'or set to false/unselected

You could enumerate the listbox. *The following example will display all
rows in the listbox that are selected and at the end present a list of
all items in the listbox. *It will select/display the second column (1
as the listbox is zero based) to the op. *The listbox contains an ID
(col 0, hidden) and the value (col 1, visible).
* * Dim s As String
* * Dim i As Integer
* * For i = 0 To Me.ListType.ListCount - 1
* * * * If Me.ListType.Selected(i) Then
* * * * * * MsgBox Me.ListType.Column(1, i) & " is selected"
* * * * End If
* * * * s = s & Me.ListType.Column(1, i) & vbNewLine
* * Next
* * If s > "" Then MsgBox s

You could have a table with the customerid and customertype. *Grab the
customer type from the listbox (col 0) and search the table for the type
and custid. *If found, selected is true else false. *Scan the list after
edit. *If not selected, see if record exists and if so delete it. *If
selected and rec now found, add it.

Using a listbox is one method in doing what you want.

Look at Selected, ItemsSelected, ListCount, Column in Help for examples
and clarification.

Hi Salad,

There are ten classifications in a table, tbl_classifications. *There
is no additional segregation of the customers by, for instance, type.
Each of the companies will fit into at least one, and possibly, all
ten classifications.

These are linked to the specific companies with the table,
tbl_company_classifications, which has the fields
company_classification_id, company_id, classification_id.

I don't understand how to associate the data in
tbl_company_classifications with a 'simple' list box.
How would I update the data when the selections are changed?

Thanks for your help,

Franc.

Perhaps a command button to update the classifications. *Then you read
each row in the classifications listboxs and get the key and look for it
in the table. *If the row is selected and it exists, fine, otherwise add
it. *If not selected then if found delete it or else fine.

I simply supplied a method of doing it. *There might be a better way.

Hi Salad,

Thanks for clearing that up. I think I understand what you mean.

Thanks again for your help,

Franc.

#14

On Mar 30, 6:37*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:

Quote:

On Mar 30, 8:50*am, franc sutherland <franc.sutherl... (AT) googlemail (DOT) com
wrote:

On Mar 24, 4:34*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:

On Mar 24, 6:09*am, franc sutherland <franc.sutherl... (AT) googlemail (DOT) com
wrote:

On Mar 23, 7:29*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:

On Mar 23, 10:38*am, franc sutherland

franc.sutherl... (AT) googlemail (DOT) com> wrote:
Hi,

I'm using Access 2003 on Windows XP.

My client wants to classify his customers according to ten
classifications. *Each customer can fall into one or more of these ten
classifications.

I want to display each of the possible ten classifications for each
customer record, and then highlight the ones that are applicable,
whilst keeping the others in place but not highlighted. *The idea is
to have a standard layout that is the same for each customer, so that
the user can identify just by the position of the highlighted whether
this customer has the appropriate classification.

I have set up the data with a company table, a classifications table
and a x-ref (cross-reference) table which has this sort of structure
x-refID, customerID, classificationID so that I can record morethan
one classification per customer.

Are there any in-built tools that can do this?

The only thing I can think of is to list the ten possible
classifications as unbound text boxes, and then using the OnCurrent
event, loop through a recordset of the classificationIDs which are in
the x-ref table for that customerID and then highlighting the
appropriate text box with bold or a color or similar.

Thanks in advance,

Franc.

you could use a continuous subform to show the classifications, with a
checkbox indicating that a particular classification is *assigned to
the customer
you just need to create a cross-product query (no joins) that always
returns all classificationId's

Hi Roger,
Thanks for your reply,
How do I connect together the continuous forms showing the ten
classifications with the data in the x-ref table which lists the
classificationIDs against customerID?
Thanks,
Franc.- Hide quoted text -

- Show quoted text -

assuming we have three tables,
tblCompany
customerId
customerName

tblClassification
classificationId
classification

tblCompanyClassification
customerId
classificationId

qryAllCC - this query is a cross-product (ie. no joins),
giving us all combinations of customer / classification

select customerId
* * *, classificationId
* from tblCompany
* * *, tblClassification

qryCompanyClassification - *this query gives all
classifications by customer, flagged which are assigned

select a.customerId
* * *, a.classificationId
* * *, iif(nz(b.customerId, "") = "", 0, 1) as assigned
* from qryAllCC as a left join
* * * *tblCompanyClassification as b on a.customerId = b.customerId
* * * * * * * * * * * * * * * * * * and a.classificationId =
b.classificationId

Hi Roger,
Thanks for your explanation. *I have tried to create the queries as
you suggested using the names I had already set up, but I got a list
of all the companies and all the classifications with a 0 in the
assigned column.

Here is the SQL from my version of qryCompanyClassification.

SELECT qry_all_cc.company_id , qry_all_cc.classification_id,
IIf(nz(tbl_company_classification.company_classifi cation_company_id,"")="",
0,1) AS assigned
FROM qry_all_cc LEFT JOIN tbl_company_classification ON
(qry_all_cc.classification_id =
tbl_company_classification.company_classification_ classification_id)
AND (qry_all_cc.company_id =
tbl_company_classification.company_classification_ company_id);

Could you tell me where I've gone wrong, please?

Many thanks,

Franc.- Hide quoted text -

- Show quoted text -

I created a small MDB with tables / queries that outlined above and it
works as expected
as far as I can tell your query matches mine
how many customers ?
how many classifications ?
how many rows do qryAllCC return, should be customer *
classification ?

for a given customer,
how many rows in tbl_company_classification ?

Hi Roger,
Thanks for following this up.

In my database there are:

2048 customers
10 classifications

qryAllCC returns 20480 rows.

The number of entries for a given customer in
tbl_company_classification depends on how many classifications that
customer falls in to, which is anywhere between zero and all ten.

In qry_company_classification there are 20480 rows and all the
assigned are 0. If I put a customerID in the criteria in the query
column then it returns the ten possibles with a one against the
correct classifications.

It looks like I wasn't sending a customerID as criteria to the query?

Franc.

#15

On Apr 1, 12:44*pm, franc sutherland <franc.sutherl... (AT) googlemail (DOT) com>
wrote:

Quote:

On Mar 30, 6:37*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:

On Mar 30, 8:50*am, franc sutherland <franc.sutherl... (AT) googlemail (DOT) com
wrote:

On Mar 24, 4:34*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:

On Mar 24, 6:09*am, franc sutherland <franc.sutherl... (AT) googlemail (DOT) .com
wrote:

On Mar 23, 7:29*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:

On Mar 23, 10:38*am, franc sutherland

franc.sutherl... (AT) googlemail (DOT) com> wrote:
Hi,

I'm using Access 2003 on Windows XP.

My client wants to classify his customers according to ten
classifications. *Each customer can fall into one or more of these ten
classifications.

I want to display each of the possible ten classifications for each
customer record, and then highlight the ones that are applicable,
whilst keeping the others in place but not highlighted. *The idea is
to have a standard layout that is the same for each customer,so that
the user can identify just by the position of the highlightedwhether
this customer has the appropriate classification.

I have set up the data with a company table, a classifications table
and a x-ref (cross-reference) table which has this sort of structure
x-refID, customerID, classificationID so that I can record more than
one classification per customer.

Are there any in-built tools that can do this?

The only thing I can think of is to list the ten possible
classifications as unbound text boxes, and then using the OnCurrent
event, loop through a recordset of the classificationIDs which are in
the x-ref table for that customerID and then highlighting the
appropriate text box with bold or a color or similar.

Thanks in advance,

Franc.

you could use a continuous subform to show the classifications,with a
checkbox indicating that a particular classification is *assigned to
the customer
you just need to create a cross-product query (no joins) that always
returns all classificationId's

Hi Roger,
Thanks for your reply,
How do I connect together the continuous forms showing the ten
classifications with the data in the x-ref table which lists the
classificationIDs against customerID?
Thanks,
Franc.- Hide quoted text -

- Show quoted text -

assuming we have three tables,
tblCompany
customerId
customerName

tblClassification
classificationId
classification

tblCompanyClassification
customerId
classificationId

qryAllCC - this query is a cross-product (ie. no joins),
giving us all combinations of customer / classification

select customerId
* * *, classificationId
* from tblCompany
* * *, tblClassification

qryCompanyClassification - *this query gives all
classifications by customer, flagged which are assigned

select a.customerId
* * *, a.classificationId
* * *, iif(nz(b.customerId, "") = "", 0, 1) as assigned
* from qryAllCC as a left join
* * * *tblCompanyClassification as b on a.customerId = b.customerId
* * * * * * * * * * * * * * * * ** and a.classificationId =
b.classificationId

Hi Roger,
Thanks for your explanation. *I have tried to create the queries as
you suggested using the names I had already set up, but I got a list
of all the companies and all the classifications with a 0 in the
assigned column.

Here is the SQL from my version of qryCompanyClassification.

SELECT qry_all_cc.company_id , qry_all_cc.classification_id,
IIf(nz(tbl_company_classification.company_classifi cation_company_id,"")="",
0,1) AS assigned
FROM qry_all_cc LEFT JOIN tbl_company_classification ON
(qry_all_cc.classification_id =
tbl_company_classification.company_classification_ classification_id)
AND (qry_all_cc.company_id =
tbl_company_classification.company_classification_ company_id);

Could you tell me where I've gone wrong, please?

Many thanks,

Franc.- Hide quoted text -

- Show quoted text -

I created a small MDB with tables / queries that outlined above and it
works as expected
as far as I can tell your query matches mine
how many customers ?
how many classifications ?
how many rows do qryAllCC return, should be customer *
classification ?

for a given customer,
how many rows in tbl_company_classification ?

Hi Roger,
Thanks for following this up.

In my database there are:

2048 customers
10 classifications

qryAllCC returns 20480 rows.

The number of entries for a given customer in
tbl_company_classification depends on how many classifications that
customer falls in to, which is anywhere between zero and all ten.

In qry_company_classification there are 20480 rows and all the
assigned are 0. *If I put a customerID in the criteria in the query
column then it returns the ten possibles with a one against the
correct classifications.

It looks like I wasn't sending a customerID as criteria to the query?

Franc.

Hi Roger,

I've managed to get it working they way you described. There are now
each of the ten possibilities listed with a tick against those which
are already assigned.
The problem now is how to amend and update the data from this layout?
The query result and the continuous form are read-only. Does there
need to be a separate step outside of this display in order to amend
and update the classifications?

Thanks again,

Franc.

#16

On Apr 1, 6:08*am, franc sutherland <franc.sutherl... (AT) googlemail (DOT) com>
wrote:

Quote:

On Apr 1, 12:44*pm, franc sutherland <franc.sutherl... (AT) googlemail (DOT) com
wrote:

On Mar 30, 6:37*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:

On Mar 30, 8:50*am, franc sutherland <franc.sutherl... (AT) googlemail (DOT) com
wrote:

On Mar 24, 4:34*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:

On Mar 24, 6:09*am, franc sutherland <franc.sutherl... (AT) googlemail (DOT) com
wrote:

On Mar 23, 7:29*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:

On Mar 23, 10:38*am, franc sutherland

franc.sutherl... (AT) googlemail (DOT) com> wrote:
Hi,

I'm using Access 2003 on Windows XP.

My client wants to classify his customers according to ten
classifications. *Each customer can fall into one or moreof these ten
classifications.

I want to display each of the possible ten classifications for each
customer record, and then highlight the ones that are applicable,
whilst keeping the others in place but not highlighted. *The idea is
to have a standard layout that is the same for each customer, so that
the user can identify just by the position of the highlighted whether
this customer has the appropriate classification.

I have set up the data with a company table, a classifications table
and a x-ref (cross-reference) table which has this sort of structure
x-refID, customerID, classificationID so that I can record more than
one classification per customer.

Are there any in-built tools that can do this?

The only thing I can think of is to list the ten possible
classifications as unbound text boxes, and then using the OnCurrent
event, loop through a recordset of the classificationIDs which are in
the x-ref table for that customerID and then highlighting the
appropriate text box with bold or a color or similar.

Thanks in advance,

Franc.

you could use a continuous subform to show the classifications, with a
checkbox indicating that a particular classification is *assigned to
the customer
you just need to create a cross-product query (no joins) thatalways
returns all classificationId's

Hi Roger,
Thanks for your reply,
How do I connect together the continuous forms showing the ten
classifications with the data in the x-ref table which lists the
classificationIDs against customerID?
Thanks,
Franc.- Hide quoted text -

- Show quoted text -

assuming we have three tables,
tblCompany
customerId
customerName

tblClassification
classificationId
classification

tblCompanyClassification
customerId
classificationId

qryAllCC - this query is a cross-product (ie. no joins),
giving us all combinations of customer / classification

select customerId
* * *, classificationId
* from tblCompany
* * *, tblClassification

qryCompanyClassification - *this query gives all
classifications by customer, flagged which are assigned

select a.customerId
* * *, a.classificationId
* * *, iif(nz(b.customerId, "") = "", 0, 1) as assigned
* from qryAllCC as a left join
* * * *tblCompanyClassification as b on a.customerId = b.customerId
* * * * * * * * * * * * * * * * * * and a.classificationId =
b.classificationId

Hi Roger,
Thanks for your explanation. *I have tried to create the queries as
you suggested using the names I had already set up, but I got a list
of all the companies and all the classifications with a 0 in the
assigned column.

Here is the SQL from my version of qryCompanyClassification.

SELECT qry_all_cc.company_id , qry_all_cc.classification_id,
IIf(nz(tbl_company_classification.company_classifi cation_company_id,"")="",
0,1) AS assigned
FROM qry_all_cc LEFT JOIN tbl_company_classification ON
(qry_all_cc.classification_id =
tbl_company_classification.company_classification_ classification_id)
AND (qry_all_cc.company_id =
tbl_company_classification.company_classification_ company_id);

Could you tell me where I've gone wrong, please?

Many thanks,

Franc.- Hide quoted text -

- Show quoted text -

I created a small MDB with tables / queries that outlined above and it
works as expected
as far as I can tell your query matches mine
how many customers ?
how many classifications ?
how many rows do qryAllCC return, should be customer *
classification ?

for a given customer,
how many rows in tbl_company_classification ?

Hi Roger,
Thanks for following this up.

In my database there are:

2048 customers
10 classifications

qryAllCC returns 20480 rows.

The number of entries for a given customer in
tbl_company_classification depends on how many classifications that
customer falls in to, which is anywhere between zero and all ten.

In qry_company_classification there are 20480 rows and all the
assigned are 0. *If I put a customerID in the criteria in the query
column then it returns the ten possibles with a one against the
correct classifications.

It looks like I wasn't sending a customerID as criteria to the query?

Franc.

Hi Roger,

I've managed to get it working they way you described. *There are now
each of the ten possibilities listed with a tick against those which
are already assigned.
The problem now is how to amend and update the data from this layout?
The query result and the continuous form are read-only. *Does there
need to be a separate step outside of this display in order to amend
and update the classifications?

Thanks again,

Franc.- Hide quoted text -

- Show quoted text -

um... you could use the checkbox's double click event to reverse the
possibility, update the record, and requery the form

you could have a unbound combo box, with yes / no, based on the bound
value of the checkbox, and use the combo box afterUpdate event to
update the record

#17

On Apr 1, 5:14*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:

Quote:

On Apr 1, 6:08*am, franc sutherland <franc.sutherl... (AT) googlemail (DOT) com
wrote:

On Apr 1, 12:44*pm, franc sutherland <franc.sutherl... (AT) googlemail (DOT) com
wrote:

On Mar 30, 6:37*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:

On Mar 30, 8:50*am, franc sutherland <franc.sutherl... (AT) googlemail (DOT) .com
wrote:

On Mar 24, 4:34*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:

On Mar 24, 6:09*am, franc sutherland <franc.sutherl... (AT) googlemail (DOT) com
wrote:

On Mar 23, 7:29*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:

On Mar 23, 10:38*am, franc sutherland

franc.sutherl... (AT) googlemail (DOT) com> wrote:
Hi,

I'm using Access 2003 on Windows XP.

My client wants to classify his customers according to ten
classifications. *Each customer can fall into one or more of these ten
classifications.

I want to display each of the possible ten classifications for each
customer record, and then highlight the ones that are applicable,
whilst keeping the others in place but not highlighted. *The idea is
to have a standard layout that is the same for each customer, so that
the user can identify just by the position of the highlighted whether
this customer has the appropriate classification.

I have set up the data with a company table, a classifications table
and a x-ref (cross-reference) table which has this sort of structure
x-refID, customerID, classificationID so that I can record more than
one classification per customer.

Are there any in-built tools that can do this?

The only thing I can think of is to list the ten possible
classifications as unbound text boxes, and then using theOnCurrent
event, loop through a recordset of the classificationIDs which are in
the x-ref table for that customerID and then highlightingthe
appropriate text box with bold or a color or similar.

Thanks in advance,

Franc.

you could use a continuous subform to show the classifications, with a
checkbox indicating that a particular classification is *assigned to
the customer
you just need to create a cross-product query (no joins) that always
returns all classificationId's

Hi Roger,
Thanks for your reply,
How do I connect together the continuous forms showing the ten
classifications with the data in the x-ref table which lists the
classificationIDs against customerID?
Thanks,
Franc.- Hide quoted text -

- Show quoted text -

assuming we have three tables,
tblCompany
customerId
customerName

tblClassification
classificationId
classification

tblCompanyClassification
customerId
classificationId

qryAllCC - this query is a cross-product (ie. no joins),
giving us all combinations of customer / classification

select customerId
* * *, classificationId
* from tblCompany
* * *, tblClassification

qryCompanyClassification - *this query gives all
classifications by customer, flagged which are assigned

select a.customerId
* * *, a.classificationId
* * *, iif(nz(b.customerId, "") = "", 0, 1) as assigned
* from qryAllCC as a left join
* * * *tblCompanyClassification as b on a.customerId = b.customerId
* * * * * * * * * * * * * * * ** * and a.classificationId =
b.classificationId

Hi Roger,
Thanks for your explanation. *I have tried to create the queries as
you suggested using the names I had already set up, but I got a list
of all the companies and all the classifications with a 0 in the
assigned column.

Here is the SQL from my version of qryCompanyClassification.

SELECT qry_all_cc.company_id , qry_all_cc.classification_id,
IIf(nz(tbl_company_classification.company_classifi cation_company_id,"")="",
0,1) AS assigned
FROM qry_all_cc LEFT JOIN tbl_company_classification ON
(qry_all_cc.classification_id =
tbl_company_classification.company_classification_ classification_id)
AND (qry_all_cc.company_id =
tbl_company_classification.company_classification_ company_id);

Could you tell me where I've gone wrong, please?

Many thanks,

Franc.- Hide quoted text -

- Show quoted text -

I created a small MDB with tables / queries that outlined above andit
works as expected
as far as I can tell your query matches mine
how many customers ?
how many classifications ?
how many rows do qryAllCC return, should be customer *
classification ?

for a given customer,
how many rows in tbl_company_classification ?

Hi Roger,
Thanks for following this up.

In my database there are:

2048 customers
10 classifications

qryAllCC returns 20480 rows.

The number of entries for a given customer in
tbl_company_classification depends on how many classifications that
customer falls in to, which is anywhere between zero and all ten.

In qry_company_classification there are 20480 rows and all the
assigned are 0. *If I put a customerID in the criteria in the query
column then it returns the ten possibles with a one against the
correct classifications.

It looks like I wasn't sending a customerID as criteria to the query?

Franc.

Hi Roger,

I've managed to get it working they way you described. *There are now
each of the ten possibilities listed with a tick against those which
are already assigned.
The problem now is how to amend and update the data from this layout?
The query result and the continuous form are read-only. *Does there
need to be a separate step outside of this display in order to amend
and update the classifications?

Thanks again,

Franc.- Hide quoted text -

- Show quoted text -

um... you could use the checkbox's double click event to reverse the
possibility, update the record, and requery the form

you could have a unbound combo box, with yes / no, based on the bound
value of the checkbox, and use the combo box afterUpdate event to
update the record

Hi Roger,
Thanks for that. They both work really well.
All the best,
Franc.

#18

On Mar 30, 5:42*pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:

Quote:

franc sutherland wrote:
On Mar 24, 5:08 pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:

franc sutherland wrote:

On Mar 23, 4:55 pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:

franc sutherland wrote:

Hi,

I'm using Access 2003 on Windows XP.

My client wants to classify his customers according to ten
classifications. *Each customer can fall into one or more of theseten
classifications.

I want to display each of the possible ten classifications for each
customer record, and then highlight the ones that are applicable,
whilst keeping the others in place but not highlighted. *The idea is
to have a standard layout that is the same for each customer, so that
the user can identify just by the position of the highlighted whether
this customer has the appropriate classification.

I have set up the data with a company table, a classifications table
and a x-ref (cross-reference) table which has this sort of structure
x-refID, customerID, classificationID so that I can record more than
one classification per customer.

Are there any in-built tools that can do this?

The only thing I can think of is to list the ten possible
classifications as unbound text boxes, and then using the OnCurrent
event, loop through a recordset of the classificationIDs which are in
the x-ref table for that customerID and then highlighting the
appropriate text box with bold or a color or similar.

Thanks in advance,

Franc.

One method. *You could use a listbox. *Set the .Selected propertyto
true or false for the item. *Set the listbox as MultiSelect or Simple so
you can select more than 1 row.

Hi Salad,
Thanks for your reply. *I have set up the list box as Simple and can
select more than 1 row.
What should it's data source be? *Should it have a control source?
Thanks,
Franc.

I don't know. *Are the customer classifications static? *Or are the
classifications different based on a status type?

I might have a table of 10 classifications if static. *Otherwise select
/present your classifications based on customer type. *There you havea
list.

Then you'd have a table to store the selections. *I might have a command
button to update the customer classifications once all classifications
are selected or create/update/delete a customer status record when the
row is selected/deselected.

* * 'clears/sets all rows to unselected.
* * 'ListType is name of control in example
* * Dim var As Variant
* * If Me.ListType.ItemsSelected.Count > 0 Then
* * * *For Each var In Me.ListType.ItemsSelected
* * * * * * *Me.ListType.Selected(var) = False
* * * *Next
* * Endif * * *

* * 'selects the first row as true
* * Me.ListType.Selected(0) = True *'or set to false/unselected

You could enumerate the listbox. *The following example will display all
rows in the listbox that are selected and at the end present a list of
all items in the listbox. *It will select/display the second column (1
as the listbox is zero based) to the op. *The listbox contains an ID
(col 0, hidden) and the value (col 1, visible).
* * Dim s As String
* * Dim i As Integer
* * For i = 0 To Me.ListType.ListCount - 1
* * * * If Me.ListType.Selected(i) Then
* * * * * * MsgBox Me.ListType.Column(1, i) & " is selected"
* * * * End If
* * * * s = s & Me.ListType.Column(1, i) & vbNewLine
* * Next
* * If s > "" Then MsgBox s

You could have a table with the customerid and customertype. *Grab the
customer type from the listbox (col 0) and search the table for the type
and custid. *If found, selected is true else false. *Scan the list after
edit. *If not selected, see if record exists and if so delete it. *If
selected and rec now found, add it.

Using a listbox is one method in doing what you want.

Look at Selected, ItemsSelected, ListCount, Column in Help for examples
and clarification.

Hi Salad,

There are ten classifications in a table, tbl_classifications. *There
is no additional segregation of the customers by, for instance, type.
Each of the companies will fit into at least one, and possibly, all
ten classifications.

These are linked to the specific companies with the table,
tbl_company_classifications, which has the fields
company_classification_id, company_id, classification_id.

I don't understand how to associate the data in
tbl_company_classifications with a 'simple' list box.
How would I update the data when the selections are changed?

Thanks for your help,

Franc.

Perhaps a command button to update the classifications. *Then you read
each row in the classifications listboxs and get the key and look for it
in the table. *If the row is selected and it exists, fine, otherwise add
it. *If not selected then if found delete it or else fine.

I simply supplied a method of doing it. *There might be a better way.

Hi Salad,
Which ListBox Event could you use to update the classifications rather
than having a separate command button. Would AfterUpdate cover all
eventualities?
Many thanks,
Franc.

#19

franc sutherland wrote:

Quote:

On Mar 30, 5:42 pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:

franc sutherland wrote:

On Mar 24, 5:08 pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:

franc sutherland wrote:

On Mar 23, 4:55 pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:

franc sutherland wrote:

Hi,

I'm using Access 2003 on Windows XP.

My client wants to classify his customers according to ten
classifications. Each customer can fall into one or more of these ten
classifications.

I want to display each of the possible ten classifications for each
customer record, and then highlight the ones that are applicable,
whilst keeping the others in place but not highlighted. The idea is
to have a standard layout that is the same for each customer, so that
the user can identify just by the position of the highlighted whether
this customer has the appropriate classification.

I have set up the data with a company table, a classifications table
and a x-ref (cross-reference) table which has this sort of structure
x-refID, customerID, classificationID so that I can record more than
one classification per customer.

Are there any in-built tools that can do this?

The only thing I can think of is to list the ten possible
classifications as unbound text boxes, and then using the OnCurrent
event, loop through a recordset of the classificationIDs which are in
the x-ref table for that customerID and then highlighting the
appropriate text box with bold or a color or similar.

Thanks in advance,

Franc.

One method. You could use a listbox. Set the .Selected property to
true or false for the item. Set the listbox as MultiSelect or Simple so
you can select more than 1 row.

Hi Salad,
Thanks for your reply. I have set up the list box as Simple and can
select more than 1 row.
What should it's data source be? Should it have a control source?
Thanks,
Franc.

I don't know. Are the customer classifications static? Or are the
classifications different based on a status type?

I might have a table of 10 classifications if static. Otherwise select
/present your classifications based on customer type. There you have a
list.

Then you'd have a table to store the selections. I might have a command
button to update the customer classifications once all classifications
are selected or create/update/delete a customer status record when the
row is selected/deselected.

'clears/sets all rows to unselected.
'ListType is name of control in example
Dim var As Variant
If Me.ListType.ItemsSelected.Count > 0 Then
For Each var In Me.ListType.ItemsSelected
Me.ListType.Selected(var) = False
Next
Endif

'selects the first row as true
Me.ListType.Selected(0) = True 'or set to false/unselected

You could enumerate the listbox. The following example will display all
rows in the listbox that are selected and at the end present a list of
all items in the listbox. It will select/display the second column (1
as the listbox is zero based) to the op. The listbox contains an ID
(col 0, hidden) and the value (col 1, visible).
Dim s As String
Dim i As Integer
For i = 0 To Me.ListType.ListCount - 1
If Me.ListType.Selected(i) Then
MsgBox Me.ListType.Column(1, i) & " is selected"
End If
s = s & Me.ListType.Column(1, i) & vbNewLine
Next
If s > "" Then MsgBox s

You could have a table with the customerid and customertype. Grab the
customer type from the listbox (col 0) and search the table for the type
and custid. If found, selected is true else false. Scan the list after
edit. If not selected, see if record exists and if so delete it. If
selected and rec now found, add it.

Using a listbox is one method in doing what you want.

Look at Selected, ItemsSelected, ListCount, Column in Help for examples
and clarification.

Hi Salad,

There are ten classifications in a table, tbl_classifications. There
is no additional segregation of the customers by, for instance, type.
Each of the companies will fit into at least one, and possibly, all
ten classifications.

These are linked to the specific companies with the table,
tbl_company_classifications, which has the fields
company_classification_id, company_id, classification_id.

I don't understand how to associate the data in
tbl_company_classifications with a 'simple' list box.
How would I update the data when the selections are changed?

Thanks for your help,

Franc.

Perhaps a command button to update the classifications. Then you read
each row in the classifications listboxs and get the key and look for it
in the table. If the row is selected and it exists, fine, otherwise add
it. If not selected then if found delete it or else fine.

I simply supplied a method of doing it. There might be a better way.

Hi Salad,
Which ListBox Event could you use to update the classifications rather
than having a separate command button. Would AfterUpdate cover all
eventualities?
Many thanks,
Franc.

I have a form with a multiselect listbox. I enter
msgbox "After"
in the Afterupdate event. Each time I clicked on a row it would spit
out "After".

So it can be run as you click or run as a batch.

#20

On Apr 6, 4:11*pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:

Quote:

franc sutherland wrote:
On Mar 30, 5:42 pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:

franc sutherland wrote:

On Mar 24, 5:08 pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:

franc sutherland wrote:

On Mar 23, 4:55 pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:

franc sutherland wrote:

Hi,

I'm using Access 2003 on Windows XP.

My client wants to classify his customers according to ten
classifications. *Each customer can fall into one or more of these ten
classifications.

I want to display each of the possible ten classifications for each
customer record, and then highlight the ones that are applicable,
whilst keeping the others in place but not highlighted. *The idea is
to have a standard layout that is the same for each customer, so that
the user can identify just by the position of the highlighted whether
this customer has the appropriate classification.

I have set up the data with a company table, a classifications table
and a x-ref (cross-reference) table which has this sort of structure
x-refID, customerID, classificationID so that I can record more than
one classification per customer.

Are there any in-built tools that can do this?

The only thing I can think of is to list the ten possible
classifications as unbound text boxes, and then using the OnCurrent
event, loop through a recordset of the classificationIDs which arein
the x-ref table for that customerID and then highlighting the
appropriate text box with bold or a color or similar.

Thanks in advance,

Franc.

One method. *You could use a listbox. *Set the .Selected property to
true or false for the item. *Set the listbox as MultiSelect or Simple so
you can select more than 1 row.

Hi Salad,
Thanks for your reply. *I have set up the list box as Simple and can
select more than 1 row.
What should it's data source be? *Should it have a control source?
Thanks,
Franc.

I don't know. *Are the customer classifications static? *Or are the
classifications different based on a status type?

I might have a table of 10 classifications if static. *Otherwise select
/present your classifications based on customer type. *There you have a
list.

Then you'd have a table to store the selections. *I might have a command
button to update the customer classifications once all classifications
are selected or create/update/delete a customer status record when the
row is selected/deselected.

* *'clears/sets all rows to unselected.
* *'ListType is name of control in example
* *Dim var As Variant
* *If Me.ListType.ItemsSelected.Count > 0 Then
* * * For Each var In Me.ListType.ItemsSelected
* * * * * * Me.ListType.Selected(var) = False
* * * Next
* *Endif * * *

* *'selects the first row as true
* *Me.ListType.Selected(0) = True *'or set to false/unselected

You could enumerate the listbox. *The following example will display all
rows in the listbox that are selected and at the end present a list of
all items in the listbox. *It will select/display the second column(1
as the listbox is zero based) to the op. *The listbox contains an ID
(col 0, hidden) and the value (col 1, visible).
* *Dim s As String
* *Dim i As Integer
* *For i = 0 To Me.ListType.ListCount - 1
* * * *If Me.ListType.Selected(i) Then
* * * * * *MsgBox Me.ListType.Column(1, i) & " is selected"
* * * *End If
* * * *s = s & Me.ListType.Column(1, i) & vbNewLine
* *Next
* *If s > "" Then MsgBox s

You could have a table with the customerid and customertype. *Grab the
customer type from the listbox (col 0) and search the table for the type
and custid. *If found, selected is true else false. *Scan the list after
edit. *If not selected, see if record exists and if so delete it. *If
selected and rec now found, add it.

Using a listbox is one method in doing what you want.

Look at Selected, ItemsSelected, ListCount, Column in Help for examples
and clarification.

Hi Salad,

There are ten classifications in a table, tbl_classifications. *There
is no additional segregation of the customers by, for instance, type.
Each of the companies will fit into at least one, and possibly, all
ten classifications.

These are linked to the specific companies with the table,
tbl_company_classifications, which has the fields
company_classification_id, company_id, classification_id.

I don't understand how to associate the data in
tbl_company_classifications with a 'simple' list box.
How would I update the data when the selections are changed?

Thanks for your help,

Franc.

Perhaps a command button to update the classifications. *Then you read
each row in the classifications listboxs and get the key and look for it
in the table. *If the row is selected and it exists, fine, otherwise add
it. *If not selected then if found delete it or else fine.

I simply supplied a method of doing it. *There might be a better way.

Hi Salad,
Which ListBox Event could you use to update the classifications rather
than having a separate command button. *Would AfterUpdate cover all
eventualities?
Many thanks,
Franc.

I have a form with a multiselect listbox. *I enter
* * * * msgbox "After"
in the Afterupdate event. *Each time I clicked on a row it would spit
out "After".

So it can be run as you click or run as a batch.

Hi Salad,
Thanks for your help. I thought it might be AfterUpdate, but I
thought I'd check in case there was something else.
Best Wishes,
Franc.