Results 1 to 15 of 15
  1. #1
    AishlinnAnne is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Aug 2016
    Location
    Mass
    Posts
    52

    Question Why wont my form work?

    I work at an estimating company. I have been tasked with creating a new database in Access, which will allow easy tracking/reporting of Jobs (quote requests). Problem being, is that I am not very well-versed in Access, so I am kind of learning as I go.
    1. My tables consist of:
    -tblBranch (BranchID, BranchName, City, State, Zip, Phone, Fax)
    -tblManager (ManagerID, ManagerName, BranchID, BranchName, Phone, Email)
    -tblCustomer (CustomerID, ManagerID, ManagerName, CustomerName, Address, City, State, Zip, Phone)
    -tblBuyer (BuyerID, CustomerID, CustomerName, BuyerName)
    -tblPlanType (TypeID, PlanType)
    -tblState (StateID, StateName)
    -tblEstimator (EstimatorID, EstimatorName, City, State, Email)
    -tblJobDetails (Quote_PK, QuoteNum, InputDate, JobName, City, State, DueDate, PlanTypeID, BranchID, ManagerID, CustomerID, BuyerID, Estimator ID)
    -tblJobOptions (Options_PK, QuoteNum, Floors, OpenWeb, IJoist, Roofs, Walls, EWPFlush, EWPTouch, EWPEntire, Z4, TrusstoWall, NonTrussHW, FBlock) <<each of these are yes/no check boxes (aside from Options_PK & QuoteNum)


    2. I have created a query that binds tblJobDetails with tblJobOptions, labeled it qry_Jobs. I only chose to do it this way because everyone I have spoken to says it is better to create forms off of queries instead of directly from tables.


    3. I need to create a form as an easy way to add new jobs, so 'Data Entry' is set to YES in form properties. I used the Forms Wizard to create a form and set the ‘Record Source’ to qry_Jobs.
    This populated the following fields:
    1. QuoteID (textbox)
    2. InputDate (textbox)
    3. JobName (textbox)
    4. City (textbox)
    5. State (combo-box)
    6. PlanType (combo-box)
    7. BranchName (combo-box)
    8. ManagerName (combo-box)
    9. CustomerName (combo-box)
    10. BuyerName (combo-box)
    11. EstimatorName (combo-box)


    4. My goal right now is to get the BranchName, ManagerName, CustomerName & BuyerName to work as cascading combo-boxes:
    1. Select BranchName, populates list of Manager in that Branch in the ManagerName combo-box.


    2. Select ManagerName, that populates a list of Customers under that Manager in the CustomerName combo-box
    3. Select CustomerName, populates list of Buyers for that Customer in the BuyerName combo-box.
    4. Select the buyer for that customer.
    5. I would then like to insert a subform which will show all the checkbox options from tblJobOptions, so that I can assign Options to each job.


    ****I was able to get this exact form the way I wanted it, but I based it off of the Table tblJobDetails and the subform off of tblJobOptions. HOWEVER, people KEEP telling me that later on down the line, if I don’t create the form off of a query instead, that it is going to cause problems. So I am just trying to correct the problem now, before we even launch the database.


    PROBLEMS:
    1. On my original form that was based off of the table, everything works exactly as I want it to and all the data I entered went to all the right places. On this new form based off of the query, I can NOT for the life of me figure out why my combo boxes aren’t working. I have a feeling I am just telling it to pull from the wrong source or I have too many/not enough/incorrect relationships set up. I have tried so many ways to fix this but at this point I think I have just been confusing myself more and making it worse. PLEASE HELP!!!
    2. Should I create a separate query for JobDetails/JobOptions for when I am creating the subform for Options? Right now the query the form is based off of consists of all the data from both tables in one query.
    3. Can someone PLEASE explain to me, in English, not Access jargon (I am still learning), WHY it is so important to base forms off of queries in the first place?


    I am sure I have 1,000,000 more questions, but I think these are my main problems for the time being.
    Any help that you can provide would be EXTREMELY appreciated.
    ForumHELP.zip
    PS- I know I am asking for help and beggars can’t be choosers, but I would really like to understand how and why this whole database works. I am not looking for someone to quick fix it for me. So if you could please explain to me in as common-tongue language as possible, WHY it isn’t working and WHY whatever your suggestions are will help, that would make me VERY happy.


    I have changed some of the work-specific information and attached the database I am working with for reference.


    THANK YOUUUU!!!!!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you have some redundant fields (highlighted in red) which may be causing an issue

    -tblBranch (BranchID, BranchName, City, State, Zip, Phone, Fax)
    -tblManager (ManagerID, ManagerName, BranchID, BranchName, Phone, Email)
    -tblCustomer (CustomerID, ManagerID, ManagerName, CustomerName, Address, City, State, Zip, Phone)
    -tblBuyer (BuyerID, CustomerID, CustomerName, BuyerName)
    -tblPlanType (TypeID, PlanType)
    -tblState (StateID, StateName)
    -tblEstimator (EstimatorID, EstimatorName, City, State, Email)
    -tblJobDetails (Quote_PK, QuoteNum, InputDate, JobName, City, State, DueDate, PlanTypeID, BranchID, ManagerID, CustomerID, BuyerID, Estimator ID)
    -tblJobOptions (Options_PK, QuoteNum, Floors, OpenWeb, IJoist, Roofs, Walls, EWPFlush, EWPTouch, EWPEntire, Z4, TrusstoWall, NonTrussHW, FBlock) <<each of these are yes/no check boxes (aside from Options_PK & QuoteNum)

    and as I think has been mentioned in other posts, tblJobOptions is not normalised

    and I'm not sure what is the benefit of tblState since it does not seem to be referenced by any other table. If this is just to ensure accuracy of typing and populating specific states in a list or combo box, then you don't need the StateID field

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    The basic structure of your tables looks ok, there are a couple of things I would re-think though.

    The way you have it set up you can have multiple option (tblOption) records for each job (tblJobDetails). If that's the case, how do you differentiate the options? in other words what makes them unique. There is no description or anything in the tblOptions table to indicate why you are creating a second record for the same job. You should probably flesh out exactly how you go about your estimating process, or how you envision it. If there will only ever be *1* option record (tblOptions) for every job (tblJobDetails) your table would have a 1 to 1 relationship and I don't necessarily think the table should be split if that's the case.

    Second. You are not bound by using a query as a data source for a table, there is no 'better way' as far as bound forms go, nor do you have to link in all the extra tables, there's no reason to do it because you have combo boxes that display the descriptions of the items you're interested in.

    You don't actually have to have a data source at all, you can use UNBOUND controls. But there is a trade off.

    I do not write any databases using bound controls, I find them too hard to control properly but here's my pro and con list (someone else can add to it if they want)

    BOUND forms (forms tied directly to tables or queries)
    Pro - you don't have to create any special mechanism to add/remove/change data to your tables
    Pro - it's easier for newer folks to get handle on data entry/form building
    Pro - It is generally easier to get the basic setup correct

    Con - Building in prevention of incorrect data is harder
    Con - Changes are permanent as soon as you tab to a new field/record
    Con - You have to be creative in your error generation to capture the errors on your form

    UNBOUND forms (forms NOT tied to a data source)
    Pro - Nothing changes in your tables unless until/unless the user wants
    Pro - Data validation (making sure data is correct before it ever gets into your tables) is easier
    Pro - Error messaging is easier to handle
    Pro - Allows for more flexible forms (data from 'subform' related fields can be displayed anywhere on the form rather than within a limited window)

    Con - A bit harder for newer people to get up to speed with
    Con - You have to create a mechanism to add/delete/change data

    I will fiddle around with your database and show an unbound form example if you want something to examine.

  4. #4
    AishlinnAnne is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Aug 2016
    Location
    Mass
    Posts
    52
    your table would have a 1 to 1 relationship and I don't necessarily think the table should be split if that's the case.
    I had someone look at it before and i was told that since there was so much data, I would probably be better off splitting them into two tables, details & options. I am honestly just watching a lot of Lynda videos, googling a lot & asking people on the forum what I am doing wrong if I can't figure out the answer myself somehow.

    You don't actually have to have a data source at all, you can use UNBOUND controls. But there is a trade off.
    I tried preparing the form as unbound when I originally began this process, however I was finding it next to impossible to create the cascading combo-boxes.

    I will fiddle around with your database and show an unbound form example if you want something to examine.
    I would really appreciate that. I am trying to learn as much as I can. I don't want to rely on the internet to help me figure it out and then months later something goes wrong and I have NO idea what I am doing..

  5. #5
    AishlinnAnne is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Aug 2016
    Location
    Mass
    Posts
    52
    you have some redundant fields (highlighted in red) which may be causing an issue
    I actually just added those recently while I was fiddling around with the cascading combo-boxes. I was trying to see if there was a different way to connect the data, if there is, that wasn't it, haha.

    tblJobOptions is not normalised
    Could you please elaborate on that a little bit? I am not sure what you mean by "is not normalised."

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I am assuming from your response that your tables tblJobDetails and tblOptions have a one to one relationship?

  7. #7
    AishlinnAnne is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Aug 2016
    Location
    Mass
    Posts
    52
    Yes, there is a 1 to 1 relationship set up between tblJobDetails & tblOptions. There were 1 large table, but I was advised to split it up. I don't even remember the reason why to be honest.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I am not sure what you mean by "is not normalised."
    Your design is similar to the way you would do it in excel. The issue is what happens if you add another 'item' - with your present design you will need to change your table, then any dependant queries, forms and reports. In addition, certain queries will become difficult or impossible to write because you have effectively designed data into your table structure.

    You've also stated there is a 1 to 1 relationship set up between tblJobDetails & tblOptions (I presume you mean tblJobOptions)- which means there will only ever be one tblJobOptions record for each tblJobDetails

    So I would expect you would have a table called say

    tblItems
    ItemPK autonumber
    ItemDesc text

    and populated like this

    itemPK..ItemDesc
    1...........Floors
    2...........OpenWeb
    etc

    then your tblJobOptions would simply be
    OptionPK autonumber
    QuoteFK long - link back to quotePK in tblJobDetails
    ItemFK long - link back to itemPK in tblItems

    So if a record exists - that is the equivalent of your floors or whatever field being ticked, if it doesn't exist then that is the equivalent of your floors or whatever field not being ticked

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    AishlinnAnne.zip

    Here's a simple example of an unbound form. I didn't change anything about your other stuff but ajax raises a good point.

    What this looks like to me is you are, in this example database, indicating which items the customer needs an estimate for, and there are other, more detailed data collection items to give a more accurate quote. if that's the case the suggested setup by ajax is likely more what you want. In the database you provided there is no method of providing a quote (money or otherwise) based on the data I can only assume that's coming later.

    That being said, have a look and also consider your users. Using checkboxes is very inefficient in terms of data entry, it's more efficient to have a combo box with your possible responses (even yes/no) so the user's fingers never have to leave the keyboard to click on a box. Even though it's possible to trigger a checkbox with the spacebar most people don't know that and will use the mouse.

    For your example there is no need to separate the tables. If instead you want to record which areas the quote needs to cover, and in each area there's a list of materials that might be used you would want a structure more like

    Code:
    tblAreas
    A_ID  A_Desc
    1     Floors
    2     Open Web
    3     I Joist
    4     Roofs
    etc...
    
    tblJobDetailsAreas
    JDA_ID  Quote_PK  A_ID
    1       1         1
    2       1         3
    3       1         4
    
    tblMaterials
    M_ID  M_Desc
    1     2x4 8ft
    2     2x4 12ft
    etc...
    
    tblJobDetailAreasMaterials
    JDAM_ID  JDA_ID  M_ID  M_Qty
    1        2       1     24
    2        2       2     36
    etc..
    Then you could summarize all materials needed for the entire job and apply cost etc.

  10. #10
    AishlinnAnne is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Aug 2016
    Location
    Mass
    Posts
    52
    Quote Originally Posted by Ajax View Post
    So I would expect you would have a table called say

    tblItems
    ItemPK autonumber
    ItemDesc text

    and populated like this

    itemPK..ItemDesc
    1...........Floors
    2...........OpenWeb
    etc

    then your tblJobOptions would simply be
    OptionPK autonumber
    QuoteFK long - link back to quotePK in tblJobDetails
    ItemFK long - link back to itemPK in tblItems

    So if a record exists - that is the equivalent of your floors or whatever field being ticked, if it doesn't exist then that is the equivalent of your floors or whatever field not being ticked
    Maybe I just haven't had enough coffee this morning yet, but that thoroughly confused me..
    Right now I just have:
    tblJobDetails: (Quote_PK, QuoteID, Date, Job Name, Location, BranchID [tblBranch], ManagerID [tblManager], CustomerID [tblCustomer], BuyerID [tblBuyer] & EstimatorID [tblEstimator])
    tblJobOptions: (ID [PK], QuoteID [FK], Floors, Roofs, Walls, EWP, Z4, Truss, Flameblock) <<this is where I assign what products ("options") each job is requesting an estimate for.

    Could you please re-explain what/why I would need a tblItems also? Like I said, I think I'm just confusing myself at this early hour.

    Thank you!!!

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    hopefully rpeare's example will clarify it for you - what I called items, he called areas

  12. #12
    AishlinnAnne is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Aug 2016
    Location
    Mass
    Posts
    52
    Quote Originally Posted by rpeare View Post
    AishlinnAnne.zip

    Here's a simple example of an unbound form.
    - I have been taking a look, trying to re-create it on my own so that I know how it was put together. I appreciate you doing that for me.
    I do have one question though, so far, why are there 3 unbound textboxes next to my drop downs for Branch, Manager & Customer? I am not seeing any data tied to it or anything so I am just a little confused by it.
    I assume that it is some hidden way of cascading the boxes? But I am literally just guessing. If you could elaborate a little when you get a change I would REALLY appreciate it!!!


    Then you could summarize all materials needed for the entire job and apply cost etc.
    - Eventually, months maybe even a year or so down the line, my boss would like to find a way to get all the pricing data they have into an access spreadsheet.
    This way, when the estimators pick which options for each job, instead of just saying, "they want roofs & walls,"....
    If they select walls, then Access will ask, "what is the square footage, what thickness board are you going to use, etc.." to narrow down the cost of the actual product being used for the option they chose.
    However, as I said, that is not anywhere in the near future. My boss knows that I am literally teaching myself Access from the ground up so he is very patient and would rather me learn the basics and learn them well, before I attempt anything too crazy. I think the coding part of learning access is the most intimidating. But like I said, right now I am trying to become familiar/comfortable with basic form building. I will then move on to reports and hopefully once I do this enough times I will start to figure out whyyyy I chose those parameters or properties, whatever it is..

    Until then, I appreciate all the help I can get from you guys on this site. I would probably still be trying to look for the "create form" button if I were on my own, haha.

  13. #13
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if you look at the code driving the form in the ON EXIT property of each of those first three, you need to have some way of clearing out the data in the 'downstream' combo boxes and that was the simplest way I could think to handle it.

    In this case, if you select a new branch you want to set all manager, buyer, customer to null because they may not apply to the new branch choice, you can make the fields visible and put in breaks in the code (msgbox "Break here") to see what is happening as it cycles through the code (or use debug.print statements). What it amounts to is that when you ENTER the cascading combo box fields the 'current' value is stored in those text boxes, when you EXIT those combo boxes, if the value is different than when you started all the 'downstream' combo boxes have their value set to null and are requeried. If you DO NOT set the value to null you could conceivably end up with invalid combinations of options (branch, manager, buyer and customer).

  14. #14
    AishlinnAnne is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Aug 2016
    Location
    Mass
    Posts
    52
    Quote Originally Posted by rpeare View Post
    AishlinnAnne.zip

    Here's a simple example of an unbound form.
    I have been messing around, trying to re-create this on my own. Everything was going well until I hit the coding.
    I was looking at what you have in the attachment you sent and I just cant seem to follow. I don't even really know what the module is for either.
    I have my form set up, but nothing is connect, aside from the comboboxes.
    Also, I am not sure if I set up my buttons correctly either. I was hoping to just follow along and do what you did, and maybe learn from repeating your work but I dont see any forms or controls being referenced in the module thing so I can't even tell what that is for.

    I am really starting to think that this just ins't my forte. Which makes me sad, because I am a wiz at excel, so I was excited to learn access but this coding.. its like reading hieroglyphics.

  15. #15
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    the module 'globalfunctions' has most of the code that runs the form when you see something like

    adddata(me.name)

    That's calling a function in the globalfunctions module and constructing a SQL statement to add a record based on specific naming criteria that I tried to explain in all the text box/labels on the form itself. If you want to use those functions in your database, you just have to copy the module to your database.

    The code relies on:
    There must be a list box on the form where the BOUND COLUMN is the primary key field for the table where the data is stored
    The FORM and LIST BOX on the form must be identical to the table name, just substituting frm for tbl in the form name, substituting lst for tbl in the list box name
    The primary key field of the table is represented by a field named 'ID' on the form
    The NAME for the label attached to the ID field has a NAME ID_Caption
    The CAPTION property for the same control needs to contain the PRIMARY KEY FIELD NAME for the table involved
    All data entry fields must be named exactly as their corresponding field name in the table where the data is stored
    Any data entry field must begin with DE in the TAG property
    Any data entry field must have a data type (N for Number, T for Text, D for Date) in the tag field
    Any required field must have a _REQ in the tag

    i.e.
    DET_REQ in the TAG property indicates a data entry TEXT field that is required
    DET in the TAG property indicates a data entry TEXT field
    DEN in the TAG property indicates a data entry NUMERIC field
    and so on.

    Any REQUIRED field will, in the label attached to the field, have a name of FIELDNAME_CAPTION

    i.e.
    If the field in the table is named 'MyDescription'
    On the form the data entry field would be named 'MyDescription'
    On the form the LABEL attached to the data entry field would be named 'MyDescription_Caption'
    On the form the CAPTION of the LABEL would read whatever you wanted to say, just bear in mind the prompt for required fields will spit out whatever is in the CAPTION of the LABEL

    If you don't want to use the module you do not have to, I just wrote/use it because it makes creating data entry forms very easy. Also makes adding/removing data entry fields very easy once you get the hang of it.

    If you don't understand what stuff is doing you can put in debug.print statements (i.e. debug.print ssql) at various places in the code (even in the modules) to see what it's doing and how things are getting triggered.

    The code is not going to be easy to step right into, I will grant that, but if you are of a logical mindset you will be able to follow it given some time.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Insert into query that just wont work.
    By todmac in forum Queries
    Replies: 3
    Last Post: 11-26-2014, 03:19 PM
  2. Insert statement wont work
    By todmac in forum Access
    Replies: 1
    Last Post: 09-15-2013, 05:13 PM
  3. DoCmd.MoveSize wont work
    By gbmarlysis in forum Programming
    Replies: 3
    Last Post: 03-02-2012, 06:24 PM
  4. Form wont add data from a combo box
    By bopsgtir in forum Forms
    Replies: 9
    Last Post: 01-02-2011, 07:26 AM
  5. Query and subquery wont work with combo box
    By jbg8931 in forum Queries
    Replies: 0
    Last Post: 05-09-2010, 10:24 AM

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
  •  
Other Forums: Microsoft Office Forums