Select item from popup form

[Guest]

I have a form that has a combo box containing PO numbers looked up from a
table.
I'd like the user to be able to dble-clk the PO field and have a form popup
listing
the PO numbers, and select a number. I'd like to indicate on list, somehow,
that certain numbers have already been used. However, the user can select a
pre-used number if they choose since a PO number can be used multiple times.

Can anyone suggest how to do this?

thanks

 

[Steve Schapel]

Tec,

Well, you could have a separate form pop up to show the list, but
wouldn't it be easier to stick with the combobox? You can add another
column to the combobox, so it shows the PO numbers, and in the second
column either a "yes"/"no" whether the number has already been used, or
if you prefer, a number to show 0 or the number of times it has already
been used. A key point here would be what does "used" mean?

 

[Guest]

Thanks for your reply.

See below.

Tec,

Well, you could have a separate form pop up to show the list, but
wouldn't it be easier to stick with the combobox? You can add another
column to the combobox, so it shows the PO numbers, and in the second
column either a "yes"/"no" whether the number has already been used, or
if you prefer, a number to show 0 or the number of times it has already
been used. A key point here would be what does "used" mean?

By "used", I mean already assigned to a Purchase Request.

I have a Purchase request form that contains a PO number combobox control
that lists numbers contained in a PONumber lookup table. By "used", I mean
numbers that have already been selected in other Purchase request records.

Currently, a different form must be opened to add numbers to the table.
What I'd like to do is have the user be able to see which numbers have aready
been selected and choose it if they want, choose an unused number, or enter a
new number, without closing the current form and opening the PO number entry
form.

 

[Steve Schapel]

Tec,

I think I would try it like this...

1. Make a totals query based on the Purchase Requests table, to return
the number of times each PO number has been used. The SQL view of such
a query would be like this...
SELECT [PO Number], Count([PurchaseRequestID]) As HowMany
FROM [Purchase Requests]
GROUP BY [PO Number]

2. Make another query, joining this first query with you PONumbers
table, and use this query for the Row Source of the combobox on your
form. The SQL of this query will be something like this...
SELECT [PONumbers].[PO Number], Nz([FirstQuery].[HowMany],0) As Used
FROM [PONumbers] LEFT JOIN [FirstQuery]
ON [PONumbers].[PO Number] = [FirstQuery].[PO Number]
ORDER BY [PONumbers].[PO Number]

3. Set the properties of the combobox on your form appropriately, e.g...
Column Count = 2
Bound Column = 1
Column Widths = 2;1.5 (or whatever)
.... so you can see the PO Numbers and the prior usage.

4. Adapt the code as provided here...
http://www.mvps.org/access/forms/frm0015.htm
.... to run on the combobox's Not In List event, so the user can enter a
new number and have it added to the PONumbers table.