Sub form question

[Don Rountree]

Is this possible? I have a form that contains only a
date field. I have a sub form that opens with the main
form, but it is not linked to the main form. The form
contains a list of products. I have a third form that is
a subform of my second form. It is linked to the sub
form by an item number field in each form. It is an
order form. Is it possible to also link the main form to
this third form by a date field. I want to end up with
the third form contiaining a date (from the main form),
an item number (from the second form) and the quantity
ordered. I'm having this difficulty because I want to
show all available items and let the user select which
item to order from the total list as opposed to having
the user enter an item number and then place an order for
that item. Any help would be appreciated.

Don Rountree

 

[Gary Miller]

Don,

You may want to back up and rethink this structure a bit.

A normal ordering scenario would more resemble this
structure. You first open a Order Form that will allow you
to enter the Date, the CustomerID and other things related
specifically to the Order in general. Usually the OrderID is
autogenerated, but sometimes that would be an entry.

Next you would have a subform on that form for each item to
be included in the Order and the quantity of that item and
maybe pricing info. The products are usually selected from a
combobox that gets it info from the Products table to make
sure that nothing is ordered that doesn't exist. No date is
needed at this level as it is already part of the Order on
the main form. These two forms are related by the OrderID
which will be automatically populated into the OrderDetails
form by the parent form if properly setup.

Now when you do an invoice you can pull in the Customer
Address and other info from the Customer table as the Order
Form trapped that ID and it can be related in a query for
the Customer Detailed info.

Does this make sense?

Gary Miller
Sisters, OR

 

[Don Rountree]

Mr. Miller,

Yes it does make sense. Actually I have constructed
these types of order scenarios in the past. I have
somewhat of a different issue here. I am a restaurant
supply company and my customers have order guides listing
all the items they buy from me. This application I'm
constructing will be used by one customer, so I don't
need to have a customer table. I want my customer to be
able to see all of his/her items and order only the ones
they need on any given order. This order will be emailed
or faxed to me and my customer service rep will enter it
into our mainframe database. If I leave it up to the
customer to enter item numbers, they will never do it.
I'm working on something to get them away from
handwritting orders (that we cannot read) and faxing them
to us.

I can change direction on construction; however, I want
the customer to see their entire list of items and enter
quantities in the lines they want. I will filter out the
unordered items in the report. Can you help me see the
best possible way to do this? Thanks...

Don Rountree

 

[Gary Miller]

Don,

I think that have the picture that you are looking for now.
A little bit of a different construct than is normally used
in Access, more like what you see on a web ordering page.

If your coding skills are fair, you could have your form be
continuous and display all of the products. I wouldn't worry
about the date thing yet. You could just have the product
number, name, description and an unbound quantity field.

A couple of approaches could be used now. The safest one
would be to give them a Finalize button in the Header that
would roll through each record in the form and, if it had a
quantity add it to your orderdetails table in code, adding
the date and the order item. This would allow the user to
change the quantity and not have that affect anything until
they finalize.

Another way, less safe, would be to trigger an append query
from the AfterUpdate of the quantity field to add it to the
orderdetails table, but if they set it back to 0 you would
then need to delete the previous appended record.

I guess a third way, if it is only for this one customer,
would be to include the quantity in the product table, let
them fill out the form and then when they are done export
the records with a quantity and then do an update query
after that to reset the quantities back to 0 to ready for
the next run. May be pretty simple this way actually.

Gary Miller

 

[Don Rountree]

Thank you Mr. Miller,

I'm going to try the first scenario. I'm fair with code,
but I've not written looping code before. Hopefully
someone will help me when I get to that part. Thanks for
the direction.

Don Rountree

 

[Gary Miller]

Feel free to get back when you need help on that. Actually,
I think that you should also take a close look at Option 3
that I gave you as that can be handled with Action Queries
and would involve a minimum of code. The first one that I
mentioned would involve creating a recordset or, at the
least, the use of a RecordsetClone which does involve some,
not involved, but exact coding.

BTW - My moniker on this list is "Gary", although I deeply
honour the respect you give me with the use of 'Mister'.

Gary Miller