Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2002
    Location
    Vancouver
    Posts
    7

    Question Unanswered: How can I select records with a combo box?

    I have a main form frmPurchaseOrder and a subform sfrmPoItems. The main form is based on a query with two tables: tblPurchaseOrders and tblSuppliers and the query for the subform has 4 tables: tblParts, tblCurrencies, tblPurchaseOrders and tblInventoryTx. The subform Link Child Fields is set to PoID (from the tblInventoryTx) and Link Master Fields is set to PurchaseOrderID (from the tblPurchaseOrders).

    I would like to let the user choose which Purchase Order to view by selecting the PurchaseOrderID from a Combo Box on the main form. Is this possible to do? I've added a combo box and it faithfilly shows the purchase order number I've chosen, but it doesn't go to that record with its related subform records.

    I'd appreciate your help.

    Thanks.

    Bill Gannon

  2. #2
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    add this code to the change event of your combo box

    Dim strSearch As String
    strSearch = Me.ComboBox 'whatever the name of your combo is
    Me.PurchaseOrderID.SetFocus
    DoCmd.FindRecord strSearch, acEntire, , acSearchAll, , acCurrent


    Hope this helps....

  3. #3
    Join Date
    Jan 2002
    Location
    Vancouver
    Posts
    7
    Thanks. I can see it is trying to work, But when it gets to the DoCmd line of code, I get a Run-time error 3200 "The record cannot be deleted or changed because tblInventoryTx includes related records".

  4. #4
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    one last reply before the weekend starts....

    I can't help you there, it works smoothly in my application, and I have a subform as well.

    There are other ways. Perhaps you can filter the records of your main form.

  5. #5
    Join Date
    Jan 2002
    Location
    Vancouver
    Posts
    7
    Is it because I have set a relationship between PurchaseOrderID in my tblPurchaseOrders and PoID in tblInventoryTx with Enforce Referential Integrity checked? I have tried Cascade update related fields on and off, but that doesn't help.

    Have a great weekend.

  6. #6
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    That should not make a difference. But it doesn't hurt to try to turn your ref. integrity off and try it again.

    This is lousy advise, but sometimes you just need to re-create your main form with the subform. Hopefully it is an easy form. Otherwise you can just use wizards and don't worry about the layout... just to try.

    I don't recognize the error. And you are not deleting any records and I don't think you are changing anything either.

  7. #7
    Join Date
    Jan 2002
    Location
    Vancouver
    Posts
    7
    Thanks again.

    I deleted the relationship ...different error: The changes were not successful because they would create duplicate values.....

    I also created the 2 forms from scratch and got the same results, with and withour enforcing referential integrity. It appears to work BUT then I noticed that when I select PurchaseOrderID number 3 from the combo box, the main form is stays on PurchaseOrderID number 1 and the subform shows the records for PoID number 3. So now, at least I know the error messages are correct.

    What I need to do is get the main form and the subform to move to the Purchase Order that the user selects from the combo box.

  8. #8
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    If your child/master link between main and subform is set up correctly it should be synchronised.

  9. #9
    Join Date
    Jan 2002
    Location
    Vancouver
    Posts
    7
    It's working now

    I had incorrectly bound my combo box to the PurchaseOrderID field of the query I changed it to unbound, looking up the PurchaseOrderIDs in the tblPurchaseOrders. That, along with your code, makes it work beautifully.

    Thanks again for your help!

  10. #10
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    Great.. Glad I was able to help.

    It is sometimes frustrating to struggle so long to find out it was a simple error somewhere in the procedure.

Posting Permissions

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