Results 1 to 7 of 7
  1. #1
    The Professor is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2012
    Location
    Cambridgeshire, UK
    Posts
    67

    Goto Record Problem

    I have two tables: Customers and Invoices, linked on a one-to-many relationship, using the unquie "CustomerID field. The Invoice table is displayed in a Subform on the Customer (parent) Form. The Invoice table also has it's own unquie key field, "InvNumber".

    The challange I have is that on the Invoice Subform, I want to be able to goto the Record matching a user chosen InvNumber, which they can select from a dropdown list. But that invNumber may belong to a different customer than the one currently being dispalyed on the parent, Customer Form. So the problem I have is, how, from the Invoice Subform, do I change the record on the (parent) Customer Form (using the CustomerID field) and then change the record on the Invoice Subform (using the InvNumber key field). Which would be the best method to use? If anyone can help, I'd be grateful. Thanks.

  2. #2
    alcapps is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    292
    usually you tie them together using the mater and child links in the subform object.

  3. #3
    The Professor is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2012
    Location
    Cambridgeshire, UK
    Posts
    67
    Hi,
    Thanks for your reply, but off the top of my head, I'm not sure what the mater and child links are, or where I'd find them - are they in property sheet?

  4. #4
    alcapps is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    292
    go to the subform object. it has those properties..

  5. #5
    The Professor is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2012
    Location
    Cambridgeshire, UK
    Posts
    67
    Hi alcapps,

    Thanks for your reply and I believe I now understand what you're saying - but the underlying tables are already fully and properly linked. The Master-Child relationship, if you will, is already set. As I mentioned in my first post they already have a one-to-many relationship, and this is working well for most purposes: one "CustomerID" can have many "InvNumbers" but of course not the other way round.

    My problem is this, whilst active in the subform (Invoice table) how do I to change the current record to display a record (i.e. invoice) that doesn't belong (i.e. not related) to the current Customer in the Master Form? I'd have to first change that Customer record to display the Customer to whom the Invoice (that I want to disply in the subform) is related. I apologise if this is long-winded and confusing, but I'm trying to be as clear as I can about what I'm wanting to do. I want the user to be able to go to any Record based on the InvNumber field. But if that Invoice/Record is not related to the Customer Record curently being displayed in the Master/Parent Form, I have a tricky problem to solve - how do I change that Customer Record?

    Thinking about it some more - should be looking to put some code in the After-Update Event of the combobox (on the subform) from which the user chooses the InvNumber? I'm wondering if the DoCmd.FindRecord method might work, I'm not too familiar with it - does it support the kind of search criteria I'll be needing?

  6. #6
    alcapps is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    292
    Yes you could put a customer combo box in the header of the form then after update event change the form to point to that customer and requery the form and your subforms will be pointing to that customer.

  7. #7
    The Professor is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2012
    Location
    Cambridgeshire, UK
    Posts
    67
    I am currently pursuing this issue further in the Programming forum under the heading: "Code executing without error, but not working"

    Prof.

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

Similar Threads

  1. Goto Record when Duplicate Record Exists
    By rlsublime in forum Programming
    Replies: 13
    Last Post: 03-22-2012, 03:46 PM
  2. Goto Record
    By jgalloway in forum Forms
    Replies: 8
    Last Post: 09-25-2011, 08:03 AM
  3. Goto record in subform - sometimes
    By RasterImage in forum Forms
    Replies: 6
    Last Post: 09-13-2011, 04:36 PM
  4. list box goto record issue
    By Madmax in forum Access
    Replies: 2
    Last Post: 07-07-2011, 06:17 AM
  5. Goto a new record in tabbed subform
    By snoopy2003 in forum Programming
    Replies: 3
    Last Post: 03-05-2011, 04:24 PM

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