Alpha Video Training
Results 1 to 6 of 6

Thread: Master Form for Set, Design Problem

  1. #1
    Member
    Real Name
    Sean Kennedy
    Join Date
    Apr 2006
    Location
    Shokan, NY
    Posts
    275

    Default Master Form for Set, Design Problem

    I've been plodding along in my attempt to redesign my contact manager that is currently in an Access 2.0 database. I have made some good progress with Alpha Five v.7. I have designed the new system entirely with standard menus, genies, and Active Scripting. So far. I'm an amateur database developer. My real job is managing a fireplace dealership (selling, installing, servicing fireplaces and stoves).

    I designed some tables and related them in sets. I have a master form attached to the main set where I want everyone to start. The idea is to first go to the customer records and then branch off to other records, such as contact requests, unit specs, estimates, and invoices. It was working fine up to the point of adding and editing request records. Yesterday I added the unit specs form and now I am having some problems. I think it is related to how I have the set arranged but I can't figure out how to solve it. I have a sample of the database zipped up and can post it. Here's the basic layout:

    Customers
    |====>Requests
    ...............|--------Units
    ...............|=====Estimates

    Customers is linked to Requests as One-to-Many, Requests is linked to Units as One-One and Estimates as One-to-Many.
    The customer number field is the linking field.

    On my master form for this set I have the customer data placed directly on the form. The request, unit, and estimate data is on a tabbed form and is accessed through embedded browses. This worked fine for the requests data. I was able to zoom to the child record and edit and start up the form to add a new record. The request browse would automatically be updated. I have attemped to set up the same thing for the unit data but have ran into a problem. When I try to add a new record I get an error saying the "custnum" is required. This error repeats itself every time I move from one field to another.

    I think it is trying to tell me that the requests table requires the customer number field to be filled. That is to be expected since I have field rules set up to require customer numbers on all records in the Requests table and the Units table. What I can't figure out is how to satisfy the requirement to fill in the customer number in the Requests table when entering a new record into the Units table.

    I want all Unit records to be entered with this form to be entered only when a Request already exists. The Units table should be able to get the custnum field value from the Requests table. The process should be something like this:

    Add customer record to Customers table
    Add request to Requests table, linking together with the Custnum field
    Add units to the Units table, linking from the existing Requests record on the RequestNum field.

    A request cannot be entered without a customer number. A unit cannot be entered without a request number and a customer number.

    Actually, I want to be able to enter Units without a request existing, but not from this form and set. I will have another form for entering units that are not linked to requests.

    I thought of trying to remove the field rule that requires the customer number to see if it will work to ad new records. But I really need to enforce the rule that all requests and units have a customer number entry. Yet, one customer can have several requests. And one customer can have several units. But each request can only have one unit, or no units, linked.

    Where have I strayed from the pure path of database design? Can you give me some guidance on how to structure my sets and forms correctly to get the results I want?

    Thanks,
    Sean Kennedy
    Last edited by seaken64; 04-18-2006 at 10:35 AM.

  2. #2
    Member
    Real Name
    Sean Kennedy
    Join Date
    Apr 2006
    Location
    Shokan, NY
    Posts
    275

    Default

    I removed the rule requiring the customer number so I could use the form and see what happens.

    When I use the Units entry form to enter a new record the program automatically creates a new request number. This must have something to do with the one-one link and how the data integrity is set. I don't understand all that yet. But, although it creates a new request number, it does not automatically fill in the customer number field. When I enter the customer number in the UNITS->CUSTNUM field I am able to create and save a new record in the Units table but it does not link to the Customer record in the embedded browse, nor does the request record show up in the Request tab browse. If I manually enter the Custnum into the REQUESTS->CUSTNUM field then both record show up in the embedded browse and act the way I want.

    I can't figure out how to make this happen automatically. I can manage to enter the customer number into each record manually and that will give me the views I want in the embedded browses on the Customer Contact Manager main form. But there must be a way to have the program automatically fill in the customer number in all the tables simultaneously. I feel like I am close but am missing something. Is my set structure out of whack? Data inegrity rules wrong?

    I'm sorry I'm so thick. I'm going back to the AlphaSports database to look around again. Maybe my idea of having all the connected information on one master form is not practical?

    Thanks,
    Sean

  3. #3
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,595

    Default Set Design

    Hi Sean,
    When you enter a new request the link field will automatically be generated when you add a new record. To add a new unit for that request, you might want to use a pop up form (using a button to add unit) and pass the custnum & requestnum with variables. Otherwise change your set link to one to many for units. With one to one links you cannot enter a record directly on the set form since it is acting like a lookup.

    Hope that helps,
    Robin

  4. #4
    Member
    Real Name
    Sean Kennedy
    Join Date
    Apr 2006
    Location
    Shokan, NY
    Posts
    275

    Default

    Quote Originally Posted by MoGrace
    Hi Sean,
    When you enter a new request the link field will automatically be generated when you add a new record. To add a new unit for that request, you might want to use a pop up form (using a button to add unit) and pass the custnum & requestnum with variables. Otherwise change your set link to one to many for units. With one to one links you cannot enter a record directly on the set form since it is acting like a lookup.

    Hope that helps,
    Robin
    I think that gives me a clue. But wouldn't changing to a One-to-many be the least favorable solution? I only want one Unit per Request. Each unit must generate it's own request form. If I have a customer with multiple units I want a seperate request for each unit. Thats one-to-one, right?

    So, I think I know I have to use variables to pass the custnum and requestnum values. but I have't figured out how to do that. Is this possible using Active Scripting? I have looked for this in the genies but haven't found out how to open a form for a new record and then automatically populate select fields with the values in the current form (where the button is). I have essentially this pop up form already. I just don't have the custnum and requestnum fields filled in when it pops up. How do I do this?

    Sean

  5. #5
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,168

    Default please explain

    Sean,

    I'm thinking people own units. Units have parts, need installation, maintenance and repair (request). Are not Requests related to the customer through the units? Joe has a fireplace he wants installed.

    Why are you not linking the units to the customers, but instead inserting a request between the customers and their units? Why is your set not:

    Customer
    l====UNIT
    l-----Request


    YOU WROTE: "A request cannot be entered without a customer number. A unit cannot be entered without a request number and a customer number.

    ?? Why must there be a request attached to a unit? That means I cannot 'register' me and my Unit with your company for possible future purchase/service.

    YOU WROTE: Actually, I want to be able to enter Units without a request existing, but not from this form and set. I will have another form for entering units that are not linked to requests".

    I don't think you need more. I think the above might be able to fulfill both needs.

    And a note of caution. You might want to copy your db, evacuate the customer files to only a few, best dummy names, and zip and post that instead of the complete table. Good luck.

    Mike W

  6. #6
    Member
    Real Name
    Sean Kennedy
    Join Date
    Apr 2006
    Location
    Shokan, NY
    Posts
    275

    Default

    Quote Originally Posted by Mike Wilson
    Sean,

    I'm thinking people own units. Units have parts, need installation, maintenance and repair (request). Are not Requests related to the customer through the units? Joe has a fireplace he wants installed.

    Why are you not linking the units to the customers, but instead inserting a request between the customers and their units? Why is your set not:

    Customer
    l====UNIT
    l-----Request

    Mike W
    I don't know. I'll have to think about it. You might be right. The thing is, to us, a request is not always related to an existing unit. Many times we get a request for a quote or consult for which we need to generate a work order but do not necessarily sell a unit. Sometimes the request is for a service we offer not directly related to a unit. But all units got there by way of a request, or, in the case of a unit we did not sell, it is already in place before we get a request.

    I'll think about your approach.

    I understand about the dummy records. I should have thought about that more before zipping the files. I'll get better at this.

    Thanks for the help.

    Sean

Similar Threads

  1. Form Design Problem
    By davej in forum Alpha Five Version 6
    Replies: 3
    Last Post: 11-01-2005, 08:37 AM
  2. How to set border around label in form design?
    By Tom Patten in forum Alpha Five Version 5
    Replies: 1
    Last Post: 02-26-2004, 04:36 AM
  3. form design and missing set info
    By Patrick Browne in forum Alpha Five Version 5
    Replies: 5
    Last Post: 01-20-2004, 04:02 AM
  4. FORM DESIGN FOR INVOICE SET
    By Mikki Tavernit in forum Alpha Five Version 4
    Replies: 4
    Last Post: 02-28-2002, 05:11 AM
  5. Set Design Problem
    By Peter.Greulich in forum Alpha Five Version 4
    Replies: 7
    Last Post: 06-17-2001, 02:15 AM

Bookmarks

Posting Permissions

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