Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2012
    Posts
    21

    Unanswered: Possible Relationship/design problem - database attached

    I think my problems is with my relationships, or how the subform is set up. Basically, When i go to enter information in the order form (I will enter description first), it brings up a message which says cannot add record(s); join ket of table 'TblITemOrder' not in recordset.

    I think this might be becuase the ID (auto number) has not went in yet, becuase after the messege it lets me insert value. If I think go to add a quantity, it does not let me until I go off the record again, and back onto it.

    Finally, I need to add the Purchase order myself within the subform. In the form it generates automaticlly. Is there anyway of getting it to be entered automatically in the subreport?

    If you have any questions please ask, general feedback would also be greate to improve my database skills (the little that i already have)

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The Main Form holds the 'Parent' Records and the Subform holds the Child Records, and everyone knows that you cannot have a Child until you have a Parent!

    The Field that links the MainForm and Subform is an AutoNumber Field, in the Main Form's Table, and as you've guessed, it doesn't get populated until a Record is initiated in that Table. There's no reasonable way to get around that, and shouldn't be, to be honest.

    The Purchase Order isn't being automatically generated by the Main Form, Access is simply setting up a New Record to be added to the Subform. That's how a MainForm/Subform scenario works! If you don't enter any data into the Subform Record, nothing happens, i.e. no Record is added to the Subform's RecordSet.

    You don't enter Data in a Report, you Display/Print data using a Report. By the time you get to generating a Report, the Data in the Subform's RecordSet should appear in the SubReport; are you saying that this isn't happening?

    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

  3. #3
    Join Date
    Sep 2012
    Posts
    21
    Thanks for your response, I do understand what your saying.

    Any suggestions on what to do next, or what to do if I start over?

    Thanks

  4. #4
    Join Date
    Sep 2012
    Posts
    21
    Basically, I need to know what I need to do to be able to add a new order, and add multiple items to that order in one form.

    I can do it at the moment, but with lots of errors, and setting the focus off the current record, then back to the current record, which makes me think there is a big problem with either the design or the form, I have attached the database, could you please tell me what I need to do?

    Thanks

  5. #5
    Join Date
    Sep 2012
    Posts
    21
    Quote Originally Posted by Missinglinq View Post
    You don't enter Data in a Report, you Display/Print data using a Report. By the time you get to generating a Report, the Data in the Subform's RecordSet should appear in the SubReport; are you saying that this isn't happening?
    Linq ;0)>
    I'm really sorry, I actually ment the Purchase Order ID does not display in the subform, I need to type it in manually(Which will say this certin Item belongs to this order), but it shows fine in the form.

    Sorry about this

  6. #6
    Join Date
    Sep 2012
    Posts
    21
    Problem Solved, I needed to change the relation type in my query. I found this useful on the internet:

    When you create a query that is based on a 1-many join, you need the join key field to be selected from the many-side table rather than the 1-side table. Therefore, replace [Service].[ServiceTicket] with [ServiceNotes].[ServiceTicket]

Posting Permissions

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