Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    NKB is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    30

    Updating a subform records based on a combo box selection in the Main form with VBA code. Not OK.

    I have a combo box (cboPurchaseID) in the main form that a user can select to display detailed line items for the Purchase ID selected in the main form. The subform name is:sbfPurchasesDetailsSubFormView.

    I have the following code for combo box afterupdate:

    Private Sub cboPurchaseID_AfterUpdate()


    strSQL = "SELECT tblPurchasesDetails.* FROM tblPurchasesDetails WHERE"
    strSQL = strSQL & " (((tblPurchasesDetails.PurchaseID)=" & Me![cboPurchaseID] & "))"
    Me!sbfPurchasesDetailsSubFormView.Form.RecordSourc e = strSQL


    Me.sbfPurchasesDetailsSubFormView.Requery

    End sub

    IT IS NOT WORKING, PLEASE HELP!

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Have you used debug.print strsql after you've constructed it.

    In the immediate window you ll see if you've missed anything and can even copy and paste it into a query designer sql view to see if it produces anything.

    Is there a space between where and (((

  3. #3
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    (I'm assuming your purchaseid is numeric and your cob is bound to the id column )

  4. #4
    NKB is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    30
    Yes, Andy49. I used the debug.print, and it is all fine, no extra character added, no ")" missed!!. It is still not working. Please help.
    Last edited by NKB; 05-27-2017 at 02:32 AM. Reason: more elaboration

  5. #5
    NKB is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    30
    Quote Originally Posted by andy49 View Post
    (I'm assuming your purchaseid is numeric and your cob is bound to the id column )
    Yes, andy49. I can see that my cboPurchaseID is shown (with changing ID every time I change the combo box selection) in the debug.print in the strSQL string.

  6. #6
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Try me.sbf.form.requery

  7. #7
    NKB is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    30
    Quote Originally Posted by andy49 View Post
    Try me.sbf.form.requery
    I tried it! It is not working!

    Is there any thing I have to do with the VBA setting. I mean references?? ADO and all that stuff?

  8. #8
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Nothing special for this. Did you get any data when you ran your sql in a query window

  9. #9
    NKB is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    30
    Here is my VBA references just in case somebody asks.


    Click image for larger version. 

Name:	Untitled.png 
Views:	14 
Size:	22.5 KB 
ID:	28898

  10. #10
    NKB is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    30
    Quote Originally Posted by andy49 View Post
    Nothing special for this. Did you get any data when you ran your sql in a query window
    Yes I ran it in query window and it is working OK. Only in afterupdate it is not working. I think it has to do with recordsource setting!!

  11. #11
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Are your parent and child fields set properly in the parent form and subform. (In properties)

  12. #12
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Otherwise is it possible to put a version of your db on here? Maybe with made up data.

  13. #13
    NKB is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    30
    Quote Originally Posted by andy49 View Post
    Are your parent and child fields set properly in the parent form and subform. (In properties)
    Yes. Actually the form and subform work perfectly using a macro (built by the wizard). But I want to use event procedures instead.

  14. #14
    NKB is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    30
    Quote Originally Posted by andy49 View Post
    Otherwise is it possible to put a version of your db on here? Maybe with made up data.
    andy49,
    I tried uploading my DB file, but the file size exceeds the 500 kB limit of this forum! What can I do?

    One more try. Andy49, you figured out what I want to do. Can you upload a sample DB with a combo box in the main form and a subform, and when selecting different values in the cbo, the subform data records get updated accordingly.? any sample data will do.

  15. #15
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Try zipping your file nkb then upload it

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 01-19-2017, 05:05 AM
  2. Replies: 1
    Last Post: 03-02-2016, 08:04 AM
  3. Replies: 6
    Last Post: 02-23-2016, 01:45 PM
  4. Replies: 4
    Last Post: 12-29-2014, 01:53 PM
  5. Replies: 3
    Last Post: 11-04-2012, 09:25 AM

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