Access Drop Down List

[Ken]

Hi
I have a CRM system which is based on access. We use this system to
automatically create all our offers and invoices etc. The problem is
the offers usually look quite absurd as we have no influence in which
order the articles within our offers / invoices appear on the final
document.

What I trying to do is to set up a drop down list of numbers between 1
and 20 which I'll then use to sort the order in which the articles
will appear in our offer / invoice. The problem is that I want this
drop down list to show only the numbers which have not previously been
select for another article within the same offer / invoice. (i.e. When
an article is set to position 1 then the drop then menu will only show
the number 2 to 20 for the next article).

Is something like this possibly in access or am I on a non runner with
this.

Ken

(Access newbie)

[paii]

Try a source query something like this for your dropdown list.
"Ken" <ke************ @zn-ag.com> wrote in message
news:e1******** *************** ***@posting.goo gle.com...

Hi
I have a CRM system which is based on access. We use this system to
automatically create all our offers and invoices etc. The problem is
the offers usually look quite absurd as we have no influence in which
order the articles within our offers / invoices appear on the final
document.

What I trying to do is to set up a drop down list of numbers between 1
and 20 which I'll then use to sort the order in which the articles
will appear in our offer / invoice. The problem is that I want this
drop down list to show only the numbers which have not previously been
select for another article within the same offer / invoice. (i.e. When
an article is set to position 1 then the drop then menu will only show
the number 2 to 20 for the next article).

Is something like this possibly in access or am I on a non runner with
this.

Ken

(Access newbie)

[paii]

Try a source query something like this for your dropdown list.

SELECT DISTINCT tblOrder.ID
FROM tblOrder
WHERE ((((SELECT [OrderID] FROM [tblInvoice] WHERE ([Invoice]=1234)
AND [OrderID]=tblOrder.[ID])) Is Null))
ORDER BY tblOrder.[ID];

You need to create a table tblOrder listing all the numbers you want to
select.

"Ken" <ke************ @zn-ag.com> wrote in message
news:e1******** *************** ***@posting.goo gle.com...

Hi
I have a CRM system which is based on access. We use this system to
automatically create all our offers and invoices etc. The problem is
the offers usually look quite absurd as we have no influence in which
order the articles within our offers / invoices appear on the final
document.

What I trying to do is to set up a drop down list of numbers between 1
and 20 which I'll then use to sort the order in which the articles
will appear in our offer / invoice. The problem is that I want this
drop down list to show only the numbers which have not previously been
select for another article within the same offer / invoice. (i.e. When
an article is set to position 1 then the drop then menu will only show
the number 2 to 20 for the next article).

Is something like this possibly in access or am I on a non runner with
this.

Ken

(Access newbie)

[Ken]

"paii" <pa**@packairin c.com> wrote in message news:<vo******* *****@corp.supe rnews.com>...

Try a source query something like this for your dropdown list.

SELECT DISTINCT tblOrder.ID
FROM tblOrder
WHERE ((((SELECT [OrderID] FROM [tblInvoice] WHERE ([Invoice]=1234)
AND [OrderID]=tblOrder.[ID])) Is Null))
ORDER BY tblOrder.[ID];

You need to create a table tblOrder listing all the numbers you want to
select.

This solution worked like a charm when I entered the invoice number
per hand but unfortunately this isn't possible with my CRM application
(Wincard CRM). Normally what the end user does is make a selection
using the customers company's name this is joined using a company id
to our Invoice Table which contains the Invoice numbers. This Invoice
table is also join to a Order table using the invoice number. The
Order table contains the articles which the customer has purchased
from us. How do I go about having my Invoice number automatically
enterd after the end user has made his original selection using the
company name.i.e instead WHERE ([Invoice] = 123) something like
([Invoice] = (selected invoice number).

Any help would be appreciated

Ken
(Access Newbie)