locked
How do I find the first record on my form from a combobox in Visual Basic 2010 RRS feed

  • Question

  • This is well documented and easy to accomplish in Access, but how does one accomplish this in Visual Basic 2010 on a form designed in Visual Studio that is bound to a SQL Server database?

    The idea is to create a combobox that holds 2 columns - a primary key value and a unique value such as a [Company Name] or a [Student Name].  The user would then find the appropriate record within the combobox and the code behind the combobox control would then perform a .findfirst method that would NOT filter out all other records, but would rather use selectedvalue primary key column value to move to the selected record.

    Wednesday, July 14, 2010 7:58 AM

All replies

  • Hi Sean,

    I can't catch your question, could you explain it in detail?

     

     

    Best regards,
    Guang-Ming Bian - MSFT
    MSDN Subscriber Support in Forum
    If you have any feedback of our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Thursday, July 15, 2010 3:26 AM
    Moderator
  • Hi Guang-Ming

    The best way to explain the problem is to demonstrate what I would normally do, so...

    I have an .MDB file that I have created at http://www.itclinic.co.za/uploads/SampleComboxApp.zip

    This file will open up a students form as the initial form.

    On the form, in the header you will find a ComboBox and a 2-Button option box.

    If you choose a student from the ComboBox you will quickly locate the record associated to the student.

    Also, if you choose to search by First Name instead of Surname, then you select a button below the search combobox that changes the rowsource of the combobox.

    This is a very typical form that has to be designed as is efficient.  As such it is an absolute requirement in all Visual Studio applications that I will need to develop.

    The code behind the ComboBox can be viewed within access, but is pasted here for convenience:

    Private Sub FindStudent_AfterUpdate()
    'There are 2 possible methods that can be employed to locate a given record here by using VBA.
    'Option 1: Using the FindRecord Option
    '=======================================================================
    '    DoCmd.GoToControl "studentid"
    '    DoCmd.FindRecord Me.Combo21, acStart, , acDown, , acCurrent, True
    'Option 2: Using the SearchRecord option (preferred)
    DoCmd.SearchForRecord acDataForm, "frm_Students", acFirst, "studentID=" & CStr(Nz(Me.FindStudent.Value, ""))

    End Sub

    What I am therefore asking for is some guidance on the code that is required to accomplish the same in VS 2010.

    Regards

    Sean

    Monday, July 19, 2010 6:52 PM
  • Hi Sean,

    Since it is a Office issue, I will move it to Microsoft Office for Developers Forums, where you can get better reply.

     

     

    Best regards,
    Guang-Ming Bian - MSFT
    MSDN Subscriber Support in Forum
    If you have any feedback of our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Tuesday, July 20, 2010 8:25 AM
    Moderator
  • Hi Guang

    No, it is not an Office issue.

    I have also uploaded a complete VS Studio solution, with a SQL Server .BAK file and the SQL Scripts to recreate the entire sample solution in VS 2010.

    http://www.itclinic.co.za/uploads/StudentCourses.zip

    As you will see from the sample solution, the part that I am stuck with is trying to figure out what to do with the ComboBox.

    I provided you with the Access version purely as a point of reference.  I have no problem doing this in Access.  I need no help from the Office guys at this point in time.

    I would like to know if anyone can help me by taking a look at the application in Access, see what the ComboBox is doing there, then take a look at the VS solution and see that I have replicated the entire application in a VS/VB + SQL Server solution - minus the combobox search that is done in Access.

    I am convinced that the answer is not a complex or difficult one at all, it simply has to do with understanding the syntax of VB and the methodology behind such a simple requirement.

    I do not want a search function that is highly complex and tries to search for a value in each and every field on my form either.  This solution assumes that the search will have been performed off a view where I would simply put an index on the StudentID column, include the FName & LName into the index, and then the search from the combobox would be SUPER FAST - irrespective of how many records there are in the database.  Once the combobox is updated with the searched value I simply want to position the cursor under the selected record on the form.

    Simple.  It is so easy in Access.  Please tell me how to do this in VS 2010.

    Sean

    Tuesday, July 20, 2010 8:40 AM
  • Hi Sean,

    Sorry for my misunderstanding, I will do a VB.net sample and post it tomorrow.

     

    Best regards,
    Guang-Ming Bian - MSFT


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Tuesday, July 20, 2010 10:38 AM
    Moderator
  • Hi Sean,

    Here is the sample:

        Private Sub StudentNameComboBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles StudentNameComboBox.SelectedIndexChanged

            ''MsgBox(Me.StudentNameComboBox.SelectedValue)

            Dim strID As Integer = StudentNameComboBox.SelectedValue

     

            Dim i As Integer = Tbl_StudentsBindingSource.Find("StudentID", strID)

     

            Tbl_StudentsBindingSource.Position = i

     

        End Sub

     

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

            Dim tempID As Integer = Me.StudentNameComboBox.SelectedIndex

            StudentNameComboBox.DataSource = Me.V_StudentName_byFNameBindingSource

           

        End Sub

     

        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

            Dim tempID As Integer = Me.StudentNameComboBox.SelectedIndex

            StudentNameComboBox.DataSource = Me.V_StudentName_byLNameBindingSource

     

            Me.StudentNameComboBox.SelectedIndex = tempID

            

        End Sub

    Like the code above, we can find the exact record in combobox SelectIndexChange event and set the position of the bindingsource. Also I add the bindingsource named V_StudentName_byLNameBindingSource.

    If you still have question about it, please let me know.

    Best regards,
    Guang-Ming Bian - MSFT
    MSDN Subscriber Support in Forum
    If you have any feedback of our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Wednesday, July 21, 2010 9:22 AM
    Moderator