Results 1 to 7 of 7
  1. #1
    msamanth is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    3

    Lightbulb Listbox Value not updated in subform

    Hello Experts,

    1) There is a maintable[T1], 10/10th field (R) which is a Multiple Selection Listbox inheriting values from another table[T2]. T2 is single column table with data to make a listbox.
    2) A mainform[F1] with some filter controls
    3) A subform[F2] based on the T1 in the mainform.
    4)The R(Remarks) in F2 is deleted & replaced with a Listbox[LB] - It being a) Control Source to [T1].(Remarks), b) Row Source to T2, c) Allow Value List Edits to F2.

    Requirement: R should be automated based on 9/10 field. The value of R in F2 should be set to "OK" 1st value in the in the list box.

    My VBA:

    Private Sub Invoice_Date_AfterUpdate() 'Invoice_Date is the 9/10 field
    Me.Remarks.Selected(1) = True
    Me.Remarks.value = Me!Remarks.Selected(1)


    End Sub
    Attached Thumbnails Attached Thumbnails Picture1.png  

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I've read this a couple of times and still don't understand what you want to accomplish. Do you want to only set that field if there's a date?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    msamanth is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    3
    yes. When there's is a date then the R Column should be updated with some text like "OK".

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Use an update query on your after update event
    Something like

    Code:
    UPDATE Table1 SET Table1.Remarks = 'OK'
    WHERE (((Table1.invoice_date) Is Not Null) AND ((Table1.Credit_Reference)= Forms!MyFormName.Credit_Reference));
    If using this as SQL, add suitable delimiters for credit reference field

    For info, I have no idea what you mean by 9/10 and 10/10 fields
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    msamanth is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    3
    Thanks for you support!

    I tried your method but it the query doesn't executes if the executable feild is a a multi-valued feild [Run Time Error 3826].
    FYI - the Remarks in my subform, is a listbox with multiple values listed in it and one of the value is "OK".


    With regard to "9/10 and 10/10 fields" - I just mentioned their position in my table. The Invoice date is 9th Column and Remarks Field is 10th column.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Include a test so you only set the value when there's a date:

    If IsDate(Me.Invoice_Date) Then
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hadn't realised it was a multivalued field.
    I never use them as they are difficult to work with.
    Hopefully pbaldy's suggestion will work for you.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 2
    Last Post: 07-13-2016, 02:19 PM
  2. Replies: 8
    Last Post: 03-12-2016, 02:06 PM
  3. Want subform updated
    By GeorgeJ in forum Programming
    Replies: 4
    Last Post: 02-15-2015, 12:18 PM
  4. Replies: 1
    Last Post: 01-31-2015, 09:03 PM
  5. Replies: 6
    Last Post: 07-24-2014, 08:18 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