Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2012
    Posts
    4

    Question Unanswered: What is the best practice for access forms/how to enter form data properly

    Hey all,

    I am putting together an application for a friend and I've become stuck. I originally had the idea of doing this in the Android OS, however, we switched to access because my friend is a little more comfortable with windows.

    I've built the tables and some queries, however, I have hit a roadblock on the first form. At first I tried to layout my main form so that jobs would be down one side (selection subform) and then the details for the corresponding selected job would show on the right side of the screen (detail subform). I used two sub forms in one main form. The idea being when the user clicks the Quote Number in the selection subform, the detail subform will populate with the Quote information. This is similar to how an email client is laid out in android or windows with messages down the left side and the message itself appearing to the right. I could not figure out how to get this to work, so I switched up to my current form layout.

    My current problem is, I have created a form (frmQuote) with two subforms (sfrmQuoteMaterials, sfrmQuoteWorkmanship). I can't figure out how I should go about retrieving and setting the information. I have a query for each form. One for general quote info, one for materials, and one for workmanship. There is some VBA in the OnLoad events to hide the Identifiers.

    My question is, have I setup the form properly? I have a feeling I am doing something that I shouldn't be... When I try to add a new record to the quote form, I would expect it to let me enter information. Currently I get a system beep and it doesn't let me enter the information.

    I realize that "properly setup" is somewhat opinionated. I don't expect more than 1000 quotes and maybe a third in jobs. The database only has one user at the moment so I think an Access database should suffice; so long as I flush or archive the previous years data somehow.

    Should I be writing individual queries for each form or one super query for frmQuote and its subforms? I have a tendency to overcomplicate things so I'll stop here.

    Any help is appreciated and welcome. This is my first real database and I want it to be a good one. I have attached the database (for access 2010) in BestRoof.zip

    Thanks.
    Attached Files Attached Files
    Last edited by Payne530; 12-04-12 at 15:05. Reason: Question was a sentence.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Generally, you select the desired record on the form, and the subform(s) display(s) the associated records. I've not tried performing the selection in a subform, as that would require the selection to ripple up to the parent form first.

    Can you amend your design so that the selection is carried out on the main form?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Dec 2012
    Posts
    4
    Yes, I am going with a button based menu instead. theres an example of the layout in this cleaner version. sry for the mess.
    Attached Files Attached Files

  4. #4
    Join Date
    Dec 2012
    Posts
    4
    So I think I hav narrowed my problem down to the recordset type. I've changed the recordset type for frmQuote, but now the materials and workmanship subforms are no longer able to pull the required information.

    Should I take another look at the design/layout of the forms? I want the quote form to be one single form. I'd rather not have separate dialogs popup for material selection and workmanship. Ideally these should be implied by the quote number so that all the materials and workmanship selected are related to the quote.

    Maybe I am striving for something outside of Access's ability?
    Attached Files Attached Files
    Last edited by Payne530; 12-06-12 at 11:18. Reason: Attachment

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Payne530 View Post

    ...I have a tendency to overcomplicate things...
    If this isn't the understatement of the year, it is very, very, very close to the understatement of the year!

    We frequently talk, here, and on other Access forums, about Databases not being normalized enough, but occasionally we see apps that are over-normalized, and IMHO this is one of those!

    You have three separate Tables for storing a customer's street address; one each for street number, street name and street type! Unless you have an overwhelming need to be able to sort on a given street name, street number, these three components would normally be stored in a single Field, in a single Table:

    1234 Easy Street

    And unless a customer could possibly have more than one address (an uncommon event, for this type of scenario, if I'm reading it correctly, but it can happen) this single Field would be in the Customer Table.

    In MS Access, Subformsare usually used if there is a One-to-Many Relationship between related Tables, i.e. if a given Record in one Table can have multiple Values for a single Field.

    A simplified example would be something like this, where customers can place multiple orders, and each order can have multiple items:

    CustomerTable
    CustomerID 'Primary Key
    CustomerFirstName
    CustomerLastName
    CustomerHomePhone
    Customer CellPhone
    CustomerStreetAddress
    CustomerCity
    CustomerPostalCode
    ...and so forth

    OrdersTable
    OrdersID 'Primary Key
    CustomerID 'Foreign Key
    OrderDate
    MethodOfPayment
    ...and so forth

    OrderItemsTable
    OrderItemsID 'Primary Key
    OrdersID 'Foreign Key
    ItemName
    ItemQuantity
    ItemCostPerUnit
    ...and so forth

    Notice that the Tables 'cascade'
    • The Primary Key of the first Table becomes the Foreign Key of the second Table
    • The Primary Key of the second Table then becomes the Foreign Key of the third Table


    To display all of these in one Form and to be able to enter New Records or Edit Existing Records in all of these Tables:
    • The Main Form would be based on the CustomerTable
    • The first Subform would be based on the OrdersTable and linked to the Main Form by the CustomerID
    • The second Subform would be based on the OrderItemsTable and linked to the first Subform by the OrdersID

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Tags for this Thread

Posting Permissions

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