Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jan 2006
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Not in the list (Access 2000)

    I have the following problem.I have a form and a subform based on a query,and this query does not allow the choice of other products.And it is quite logical, since the parameter is (SELECT Max([orderid]) FROM orders).
    Naturally no other product can be chosen. I am wondering can i find a trick with both to have the cake and to eat it.I mean to preserve the condition and at the same time to be able to choose and add other products from the table products.Shall i change the source or use some NotIn the List methods?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not in the list (Access 2000)

    I don't understand what you want to do. Your main form doesn't contain any controls except for the subform, and the main form and subform have basically the same record source.

    The record source limits the OrderID to the highest available OrderID, but it allows you to select different products - I can select them from the combo box in your subform. So I don't see the problem you mention.

  3. #3
    Lounger
    Join Date
    Jan 2006
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in the list (Access 2000)

    Thank you for the reply.Mt problem is the following.If you open the subform and try to choose say the fourth product,when closing the form you will get the following message:
    "The Microsoft Jet database engine cannot find a record in the table orders with key matching fieldss 'orderid' My question is can i make the subform accept also edditing and adding new products under the same order number ? In my example, the last order is 5791 and consists of two products: First Products and Third Products.I want to add to this order say Fourth Products.But my form does not allow it.Can i make it accepting editing of the order?

    Canett

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not in the list (Access 2000)

    I cannot reproduce the problem in the database you posted. I can add a record to the subform and select Fourth Product without problem. After closing the form, the new record has been saved correctly, without error messages. Perhaps the version of the database that you posted is different from the original?

  5. #5
    Lounger
    Join Date
    Jan 2006
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in the list (Access 2000)

    Thank you.No,it is the same database.But i have found out,that if i write manually the order number at the right hand of the row, then the form accepts my fourth product.In this case i had to write 5791 in the right box.Since this is the case perhaps there is a way to automate the process? Somehoe the number 5791 to appear in default?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not in the list (Access 2000)

    In the database you attached this time, there is no main form.

    If you use frmSubform as a subform, the OrderID will be populated from the main form automatically. No extra properties or code necessary.

    If you want to use frmSubform as a stand-alone form, you can set the Default Value property of the OrderID control to
    <code>
    =DMax("OrderID","Orders")
    </code>

    The highest OrderID will automatically be filled in in a new record.

  7. #7
    Lounger
    Join Date
    Jan 2006
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in the list (Access 2000)

    Thank you for the clever answer.I will start working on it now

    Canett

Posting Permissions

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