none
Pulling out and showing appropriate data based on Name selection RRS feed

  • Question

  • I'd like to create a combo box so that when user selects a student name from the drop down (based on a distinct query), it will show all Errors that student made in the second box (a list box). Then when user click to select a record in the second box, it will pop up Error Details in the third box (a list box). And then when user clicks on a record in the Error Details box, it will pop up Error Explanation in the 4th box.
    To make it more complicate, the error validation is based on the position of "1" in the column [error validation] in tblErrorData. For example, position ----1---- is the 5th position. This 5th position is associated with the validation_message in the table, validation message table.

     Do you know how to solve this? Thank you in advance for your help.
    Wednesday, May 17, 2017 10:02 PM

All replies

  • Hi ttim,

    It sounds like you could use the same approach for Cascading Comboboxes (except yours will be Listboxes).

    Hope it helps...


    • Edited by .theDBguy Thursday, May 18, 2017 2:53 AM
    Wednesday, May 17, 2017 10:26 PM
  • Hi ttim,

    you can try to use After update event like below to fill the data.

    Private Sub List7_AfterUpdate()
    List9.RowSource = "SELECT fld2 from combo_fields WHERE fld1 = '" & Me.List7.Value & "'"
    End Sub
    
    Private Sub List9_AfterUpdate()
    List11.RowSource = "SELECT fld3 from combo_fields WHERE fld2 = '" & Me.List9.Value & "'"
    End Sub

    Output:

    other thing you had mentioned that,"To make it more complicate, the error validation is based on the position of "1" in the column [error validation] in tblErrorData. For example, position ----1---- is the 5th position. This 5th position is associated with the validation_message in the table, validation message table."

    I did not understand peroperly "----1----". do you mean position of character?

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, May 18, 2017 2:42 AM
    Moderator
  • Hi Deepak,

    Thank you for responding. Your animated output is exactly what I am looking for. Yes, it's position of character. In the query to pull data out, there is a table with a column named, "Validation". This column have value "1" that is positioned in the field so that it is matched with the other table that has the fields  [position value] and [validation message].  From that, I can get the position value, what is the error/validation message, and the total of counts of error that the students have. And then after that is a list of error message as following.

    Stud                Error Message       Detail Error      Error Explanation 
    John Doe         All Records(10)      
                          Invalid codes          Code100         code in class
                                                        


    Thursday, May 18, 2017 5:22 AM
  • Thank you, theDBguy. I will take a look at it. 

    Thursday, May 18, 2017 5:23 AM
  • Hi ttim,

    does it mean "----1----" if 1 is at 5th position in the field then there is 5 errors made by that student and we need to display it that 5 errors to the student? or it needs to look for a error code 5 in the table?

    If yes, I think no need to save data like this.

    we can make it simple so that we can easily fetch the errors made by student.

    if possible then you can try to post screen shot of your table design and data stored in the table.

    so that we can get the exact idea.

    I think it is better to just store the numbers directly in the table instead of storing like this "----1----".

    it only make it more complicated.

    we can use joins to fetch data from multiple tables.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, May 19, 2017 1:15 AM
    Moderator
  • Hi Deepak,

    Yes, if there are 5 errors made by that student, all 5 of them need to be displayed to the teacher, not the student. The teacher wants to know what kind of errors the student made. The tables are already built and I have to use them. 

    The SQL statement is
    strErrorSQL = "select position, validation_msg, count(*) as CNT 
    From qryDistinctValidation A, qryMainErrorList B
    Where MID(Error_Validation_Filtered, postion, 1) = '1')
    Group by position, validation_msg
    Order by validation_msg

    ----------------

    With qryDistinctValidation pulls out position and validation_msg
    and qryMainErrorList produces the counts based on the field [Error_Validation_Filtered] with the position
    mentioned above like ----1----

    Thank you

    Friday, May 19, 2017 5:42 AM
  • Hi ttim,

    I can understand your requirement, like you want to display error of students to Teacher and there are several tables contains error code and error description.

    but what I did not understand is what is the purpose to store value like this "----1----" ?

    it is confusing me. it is much easy to directly store the numeric value 5 in the table and match it with error code and description to fetch it.

    what is the maximum limit of storing the value like this? is it limited to any number of errors?

    it is better if you post your database design here.

    community members will try to suggest you very simple solution for you issue.

    I think that we can store all error related details in one single table, like error code, error description and try to fetch that using error id.

    other thing I want to confirm with you that currently how you calculate that particular student has made how many numbers of errors?

    I mean how you assign any error to any student?

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 19, 2017 6:50 AM
    Moderator
  • Hi Deepak,
    Thank you for your feedback. This program (tables, forms, etc.) is already built and I cannot change them. What I understand is in the SQL statement, MID(Error_Validation_Filtered, Valstr_Pos), the function MID takes the string ----1----, check to see if the 5th position is 1. If yes then it will pull out an error message and a count to show how many times the students making this error.

    HTHs

    Monday, May 22, 2017 4:23 PM
  • Hi ttim,

    I can understand your situation.

    you not need to change anything.

    if possible for you then you can try to update the old data in your table with storing numbers as text.

    like "----1----" you get 5th position so you can directly store 5 as text in your table.

    and then you just need to directly check that number and need not to use MID() Function.

    but if you don't want to change anything then you can continue with existing approach.

    as an alternative , you can also try to refer InStr Function.

    InStr Function

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 23, 2017 6:34 AM
    Moderator