Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    subform auto update (Office XP)

    I have a form based on a table called Customer Invoices. On this form is a sub form based on a table called customer sales. The forms are named as per the table names.

    What I need to be able to do is enter a customer number on the main form and have the number transfered immediately to the customer number field on the sales sub form. I have tried writing the code and putting it into the after update/before update section of the sales sub form but I can't get it to work.

    Currently I am having to enter the customer number into the invoice form and the sales sub form, something I would rather not do.

    Can anyone provide any insight on this one?

    Much 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: subform auto update (Office XP)

    You don't have to write any code at all to do this. This is what Access does automatically when you link a subform to a mainform. If you already have the subform on the mainform, then look at the DATA tab on the property sheet for the subform control on the mainform. It contains the Master Linking Field (the field from your mainform recordsource) and the Child Linking Field (a field from your subform recordsource).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subform auto update (Office XP)

    Thanks for that

    I have placed the subform onto the main form which worked OK

    This solution hasn't worked as (I think) customer number in my subform is not a primary key. Up until now the only common data linkage between the two tables was an invoice number field. We are now no longer using this as we want to look at and enter sales by customer over a time period rather than an invoice number.

    Can I do a dlookup function based on the customer id in the main form?

    Thanks

  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: subform auto update (Office XP)

    Customer# does not have to be the primary key field. In fact, in most situations there is a one-to-many relationship between the table behind the mainform and the table behind the subform. So, you would have many records in the subform for the customer in the mainform. In these situations, you don't even need to display the customer# in the subform, this prevents a user from accidentally changing it.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subform auto update (Office XP)

    thanks. I have added the customer numbers to the table and can now use the link option.

    Thanks for the solution

Posting Permissions

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