Variable test and options on a form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SQUILIKI - 28 Mar 2005 10:53 GMT

I have a pull down box that allows me to get a record from a query . One
of the fields is quantity. Quantity can range from 1 - 25.

I would like to create a subform with text boxes and associated option
buttons . The number of text boxes and option buttons should be the
quantity from the pull down box (combo box). I would then populate the text
box with records from another table. The option box would be used later on
as a switch to update the record to table one or table two. I can define
25 different subforms which I could call from a case function. But I would
like to know if there is a way to dynamically create this subform based on
the quantity form the combo box?

Thanks in advance,
Steve

Reply to this Message

Jeff Boyce - 28 Mar 2005 14:08 GMT

Steve

I'm not visualizing what you are trying to do. I'm not sure I understand
"why", either <g>!

If you have a query that returns specific field values, and have a way to
select a criterion of quantity, I am assuming, given a well-normalized data
design, that you'd get the same fields no matter which quantity you used as
a criterion.

If this is true, why would you need more than one form/subform to display
those fields in?

Signature

More info, please ...

Jeff Boyce
<Access MVP>

> I have a pull down box that allows me to get a record from a query . One
> of the fields is quantity. Quantity can range from 1 - 25.
[quoted text clipped - 10 lines]
> Thanks in advance,
> Steve

Reply to this Message

SQUILIKI - 28 Mar 2005 14:39 GMT

Hi Jeff,
Sorry for not being clearer, and I might be making it more difficult than it
should be.

Background: For simplicity, let's assume I have two tables:
Orders and serial numbers.

The pull down box grabs a record from the order table. which includes the
quantity sold (example 20 PCs), and an order line ID (unique)

The serial number table includes the serial number (unique) as well as the
order table line ID . This is my join.

The serial number table includes all serial numbers of the products built or
sold. When a product is sold, I update the serial number table with the
order table line ID.

My problem is that at the time of order entry, I do not know what PCs with
serial number will be shipped. It is only after I pick them out of inventory
(my inventory does not have a serial control) and the warehouse manually
writes down the serial numbers of the product shipped for this line item, do
I know which serial numbers where shipped.

So, since I know how many PCs were shipped (in this example 20 pcs), and I
have a piece of paper which tells me which serial numbers were shipped, I now
want to update the 20 serial numbers in the serial number table with the
order line ID reference.

What I wanted to do , is open the serial number table with a pull down menu
(serial number from -to : this I can do) and have an option box next to the
, let's say 100 serial numbers from this query. I would then click on the
option box which would update the serial number table with the order line ID
. A counter would show me how many sold PCs would still need to be updated
in the serial table.

Hope this is clearer and would like to thank you up front for your
assistance.

Best regards,
Steve

I have an orders table with order line items.

> Steve
>
[quoted text clipped - 27 lines]
> > Thanks in advance,
> > Steve

Reply to this Message

Jeff Boyce - 29 Mar 2005 14:02 GMT

Steve

One approach to handling this would be to use a form. Add a combo box that
lets you select the OrderNumber. Add a combo box that lists all types of
items (this will require that your inventory items have a "type", otherwise,
you'll have to look through all your monitors and keyboards before you get
to your PCs)

Add one listbox that lists all the items of that type that have no
OrderNumber. Add a second listbox that lists all the items of that type
that HAVE the OrderNumber selected above (to start with, none!).

The generic approach to "wiring" these together is to add command buttons
you use to "push" an item from the "no Order" listbox to the "THIS Order"
listbox (and back again, in case you make a mistake). One way to do this is
to create an Update query that uses the OrderNumber (from the combo box) and
the InventoryItemNumber (from the "no order" listbox) and updates THAT
InventoryItem to THAT OrderNumber. After the update query, use something
like:

Me!lstYourORDEREDItem.Requery
and
Me!lstUnORDEREDItem.Requery

to refresh the two listboxes.

Signature

Good luck

Jeff Boyce
<Access MVP>

> Hi Jeff,
> Sorry for not being clearer, and I might be making it more difficult than it
[quoted text clipped - 70 lines]
> > > Thanks in advance,
> > > Steve

Reply to this Message

SQUILIKI - 29 Mar 2005 14:17 GMT

Hi Jeff,
Great idea. I follow it.I can do everything except figure out how to
populate the second list box from the selected items in the first list box.
Do I write the first list box to a temp tableand then have the second list
box build off ofthis temp table( continual update with Me. lstbox2.requery)???

Second question. How can I count the items selected in the first list box
after every selection. Is there a command lstbox.itemcount.selected or
something like that. If so, does this need to be requeried after every
selection?
Thanks

> I have a pull down box that allows me to get a record from a query . One
> of the fields is quantity. Quantity can range from 1 - 25.
[quoted text clipped - 10 lines]
> Thanks in advance,
> Steve

Reply to this Message

SQUILIKI - 29 Mar 2005 14:21 GMT

Hope I was clearer this time :)

> Hi Jeff,
> Great idea. I follow it.I can do everything except figure out how to
[quoted text clipped - 22 lines]
> > Thanks in advance,
> > Steve

Reply to this Message

Jeff Boyce - 29 Mar 2005 16:06 GMT

See in-line responses...

> Hi Jeff,
> Great idea. I follow it.I can do everything except figure out how to
> populate the second list box from the selected items in the first list box.
> Do I write the first list box to a temp tableand then have the second list
> box build off ofthis temp table( continual update with Me. lstbox2.requery)???

The query/source underlying the second listbox is "show me all of THIS
OrderNumber's records in the InventoryItem table". None indicated yet, none
showing!

The command button includes the code that says "update the InventoryItem
that I've selected (in listbox1) to show the OrderNumber (in combobox 1)".

> Second question. How can I count the items selected in the first list box
> after every selection. Is there a command lstbox.itemcount.selected or
> something like that. If so, does this need to be requeried after every
> selection?
> Thanks

Check Access HELP on syntax for ItemSelected. You can either only allow one
at a time, or multi-select. If you do multi-select, you'll have to change
your code to iterate through all items in the collection of ItemSelected.
If you do single selection, the value of the listbox is the first column of
the source for the listbox.

Signature

Good luck

Jeff Boyce
<Access MVP>

Reply to this Message