Results 1 to 7 of 7

Listbox Value not updated in subform

  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 online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    18,358
    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
    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
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,007
    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 (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment
    nil illegitimi carborundum est

  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 online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    18,358
    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
    www.BaldyWeb.com

  7. #7
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,007
    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 (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment
    nil illegitimi carborundum est

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

Similar Threads

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