Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Update a table

Discussion in 'Information Technology' started by lemonhead, Jul 28, 2006.

Tags:
  1. lemonhead

    lemonhead
    Expand Collapse
    Guest

    My table is not updating with the information from my form. What am I
    missing? I have a combo box where a user selects a doll. Once the doll is
    selected, a text box displays the doll's number from a query. The doll's
    number is not updating to the table. Help!
     
  2. Loading...


  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 10 May 2006 11:23:02 -0700, lemonhead
    <lemonhead@discussions.microsoft.com> wrote:

    >My table is not updating with the information from my form. What am I
    >missing? I have a combo box where a user selects a doll. Once the doll is
    >selected, a text box displays the doll's number from a query. The doll's
    >number is not updating to the table. Help!


    Bear in mind: You can see your form, and you know what's in your
    database. We do not.

    What is the Recordsource property of the form? What is the Control
    Source of this combo box? What is the Control Source of the textbox?
    What do you expect to be updated, where?

    John W. Vinson[MVP]
     
  4. schasteen

    schasteen
    Expand Collapse
    Guest

    Make sure the textbox control source is set to the appropriate field of you
    table. This assumes the forms record source is set to the table or a query.

    "lemonhead" wrote:

    > My table is not updating with the information from my form. What am I
    > missing? I have a combo box where a user selects a doll. Once the doll is
    > selected, a text box displays the doll's number from a query. The doll's
    > number is not updating to the table. Help!
     
  5. lemonhead

    lemonhead
    Expand Collapse
    Guest

    The record source property of the form is "measurement table"
    The control source of the combo box is "dollname1"
    The control source of the text box is "=[dollname1].column(1)"
    I would like the text box to update to the "measurement table"

    "John Vinson" wrote:

    > On Wed, 10 May 2006 11:23:02 -0700, lemonhead
    > <lemonhead@discussions.microsoft.com> wrote:
    >
    > >My table is not updating with the information from my form. What am I
    > >missing? I have a combo box where a user selects a doll. Once the doll is
    > >selected, a text box displays the doll's number from a query. The doll's
    > >number is not updating to the table. Help!

    >
    > Bear in mind: You can see your form, and you know what's in your
    > database. We do not.
    >
    > What is the Recordsource property of the form? What is the Control
    > Source of this combo box? What is the Control Source of the textbox?
    > What do you expect to be updated, where?
    >
    > John W. Vinson[MVP]
    >
     
  6. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 10 May 2006 14:12:02 -0700, lemonhead
    <lemonhead@discussions.microsoft.com> wrote:

    >The record source property of the form is "measurement table"
    >The control source of the combo box is "dollname1"
    >The control source of the text box is "=[dollname1].column(1)"
    >I would like the text box to update to the "measurement table"


    Why?

    There is NO point to storing this information redundantly in a second
    table. If you have the doll name in the Dolls table, and the doll ID
    (the bound column) of the combo box in the Measurement table, you can
    always do a Query joining the two tables to display the name.

    Where is the doll name coming from? Why is there a field named
    dollname1 - that makes me very concerned that there are fields
    dollname2 and dollname3, a violation of normal form; are there?


    John W. Vinson[MVP]
     
  7. lemonhead

    lemonhead
    Expand Collapse
    Guest

    You raise an interesting point of view. Yes, there is dollname2-dollname12.
    This is for a show where there are many entries. The user inputs other data
    such as size, style, etc. All of the user data is stored in the table. What
    I need is to print a report of all of this information to include the doll
    name and number. I may have erroneously thought that if the table wasn't
    updating, then the report wouldn't print it-which it isn't. The doll name
    is coming from the dolls table. Am I going about this the wrong way if its a
    violation of normal form?

    "John Vinson" wrote:

    > On Wed, 10 May 2006 14:12:02 -0700, lemonhead
    > <lemonhead@discussions.microsoft.com> wrote:
    >
    > >The record source property of the form is "measurement table"
    > >The control source of the combo box is "dollname1"
    > >The control source of the text box is "=[dollname1].column(1)"
    > >I would like the text box to update to the "measurement table"

    >
    > Why?
    >
    > There is NO point to storing this information redundantly in a second
    > table. If you have the doll name in the Dolls table, and the doll ID
    > (the bound column) of the combo box in the Measurement table, you can
    > always do a Query joining the two tables to display the name.
    >
    > Where is the doll name coming from? Why is there a field named
    > dollname1 - that makes me very concerned that there are fields
    > dollname2 and dollname3, a violation of normal form; are there?
    >
    >
    > John W. Vinson[MVP]
    >
    >
     
  8. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Thu, 11 May 2006 08:14:02 -0700, lemonhead
    <lemonhead@discussions.microsoft.com> wrote:

    >You raise an interesting point of view. Yes, there is dollname2-dollname12.


    Then you should consider properly normalizing your tables. If you have
    a many (shows? entries?) to many (dolls) relationship, you DON'T
    create multiple fields! "Fields are expensive, records are cheap".

    Instead, you use Access as a relational database: THREE tables:

    Entries
    EntryID
    <information about the entry as a whole>

    Dolls
    DollID
    DollName
    <information about the doll as an object>

    DollsEntered
    EntryID
    DollID

    If one Entry consists of eight dolls, there would be eight records in
    the DollsEntered table, one for each. You'ld display this on a
    Subform.

    >This is for a show where there are many entries. The user inputs other data
    >such as size, style, etc. All of the user data is stored in the table.


    You say "the table". Does your database in fact contain ONLY ONE
    TABLE? If so, you're not taking advantage of any of the power of
    Access!

    >What I need is to print a report of all of this information to include the doll
    >name and number. I may have erroneously thought that if the table wasn't
    >updating, then the report wouldn't print it-which it isn't. The doll name
    >is coming from the dolls table. Am I going about this the wrong way if its a
    >violation of normal form?


    Yes.

    For one thing, your assumption that you must have everything in one
    table in order to create a Report is a common error - but it *is* an
    error. You can, and should, base your Report *ON A QUERY* joining the
    tables (plural) which contain the information that you want reported.


    John W. Vinson[MVP]
     
  9. lemonhead

    lemonhead
    Expand Collapse
    Guest

    Yes, there are many tables. I have tables for dolls, owners, judges,
    measurements, etc.

    The measurement table is organized by show. Each show will have up to a
    dozen dolls. Each doll has a name, number, and a size. The name is a combo
    box which is pulling from a query(doll name/number) which is pulling from the
    dolls table. The doll number is pulling from the doll name/number query.
    The size is user entered information. Each show requires user entry of
    show#, date, etc. Each show is a record in the measurement table. I need to
    print a report for a specific show. Let's say there was a show on May 11,
    2006. This show had 6 dolls. I need to know what dolls, their numbers and
    their sizes were at that show.

    If I were to utilize multiple tables for this information, how do I know
    what dolls were at each show? If my report pulls the dolls from the dolls
    table and the doll's number (also from the dolls table), and the size from
    the measurement table, how do I know what dolls were at each show? Wouldn't
    that information be stored on the measurement table? Yes, it is necessary to
    have the doll's name & number on the report for each show.

    I admit that I am having trouble wrapping my mind around the "normalizing
    your tables" portion of your response. I am not sure you answered my
    question based on the last two comments you made. Please correct me if I am
    wrong. Don't give up on me yet. I do appreciate your help and time.

    "John Vinson" wrote:

    > On Thu, 11 May 2006 08:14:02 -0700, lemonhead
    > <lemonhead@discussions.microsoft.com> wrote:
    >
    > >You raise an interesting point of view. Yes, there is dollname2-dollname12.

    >
    > Then you should consider properly normalizing your tables. If you have
    > a many (shows? entries?) to many (dolls) relationship, you DON'T
    > create multiple fields! "Fields are expensive, records are cheap".
    >
    > Instead, you use Access as a relational database: THREE tables:
    >
    > Entries
    > EntryID
    > <information about the entry as a whole>
    >
    > Dolls
    > DollID
    > DollName
    > <information about the doll as an object>
    >
    > DollsEntered
    > EntryID
    > DollID
    >
    > If one Entry consists of eight dolls, there would be eight records in
    > the DollsEntered table, one for each. You'ld display this on a
    > Subform.
    >
    > >This is for a show where there are many entries. The user inputs other data
    > >such as size, style, etc. All of the user data is stored in the table.

    >
    > You say "the table". Does your database in fact contain ONLY ONE
    > TABLE? If so, you're not taking advantage of any of the power of
    > Access!
    >
    > >What I need is to print a report of all of this information to include the doll
    > >name and number. I may have erroneously thought that if the table wasn't
    > >updating, then the report wouldn't print it-which it isn't. The doll name
    > >is coming from the dolls table. Am I going about this the wrong way if its a
    > >violation of normal form?

    >
    > Yes.
    >
    > For one thing, your assumption that you must have everything in one
    > table in order to create a Report is a common error - but it *is* an
    > error. You can, and should, base your Report *ON A QUERY* joining the
    > tables (plural) which contain the information that you want reported.
    >
    >
    > John W. Vinson[MVP]
    >
     
  10. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Thu, 11 May 2006 14:23:02 -0700, lemonhead
    <lemonhead@discussions.microsoft.com> wrote:

    >Yes, there are many tables. I have tables for dolls, owners, judges,
    >measurements, etc.
    >
    >The measurement table is organized by show. Each show will have up to a
    >dozen dolls. Each doll has a name, number, and a size. The name is a combo
    >box which is pulling from a query(doll name/number) which is pulling from the
    >dolls table. The doll number is pulling from the doll name/number query.
    >The size is user entered information. Each show requires user entry of
    >show#, date, etc. Each show is a record in the measurement table. I need to
    >print a report for a specific show. Let's say there was a show on May 11,
    >2006. This show had 6 dolls. I need to know what dolls, their numbers and
    >their sizes were at that show.
    >
    >If I were to utilize multiple tables for this information, how do I know
    >what dolls were at each show? If my report pulls the dolls from the dolls
    >table and the doll's number (also from the dolls table), and the size from
    >the measurement table, how do I know what dolls were at each show? Wouldn't
    >that information be stored on the measurement table? Yes, it is necessary to
    >have the doll's name & number on the report for each show.
    >
    >I admit that I am having trouble wrapping my mind around the "normalizing
    >your tables" portion of your response. I am not sure you answered my
    >question based on the last two comments you made. Please correct me if I am
    >wrong. Don't give up on me yet. I do appreciate your help and time.


    If "Each show will have up to a dozen dolls" and "each doll may appear
    in more than one show" then your table design does need to be changed,
    as I'm suggesting.

    Rather than having twelve fields for each show, you *need another
    table* - let's call it DollsInShow.

    This table would have (at least) two fields: the show date, or better
    the unique ID of the Shows table, to indicate which show this doll is
    in; and the DollID to indicate which doll is in this show.

    You do NOT need to or want to store the size, the name, or any other
    information from the Dolls table in this table, or in the show table.
    Instead, you would base your Report on a query joining *all three*
    tables. You'ld pick the show date, venue, etc. from the Shows table,
    and the doll name, size, etc. from the Dolls table; only those dolls
    actually at this show will be included.

    I'm not clear though on what the "measurements" table contains - I may
    be wrong in assuming that it's static measurements of the doll. If it
    is, then that information should be in the Dolls table; if the doll is
    11" long it will stay 11" long whichever show it's in!

    John W. Vinson[MVP]
     

Share This Page