Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50

    Unanswered: Order form layout

    Hey all [again],

    I was wondering, I saw this form in a systems analysis and design book and i was trying to copy it but i 'm running into problems. The form will work nicely with what i'm doing but i can't seem to get it done.

    I've made a picture so u'll know what i'm talking about. The order number is an autonumber and the date ordered should be generated by the system automatically when a new order is prepared. It seems that the medication name (listdown box) and distributor (listdown box), quantity, delivered (checkbox) and date delivered (current date will automatically appear when the delivered checkbox is ticked) are in a subform... or in datasheet view...

    I have a table for medication orders which have in all those fields. So when i try to get it in that type of form layout i am having some trouble. Any thoughts about this?

    bajanElf
    Attached Thumbnails Attached Thumbnails form.gif  
    "The extreme always make an impression." - Jeff Hardy

  2. #2
    Join Date
    Dec 2003
    Posts
    268

    To do it right

    This one isn't so easy.

    I have used something similar to this. Here is what I did.

    Schema for the tables
    ' Create Table : 'tblMed'
    ' Mdeication :
    '
    CREATE TABLE tblMed (
    Medication TEXT(1) NOT NULL,
    CONSTRAINT pk_tblMed PRIMARY KEY (Medication));

    '
    ' Create Table : 'tblDistributor'
    ' distributor :
    '
    CREATE TABLE tblDistributor (
    distributor VARCHAR(25) NOT NULL,
    CONSTRAINT pk_tblDistributor PRIMARY KEY (distributor));

    '
    ' Create Table : 'Order'
    ' OrderID :
    ' OrderDate :
    ' Medication : (references tblMed.Mdeication)
    ' distributor : (references tblDistributor.distributor)
    ' Qty :
    ' Delivered :
    ' DeliveryDate :
    '
    CREATE TABLE Order (
    OrderID LONG NOT NULL,
    OrderDate DATETIME NOT NULL,
    Medication VARCHAR(25) NOT NULL,
    distributor VARCHAR(25) NOT NULL,
    Qty LONG NOT NULL,
    Delivered LONG NOT NULL,
    DeliveryDate DATETIME NOT NULL,
    CONSTRAINT pk_Order PRIMARY KEY (OrderID),
    CONSTRAINT fk_Order FOREIGN KEY (Mdeication)
    REFERENCES tblMed (Mdeication),
    CONSTRAINT fk_Order2 FOREIGN KEY (distributor)
    REFERENCES tblDistributor (distributor));

    on the tblOrder:
    Set the default value of orderdate to Now()
    Change Order# to autonumber
    My tool doesnt allow for boolean, so change delivered to boolean

    Then on the form, add combo boxes for their record source do a query on thier respective tables, medication or distributor.

    This format allows for more than one distributor per medication. However If you want to get fancy:

    You can always do a relationship between distributor (allowing only one distributor per medication but many medications per distributor) Add a foreign key to the medication table for the respective distributor, then on the query you can add both attributes, and set a selection criteria fo rthe distributor to the control name on the form. then add requery to the after update event on the distributor combobox.

    The former way is a little easier to implement and doesn't require any code. The second is a little hard. Either way it is easier than using a subform.

  3. #3
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50

    Unhappy

    Thanks for replying. I got the tables for Orders, Medication and Distributor, got the relationships among the tables and got the combo boxes in the form, but the queries I am a bit confused with.
    "The extreme always make an impression." - Jeff Hardy

  4. #4
    Join Date
    Dec 2003
    Posts
    268

    Need more Info

    I need more information as to what you are confused with.

  5. #5
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50

    Angry

    Sorry I wasn't so clear on that last post. In otherwards I don't understand what you mean by ...

    "Then on the form, add combo boxes for their record source do a query on thier respective tables, medication or distributor."

    You mean put the query in the combo box? Do separate queries for the tables? Don't understand that...

    I'm posting the database just in case
    Attached Files Attached Files
    "The extreme always make an impression." - Jeff Hardy

  6. #6
    Join Date
    Dec 2003
    Posts
    268

    Looks like you got it

    Looks like you got it. The combo boxes are being driven by the tables. One thing I would suggest is adding the informtion for the new tables to your relationships. Enforce referential integrity and cascade on update. This will ensure that the information captured is legitmate and accurate.

  7. #7
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50
    ok, i know u said "it looks like i got it", but i don't think i do. so can u explain it to me again?

    I don't understand what u mean by creating a query and placing it in the control source of the combo box. I have the medication name and supplier name as a drop down list box, so the user can select the medication name... i also don't want the user to have to select the supplier either. in the medication table it has a field for the name of the supplier. so when the user selects the medication name ffrom the list, the supplier name should automatically be generated.

    something else i was wondering about. these orders are like batch orders, so if today for example u have to order 4 different types of medication, there should only be one order number for that, if u get me.

    will i be able to select different medications and so forth, for the same date?
    "The extreme always make an impression." - Jeff Hardy

  8. #8
    Join Date
    Dec 2003
    Posts
    268

    Cant Download

    I cant seem to download your db. Can you repost it.

    Thanks.

    MW

  9. #9
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50

    Smile

    I posted it again. Thanks for the help.

    Lisa
    Attached Files Attached Files
    "The extreme always make an impression." - Jeff Hardy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •