0

I'm working on Access Tracker, basically it has a table and form. The column headers of table are parent entity, subs, Compliance etc., the combo boxes in form are parententity2, subs2, compliance2, etc. Each parent in the table can have multiple subs. I have written a code in such a way when I select a value in the form of parententity2, then it autopopulates the rest of the information. However, when a parent has multiple subs it is not showing all of the values, instead it is showing the first value which is in the list. For example ABC is a parent company and A1, B1, C1, are subs..then output is when I filter ABC then under subs dropdown I can only see A1, instead of A1, B1, C1

Private Sub Subs2_AfterUpdate()

Dim strFilter As String

Dim abc


    ' Get the selected value from the parententity2 combo box
    'Dim strFilter As String
    strFilter = Me.Parententity2.Value
    
    ' Apply the filter to the form's record source
    Me.RecordSource = "SELECT * FROM [Data] WHERE [parent entity] = '" & strFilter & "'"
    
    ' Populate the Subs column with the filtered data
    Me.Subs2.RowSource = "SELECT DISTINCT [subs] FROM [Data] WHERE [parent entity] = '" & strFilter & "'"
End sub
June7
  • 19,874
  • 8
  • 24
  • 34
user21308963
  • 1
  • 1
  • Looks like code should work. However, why are you pulling subs from Data table and not a lookup table? Are you sure there are records in Data with all 3 sub values? Edit question to show sample data as formatted text tables. – June7 Mar 15 '23 at 17:23
  • Yes, for few accounts there is only on sub and for few more than 2... Data is the name of the table here – user21308963 Mar 16 '23 at 02:03
  • Just to add for above questions the columns I have selected are in short text format and their look up property is text box , is this can be a reason..I tried changing to combo box, when I changed it the output whatever I was getting even that is not populating – user21308963 Mar 16 '23 at 02:22
  • Maybe post in a forum that allows attaching files and post your db for analysis. – June7 Mar 16 '23 at 03:09
  • Im not sure which forum it is, since am working in a company I cannot attach files to outside company portals – user21308963 Mar 16 '23 at 03:13
  • Would love to help if I had enough information to work with. Don't know your data structure nor form design. That's a big vacuum. As I said, code presented looks like it should work. – June7 Mar 16 '23 at 03:19
  • Can I know your email id, or how can we both connect? – user21308963 Mar 16 '23 at 04:27
  • Sorry, no. Only communication is via forum. – June7 Mar 16 '23 at 08:44
  • I didnt understand by the word forum...can you share the link of that forum – user21308963 Mar 16 '23 at 10:39
  • Stackoverflow is a forum. Communicate via these comments or answer here or in any other website forum. – June7 Mar 16 '23 at 18:21
  • I can let you know all the details which you need......All the boxes in userform are selected as combo box... the data types of table are selected as short text and the look up as text box...row source for parent entity is SELECT Data.ID, Data.[Parent entity] FROM Data; ...and there is no row source for other combo boxes.... – user21308963 Mar 17 '23 at 05:16
  • let me know if you need any further information – user21308963 Mar 17 '23 at 05:17
  • I also tried changing the look up of data types to combo box and making the value as yes for allow multiple entries..but this didnt work – user21308963 Mar 17 '23 at 05:19
  • That doesn't really provide any new info, except "no row source for other combo boxes" which makes no sense. I already advised what I would need to analyze this issue. – June7 Mar 17 '23 at 05:36
  • Can we attach the file here? – user21308963 Mar 17 '23 at 05:41
  • All info to analyze issue is supposed to be within the question, not linked files or downloads. No, files cannot be directly attached. You can post a link to a download but many will not download from external sources. I already suggested posting in a forum that does allow attaching files and you said you cannot attach files. – June7 Mar 17 '23 at 06:22

0 Answers0