none
Another Combo Box Question :( RRS feed

  • Question

  • Hello.

    I have a severe neurological problem and I've tried hard to the last 2 days to get a simple combo box problem solved without any success.

    I have a form named FrmTutoring which has a combobox named StudentName (which queries another table named TblStudentInfo) and a second combobox named SAISID. I'd like to automatically populate the SAISID field when a student name is selected. The SAIS ID's are also in the TblStudentInfo table along with the student names.

    I've watched tutorials, I've read several similar questions here after using the search feature for cascading combo boxes, and I've also downloaded Ken Sheridan's sample database using Dlookup. Between that, using queries, expressions, VBA, and mixing SQL and [whatever].references, I'm completely lost and drained and I have no idea how to get this simple thing to work. I've also provided my first database for reference which is populated with randomly generated fake data for testing purposes. Any help would be very much appreciated.

    https://drive.google.com/file/d/11EB2UV-8na1lTPMlBTVuolIEChJRgObb/view?usp=sharing

    Thursday, May 17, 2018 2:57 PM

Answers

  • That's exactly what my approach will do. For example, here's what it might look like in Design View:

    Just make sure to include the SAISID column in your student name query and also change the column count to 2.

    Hope it makes sense...

    • Marked as answer by _Sniffles_ Thursday, May 17, 2018 5:29 PM
    Thursday, May 17, 2018 5:15 PM

All replies

  • Hi,

    I don't see any combobox named "StudentName" or "SAISID."

    Are you sure this is the correct database file?

    Just curious...

    Thursday, May 17, 2018 4:23 PM
  • Thank you for taking a look. 100% sure. Open the tutoring form.
    Thursday, May 17, 2018 4:25 PM
  • Personal names can legitimately be duplicated, so must never be used as keys.  Nor is a combination of names and other columns as a composite key reliable.  I was once present at a clinic when two patients arrived within minutes of each other, both female, both with the same first and last names and both with the same date of birth.  People should always be identified by a 'surrogate' numeric primary key, e.g. PatientID, usually an autonumber for convenience.

    In your database the TblSudentInfo table has an autonumber primary key named ID.  It would be better named StudentID, but leaving that point aside for the moment, any referencing table. e.g. your TblTutoring table, should have a foreign key column named StudentID, of long integer data type.  It should not have a StudentName column, nor a SAISID column as both of these are functionally determined by StudentID.  Consequently to include them in a referencing table would mean that they are transitively dependant on the primary key of that table, and the table would not be normalized to Third Normal Form (3NF).  It would consequently be open to the risk of update anomalies.

    With only the foreign key StudentID in the referencing table you can automatically show the student name and SAISID values for the student in the form by including a combo box set up as follows:

    Name:                 cboStudent
    ControlSource:    StudentID

    RowSource:         SELECT ID, SAISID , StudentName FROM TblSudentInfo  ORDER BY StudentName;

    BoundColumn:     1
    ColumnCount:      3
    ColumnWidths:    0cm;0cm;8cm

    The exact final dimension of the ColumnWidths property is not important so long as it is at least the width of the control.  The important thing is that the first two dimensions are zero to hide the first two columns.

    The above control will show the student name, but its value will be that of the hidden ID column.  To show the SAISID value for the selected student add an unbound text box, named txtSAISID say, to the form and set its ControlSource property to:

    =cboStudent.Column(1)

    The Column property is zero-based, so this will show the value of the second column, SAISID.

    In any reports in which you want to show the student data as well as data from one or more referencing tables, you should base the report on a query which joins the referencing table to the TblStudentInfo table on ID/StudentID.  You can then return values from any columns in both tables.

    As I pointed out in my first paragraph, identifying a person solely by name is not very reliable as personal names can legitimately be duplicated.  A combo box for selecting a student would therefore be better having one or more additional columns in its list.  You'll find an example of this in NotInList.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file's opening form the contact's address is shown in a second column in the combo box's drop down list, allowing two or more contacts of the same name to be differentiated.  The address is concatenated by calling the following function in the RowSource property of the combo box:

    Public Function ConcatValues(strSeparator As String, ParamArray arrVals())

        ' Pass this function a character or characters
        ' to be used as the separator followed by the values to be combined
        ' For example: strFullName =
        ' ConcatValues(" ",FirstName, MiddleName,LastName)"

        Dim X As Integer, strLine As String

        For X = 0 To UBound(arrVals)
            If Not IsNull(arrVals(X)) And Trim(arrVals(X)) <> "" Then
              strLine = strLine & strSeparator & arrVals(X)
            End If
        Next

        ' remove leading separator character(s)
        ConcatValues = Mid(strLine, Len(strSeparator) + 1)

    End Function

    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Thursday, May 17, 2018 4:39 PM Typo corrected.
    Thursday, May 17, 2018 4:36 PM
  • Thank you for taking a look. 100% sure. Open the tutoring form.

    Okay, thanks, I see it. However, unfortunately, TblStudentInfo is empty, so I can't determine what information you would want to show in the SAIS ID combobox. Maybe what Ken wrote is enough to help you fix your issue.

    However, if you simply want to display the assigned SAIS ID for the same student selected from the student name combo, then you don't really need a cascading combobox. All you need to do is include the SAISID column in your query QryStudentName and adjust your combo's Column Count = 2.

    Then, instead of a combobox, use a Textbox for SAIS ID and use the following as its Control Source:

    =[StudentName].[Column](1)

    Hope it helps...

    • Marked as answer by _Sniffles_ Thursday, May 17, 2018 5:02 PM
    • Unmarked as answer by _Sniffles_ Thursday, May 17, 2018 5:02 PM
    Thursday, May 17, 2018 4:53 PM
  • Ken, thank you for your thorough and comprehensive reply. Unfortunately it's a bit difficult and advanced for me to understand. I've never used access for anything or created anything with it before. I've read about keys and such and couldn't understand them at all other than primary keys. I could not get different relationships to work due to various error messages which I cannot remember but some were regarding unique identifiers or something similar. I was unable to resolve any of those error messages or understand how to create relationships.

    Further Normalization is beyond my abilities. I'm not sure it's entirely necessary given there will only be a total of 30 records but then again I have no idea what I am doing. I am going off of about a week of self study.

    There is simply an autonumber field on all tables for a primary key. To my knowledge the names are not set to the primary key. These are kindergarten students in a private school and as a result they don't exactly have any real student ID they are assigned. The SAIS ID is only for testing them but they are not given in the first days of school when all the other information is provided. In all instances and regards they are identified by name because there will only be 30 names in the database as a maximum. You do raise a good point about duplicates, no idea how to handle that based on what you said.

    This is even more complicated than I thought. I will just leave the functionality set to manual. I appreciate your time and your response.

    Thursday, May 17, 2018 4:54 PM
  • This is even more complicated than I thought. I will just leave the functionality set to manual.

    Hi,

    Did you see/try the approach I posted earlier?

    Just curious...

    Thursday, May 17, 2018 5:05 PM
  • Thank you for taking a look. 100% sure. Open the tutoring form.

    Okay, thanks, I see it. However, unfortunately, TblStudentInfo is empty, so I can't determine what information you would want to show in the SAIS ID combobox. Maybe what Ken wrote is enough to help you fix your issue.

    However, if you simply want to display the assigned SAIS ID for the same student selected from the student name combo, then you don't really need a cascading combobox. All you need to do is include the SAISID column in your query QryStudentName and adjust your combo's Column Count = 2.

    Then, instead of a combobox, use a Textbox for SAIS ID and use the following as its Control Source:

    =[StudentName].[Column](1)

    Hope it helps...

    https://drive.google.com/open?id=1uLjVKivVGJA_ig30Lwy1wLFkNUiH730n

    Sorry about that I thought I uploaded a populated database. Here is one with fake data.

    Thank you I will try that and see what happens, see if I can get it to work. 
    Thursday, May 17, 2018 5:07 PM
  • Yeah thank you, it didn't appear right away and I got no notification but I replied just now and fixed the preferences. Thank you I appreciate your help.
    Thursday, May 17, 2018 5:08 PM
  • I got it and see the data now.

    So, if I use the student name combobox and select, say, Aflredo (SAISID 43232287), what exactly should I see when I click the dropdown for the SAISID combobox?

    As I said earlier, if all you need is to display the value 43232287 in the SAISID combobox, then you don't really need a combobox and simply use the approach I posted above.

    Otherwise, I just need to know what SAISID values you want to show up in the SAISID dropdown.

    Thanks.

    Thursday, May 17, 2018 5:11 PM
  • Thank you for taking a look. 100% sure. Open the tutoring form.

    Okay, thanks, I see it. However, unfortunately, TblStudentInfo is empty, so I can't determine what information you would want to show in the SAIS ID combobox. Maybe what Ken wrote is enough to help you fix your issue.

    However, if you simply want to display the assigned SAIS ID for the same student selected from the student name combo, then you don't really need a cascading combobox. All you need to do is include the SAISID column in your query QryStudentName and adjust your combo's Column Count = 2.

    Then, instead of a combobox, use a Textbox for SAIS ID and use the following as its Control Source:

    =[StudentName].[Column](1)

    Hope it helps...

    Yeah I have it setup right now to run a query that shows the student name and SAIS ID next to it and when a name is selected it inserts the SAIS ID. I was wanting to simply change the functionality so that the SAIS ID is automatically inserted instead of having to deal with the combobox altogether.
    Thursday, May 17, 2018 5:12 PM
  • That's exactly what my approach will do. For example, here's what it might look like in Design View:

    Just make sure to include the SAISID column in your student name query and also change the column count to 2.

    Hope it makes sense...

    • Marked as answer by _Sniffles_ Thursday, May 17, 2018 5:29 PM
    Thursday, May 17, 2018 5:15 PM
  • A table can, and often will, have more than one key, but can have only one primary key.  By referencing the non-key column StudentName in TblStudentInfo by the same-named column in TblTutoring you are making the assumption that StudentName is a candidate key of TblStudentInfo.  It isn't.  Do not underestimate the probability of personal names being duplicated.  As well as my experience with the two patients at the clinic, I worked with two Maggie Taylors.

    The mathematical probability of names being duplicated is higher than you might think, and cannot be ruled out in a population of thirty.  The number of contemporary classical music composers in North America is, I'd guess, not great, but includes two John Adams.

    I tried to make my description of what needs doing as simple and as full as possible.  I'd be surprised if you cannot implement it with ease.

    Ken Sheridan, Stafford, England

    Thursday, May 17, 2018 5:19 PM
  • To automatically populate the SAISD field when you select a StudentName in the FrmTutoring form:

    1. Change the SAISID DropDown box in your FrmTutoring to a plain Textbox control because you are going to automatically populate it with a Lookup from another table.
    2. In your FrmTutoring StudentName dropdown box AfterUpdate Event:

    Private Sub StudentName_AfterUpdate()
        With Me.SAISID        DELETE. NOT NEEDED
            .SetFocus             DELETE. NOT NEEDED
            .Dropdown           DELETE. NOT NEEDED
        End With                 DELETE. NOT NEEDED
    Me.SAISID = DLookup("[SAISID]", "TblStudentInfo", "[StudentName]=Forms![FrmTutoring]![StudentName]")
    End Sub

    Thursday, May 17, 2018 5:21 PM
  • It took a few minutes to figure it out but I finally got what you are saying and got it working. Thank you. This is good enough. It should work just fine for my purposes. Thank you for a fairly simply and easy to understand solution.
    Thursday, May 17, 2018 5:27 PM
  • Thank you. I appreciate that extra snippet. I did remove the Event code when I tried the dbguy's solution. It seems to work automatically without any additional code but I may be able to use that to solve some other problems thank you for taking a look and passing me some good info.
    Thursday, May 17, 2018 5:29 PM
  • Hi,

    Glad to hear you got it sorted out, for now. We're all happy to assist. Good luck with your project.

    Thursday, May 17, 2018 5:31 PM
  • I cannot implement it with ease or great difficulty. I have a severe neurological problem that makes it difficult for me to understand things, my ability to concentrate and my memory. Sometimes I do not recognize numbers or letters I've been reading and working with my entire life.

    I do appreciate your help and your time. I will save your response and study it and see if I can understand it well enough to make improvements over time. I want to do my best in making something helpful not just get something working. You are right about the names. It's a certainty there will be duplicates at some point. For now the other solution is best for me because it is simple, I can understand it, and it will work. It is appropriate for me given the circumstances and my knowledge level and also my skill set. It works and it will do until I can understand how to make the functionality much better. It may be possible in time but I do not know. I will try. I am reading a Microsoft Access book, trying to learn. I've already taken some important things away from your reply, in time I hope I can understand everything and get all the value of your your reply. Again, thank you for your time, your effort, and your extremely thorough reply.

    Thursday, May 17, 2018 5:35 PM
  • It took a few minutes to figure it out but I finally got what you are saying and got it working. Thank you. This is good enough. It should work just fine for my purposes. Thank you for a fairly simply and easy to understand solution.

    If this comment was meant for me, I was glad to help as the others were as well. When I downloaded the database I also noted a number of potential problems with the setup and table designs that could use some changes as Ken pointed out. Primary amoung these is the use of StudentName. Make absolutely sure you enter both the students First and Last names, and even then, there could be more than one Bill Jones or Mary Smith. I would take Kens advise on this and Change the ID to StudentID, then add that field to the other tables as it can then be used as a common unique field on which to base queries and LookUp calculations. I also noted that the SAISID field is a Text Data Type. This is apparently a Numeric field. So change the SAISID field in your table to a 'Number' Data Type. This will avoid numerous problems in the future.

    Warning: the DLookUp solution I provided will not work properly if there are duplicate student names in your TblStudentInfo table. ACCESS will take the first instance of the students name it finds and put the corresponding SAISID value in the field. That is why it is so important not to use names. You should use the unique StudentID number that is automatically generated by ACCESS when the record is originally created.

    Thursday, May 17, 2018 7:35 PM