Variable test and options on a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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?
 
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

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.
 
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
 
Hope I was clearer this time :)

SQUILIKI said:
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
Click to expand...
 
See in-line responses...

SQUILIKI said:
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.
Click to expand...
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
Click to expand...

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.
 
Back
Top