Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2014
    Posts
    9

    Unanswered: Form Wizard Trouble

    Hello all I am attaching a file which has exports from my database.

    The problem is, I created a Form using the wizard for the Table 07CTR Hours and when the form was created for some reason all but a few of the boxes were combo boxes. They were assigned Currency format on the table with no lookups so I don't understand why they are appearing as combo boxes.

    I looked myself for any differences in the properties of each field in the table but couldn't find anything.

    I am not just posting this to get someone to do it for me. I am developing my skills on access for work. While I appreciated a solution being found I would like to know how it happened so as to not experience this problem again and learn from my mistakes.

    Thank you for taking time to review my problem

    intern91
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    just change the control from a combo/list box to a text box control

    open the form in design in design mode
    click the control(s)
    right click properties
    select change to, and select text box


    ...as to why its done this who knows. there is probably some residual crud. doing a compact and repair didn't seem to 'fix' the problem

    ...,but having said that any form with 100+ control is going to be a frightmare. are you absolutley certain you 'need' 100 controls which on the face of it all seem to have similar data
    are you certain your design is normalised
    I'd rather be riding on the Tiger 800 or the Norton

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

    ...as to why its done this who knows. there is probably some residual crud...

    ...are you certain your design is normalised...
    Actually, testing, i.e. creating a new Form with the Wizard and selecting a Form Layout that produces a Single View Form always appears to exhibit this behavior, with Currency Datatypes. I even tried reducing the number of Fields used to a reasonable 25 and it made no difference. Even deleting 75 of the Records in the Table didn't help...same behavior.

    On the other hand, creating a new database, with a Table with a number of Fields, including some with the Currency Datatype, doesn't produce this behavior, which leads me to think that the problem really centers around the 100 Fields we see, here. I think that it has somehow overwhelmed the Access Gnomes! The Forms Wizard couldn't even create Forms with some of the available Layouts because of the number of Fields.

    I think that Normalization has to be the issue, here...I can't imagine any situation where you'd have 100 Fields in a single Table, and still be normalized, especially given the names of the Fields, here, i.e. CTR1, CTR2, CTR3...CTR99. Either of of these things, alone, almost always point to a lack of normalization.

    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

  4. #4
    Join Date
    Aug 2014
    Posts
    9
    Thanks for the responses, I will have another look at the fields and try to fix it with your advice.

    CTR refers to cost time resource, So each CTR is referring to different costs of areas for a given project.

    Thanks
    intern91

    Edit: I deleted the Table and Form, and created a new table with field names of CTRHours # and it has resolved the problem, it has fixed the problem and now my form is showing currency without the drop down. I will have to think twice about how I name my fields in the future.
    Last edited by intern91; 09-01-14 at 05:38. Reason: Update

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so push them down into another table....
    table projects
    ID
    ...any other stuff

    CTRData
    FinancialYear
    FinancialPeriod
    CTRCode
    Amount
    the PK is a composite of FinancialYear, FinancialPeriod & CTRCode, so that guarantees you cannot have more than one CTR amount for the same project year and period
    In guessing that you have called table 07CTR becuase thats a period or possibly project ID

    if you want have another table to hold descriptions of what CTRXXX is. one of the reasons for doing this is that you can then enforce data integrity.. so someone can't enter invalid CTR codes.... ferinstance CTR1OO as opposed to CTR100. it will also help with query design, if as and when you want to recreate the spreadsheet layout at a later date
    table CTRCodes
    Code
    Description

    if you are feeling chipper have a table for dates
    table FinancialPeriods
    FYear
    FPeriod
    StartsOn
    ..then use the FYear & FPeriod as foreign keys in your CTRData table

    another good reason. say the idiots, sorry accountants, want to look at CTR85 over several months. in your way its a pig to writes the query, the normalised way its easy to write a the query, actually its easy enough to write some VBA code that will allow them to specify what ever they want in a report.

    Another reason is if say the morons, sorry accountants magically decide to add more CTR codes then in your model it requires a code redesign, in the proposed model they the users add whatever codes they want and "jobs a goodun". so you don't get coerced into dong last minute code changes up against an artificial deadline imposed by accountants whose whole outlook on life is what happened last year, not what is happening right now or about to happen.

    I would still use the CTRXXX as the code, rather than use an autonumber key.

    another suggestion bione up on reserved words (words or symbols that you should not use) in Access. also develop or adopt a naming convention. doesnt' matter if its CamelCase or underscores to separate words
    eg
    TelephoneNumber
    or
    telephone_number
    using a naming convention normally you'd contract common words eg TelephoneNumber could become TelNo, but don;'t become too obscure or obtuse, it should still be legible to you, who follows you and ideally anybody who has a legitimate reason to view the table and or column names
    don't repeat the table name in the columns of that table, but by all means refer to the parent table where that value is the primary key
    table names plural where appriopriate
    eg
    table DTRCodes
    Code PK

    table DTRData
    FYear
    FPeriod
    CTRCode FK refers to Code in DTRCodes
    ..this is more about legibility and style than must do, but I'd argue its bleedign obvious that Code inside DTRCodes refers to a DTR Code, and calling the same data DTRCode in DTRData makes it equally obvious thats where the FK is.....

    if you are designing relational table, always enforce relationships with RI on, the column must be the same size and datatype.

    but don't use spaces in table or column names.

    And before anyone asks, I do have a soft spot for Accountants....



    ...its that large patch of quicksand on Crosby beach, near the coastguard building

    also if you are ever tempted to store dates as a string......
    ....don't
    Last edited by healdem; 09-01-14 at 05:50.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Aug 2014
    Posts
    9
    Wow most clear and concise advice I have received on a forum post, I am new to Access so I will be looking into this a lot, Thank You. I am on placement so will need to talk to someone who understands the CTR and can advise me on how I can adjust them so there isn't 100 fields in the table. At the moment I think we are seeing if a set of excel documents can be turned into an access database.

    There will be a lot of fine tuning.

    Thank you very much for your help today.

    intern91

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    we aim to please

    spreadsheets are a way to analyse data, they should not be used for storing data.

    don't try and use the spreadsheet approach in a database
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Aug 2014
    Posts
    9
    I did that on my first attempt and it completely backfired. So I started from scratch using a template as a reference. So far I believe I have done the bases of the database in reference to the template. Just trying to learn and figure out how they did the end form and user prompts. But what else is an intern for =]

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
  •