Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form/Subform Question (Win/Office XP)

    Hello,

    The Fact:s:
    I have 3 tables (TBLweek_of, TBLCategory, TBLTotals). TBLWeek_of and TBLCategory are reference tables with only one field as primary key, since each week is unique and each category is unique.

    TBLTotals is a transaction table with 3 fields: Week_of, Category, and Totals.

    Week_of and Category together make up the primary (foreign) key to avoid duplicates. This is all working beautifully.

    Problem:
    I want to create a form/subform so that when I create a new week_of, I don't have to pick the week each time from the combo box when I add totals to each category. I can't seem to get it work.

    Question:
    How do you get a form to use data from a (reference) table and dump a new record into another (transaction) table?

    Any help would be greatly appreciated.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Form/Subform Question (Win/Office XP)

    As you suggested in your subject, this is a form/subform situation. The form would have the Week_of specified somewhere. The subform would be based on your tblTotals, but it only needs to include 2 controls: 1 for category the other for the Total. When you add a subform to a form, Access will suggest how it should link them if it finds common fields names. Otherwise, you will have to manually define the Master and Child Linking fields, which are properties of the subform control (not the subform itself) that sits on the mainform. Look in Northwinds for examples of forms with subforms. The subform will only display records out of tblTotals for the Week_of specified on the Main form. And when you add new records to tblTotals via the subform, they automatically get added for the Week_of.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form/Subform Question (Win/Office XP)

    Thank you for your quick response. I did what you suggested (linking) but because my totals is a number field and my week_of field is text, it gives me a "data type" error message, and tells me that it will ignore any unlinked fields. So I'm stuck. I've studied the Northwind, but I can't zero in on an example that I can understand. Other tables are involved and can't make a mental connection. Just to let you know...TBLWeek_Of - data type - text. I did this to match with Category data type (text) in order to link the two. Also, I'll never do any date calculations, so text will work fine. (Unless you can point out any deeper ramifications that I'm not aware of).

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Form/Subform Question (Win/Office XP)

    I think you have misunderstood the "linking" fields. These are the fields that are common in both tables. In the situation you described that would be the Week_of fields. In the subform, you would see all the records for that particular week_of, with each record showing the Category and Total.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form/Subform Question (Win/Office XP)

    Mark,

    Drinks are on me!!! Take your points. I got it to work and am feeling 2 months of weight off my back. I started this August 11 and can now put it to bed!

    -Gene

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Form/Subform Question (Win/Office XP)

    Glad you got it going!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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