Updating database using userform dependent combobox selection (VBA)

[thus77]

Hi all,

I am new to post on this forum, but I have referred to this site on various occasions.

Am also a VBA illiterate but I have some how made my Userform work.

So here's my question.

How can I update the database using the form which has dependent combo box selection?

- I have created a userform which looks up data from a sheet based on:
1. unique customer name (column T) list : upon selection of which returns a list of the second combo box "First name"
2. "first name" selection populates the form

This works perfectly well upto here. But when I try to change the "customer name" selection, the "First name" remains the list of the first choice.

Also if i try to update the form, the first row of the filtered data gets updated, not the corresponding row...


I need help:
- to make first name list change based on the "Customer Name" selection
- to be able to update the right row on the database using the form

have just pasted the partial code...

Sub CmbCustomer_DropButt*******() button click


If CmbCustomer.ListCount = 0 Then
Worksheets("Data").Activate
Dim r As Range
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For Each r In Range("T2", Range("T" & Rows.Count).End(xlUp))
        If Not IsEmpty(r) And Not .exists(r.Value) Then .Add r.Value, Nothing
    Next
    Me.CmbCustomer.List = .keys
End With
End If
End Sub


Private Sub CmbCustomer_Change()
Worksheets("Data").Activate
    With ActiveSheet
        .AutoFilterMode = False
        With .Range("T1", Range("a" & Rows.Count).End(xlUp))
            .AutoFilter
            If Len(Me.CmbCustomer.Value) > 0 Then
                .AutoFilter Field:=20, Criteria1:=Me.CmbCustomer.Value
            End If
End With
End With


End Sub


    Sub CmbFirstName_DropButt*******() button click
    If CmbCustomer = "" Then
    MsgBox "Select Customer Name"
    Else:
    If CmbFirstName.ListCount = 0 Then
       
       Worksheets("Data").Activate
               Dim rCell As Range, rVisibles As Range
    
               With Sheets("Data")
                   Set rVisibles = .Range("x2", .Cells(Rows.Count, "x").End(xlUp)).SpecialCells(xlCellTypeVisible)
               End With
               For Each rCell In rVisibles
                   Me.CmbFirstName.AddItem rCell.Value
                    
               Next rCell
       End If


        End If
    End Sub


Private Sub cmbfirstname_change()


    Worksheets("Data").Activate
    Dim lngDataRow As Long
    lngDataRow = Application.Match(Me.CmbFirstName.Value, Worksheets("Data").Range("X:X"), 0)
    
        If Worksheets("Data").Range("A" & lngDataRow).Value = 1 Then Me.cboCustStat.Value = "Active"
        If Worksheets("Data").Range("A" & lngDataRow).Value = 2 Then Me.cboCustStat.Value = "Prospect"
        If Worksheets("Data").Range("A" & lngDataRow).Value = 0 Then Me.cboCustStat.Value = "Closed"
        
        If Worksheets("Data").Range("B" & lngDataRow).Value = 1 Then Me.cmbPriceList.Value = "Yes"
        If Worksheets("Data").Range("B" & lngDataRow).Value = "" Then Me.cmbPriceList.Value = "No"
        
        If Worksheets("Data").Range("C" & lngDataRow).Value = 1 Then Me.cmbMailingEvent.Value = "Yes"
        If Worksheets("Data").Range("C" & lngDataRow).Value = "" Then Me.cmbMailingEvent.Value = "No"
        
        If Worksheets("Data").Range("D" & lngDataRow).Value = 1 Then Me.CmbBauma.Value = "Yes"
        If Worksheets("Data").Range("D" & lngDataRow).Value = "" Then Me.CmbBauma.Value = "No"
        
        If Worksheets("Data").Range("E" & lngDataRow).Value = 1 Then Me.cboGforce.Value = "Yes"
        If Worksheets("Data").Range("E" & lngDataRow).Value = "" Then Me.cboGforce.Value = "No"
        
        If Worksheets("Data").Range("G" & lngDataRow).Value = 1 Then Me.cboMail.Value = "Yes"
        If Worksheets("Data").Range("G" & lngDataRow).Value = "" Then Me.cboMail.Value = "No"
       
        If Worksheets("Data").Range("J" & lngDataRow).Value = 1 Then Me.cmbMailingParts.Value = "Yes"
        If Worksheets("Data").Range("J" & lngDataRow).Value = "" Then Me.cmbMailingParts.Value = "No"
        
        If Worksheets("Data").Range("L" & lngDataRow).Value = 1 Then Me.CmbMailHigh.Value = "Yes"
        If Worksheets("Data").Range("L" & lngDataRow).Value = "" Then Me.CmbMailHigh.Value = "No"
        
        If Worksheets("Data").Range("M" & lngDataRow).Value = 1 Then Me.CmbMail30.Value = "Yes"
        If Worksheets("Data").Range("M" & lngDataRow).Value = "" Then Me.CmbMail30.Value = "No"
        
        If Worksheets("Data").Range("N" & lngDataRow).Value = 1 Then Me.cmbTurnover.Value = "Yes"
        If Worksheets("Data").Range("N" & lngDataRow).Value = "" Then Me.cmbTurnover.Value = "No"
        
        If Worksheets("Data").Range("Q" & lngDataRow).Value = 1 Then Me.CmbDieCast.Value = "Yes"
        If Worksheets("Data").Range("Q" & lngDataRow).Value = "" Then Me.CmbDieCast.Value = "No"
        
        Me.txtAKA.Value = Worksheets("Data").Range("U" & lngDataRow).Value
        Me.cboTitle.Value = Worksheets("Data").Range("W" & lngDataRow).Value
        Me.CmbSurname.Value = Worksheets("Data").Range("Y" & lngDataRow).Value
        Me.txtDesignation.Value = Worksheets("Data").Range("Z" & lngDataRow).Value
        Me.txtEmail1.Value = Worksheets("Data").Range("AA" & lngDataRow).Value
        Me.txtDirectPhone.Value = Worksheets("Data").Range("AB" & lngDataRow).Value
        Me.txtStdPhone.Value = Worksheets("Data").Range("AC" & lngDataRow).Value
        Me.txtMobile.Value = Worksheets("Data").Range("AD" & lngDataRow).Value
        Me.txtFax.Value = Worksheets("Data").Range("AE" & lngDataRow).Value
        Me.txtWebsite.Value = Worksheets("Data").Range("AF" & lngDataRow).Value
        Me.txtAddress.Value = Worksheets("Data").Range("AG" & lngDataRow).Value
        Me.txtAddress2.Value = Worksheets("Data").Range("AH" & lngDataRow).Value
        Me.TxtAddress3.Value = Worksheets("Data").Range("AI" & lngDataRow).Value
        Me.txtCity.Value = Worksheets("Data").Range("AJ" & lngDataRow).Value
        Me.txtState.Value = Worksheets("Data").Range("AK" & lngDataRow).Value
        Me.txtPostcode.Value = Worksheets("Data").Range("AL" & lngDataRow).Value
        Me.cboCountry = Worksheets("Data").Range("AM" & lngDataRow).Value
        Me.cmbLang.Value = Worksheets("Data").Range("AN" & lngDataRow).Value
        Me.cboAssignedTo.Value = Worksheets("Data").Range("AO" & lngDataRow).Value
        Me.cmbEmear.Value = Worksheets("Data").Range("AP" & lngDataRow).Value
        Me.cboCat.Value = Worksheets("Data").Range("AQ" & lngDataRow).Value
        Me.cboPrinSeg.Value = Worksheets("Data").Range("AR" & lngDataRow).Value
        Me.cboOption.Value = Worksheets("Data").Range("As" & lngDataRow).Value
        Me.CboRent.Value = Worksheets("Data").Range("At" & lngDataRow).Value
        Me.txtDlrNme.Value = Worksheets("Data").Range("AU" & lngDataRow).Value
        Me.txtActivity.Value = Worksheets("Data").Range("AV" & lngDataRow).Value
        Me.txtTMS.Value = Worksheets("Data").Range("AW" & lngDataRow).Value
        Me.cmbTMS.Value = Worksheets("Data").Range("AX" & lngDataRow).Value
        Me.CmbSteel.Value = Worksheets("Data").Range("AY" & lngDataRow).Value
        Me.cmbGTH.Value = Worksheets("Data").Range("AZ" & lngDataRow).Value
        Me.txtParts.Value = Worksheets("Data").Range("BA" & lngDataRow).Value
        Me.chkAlum.Value = Worksheets("Data").Range("BB" & lngDataRow).Value
        Me.ChkBoom.Value = Worksheets("Data").Range("BC" & lngDataRow).Value
        Me.ChkScissor.Value = Worksheets("Data").Range("BD" & lngDataRow).Value
        Me.ChkGTH.Value = Worksheets("Data").Range("BE" & lngDataRow).Value
        Me.ChkOBrands.Value = Worksheets("Data").Range("BF" & lngDataRow).Value
        Me.ChkParts.Value = Worksheets("Data").Range("BG" & lngDataRow).Value
        Me.ChkService.Value = Worksheets("Data").Range("BH" & lngDataRow).Value
        Me.ChkTraining.Value = Worksheets("Data").Range("BI" & lngDataRow).Value
        Me.ChkUsed.Value = Worksheets("Data").Range("BJ" & lngDataRow).Value
        Me.TxtLastOb.Value = Worksheets("Data").Range("BK" & lngDataRow).Value
        Worksheets("Data").Range("BL" & lngDataRow).Value = Me.txtLastAns.Value
        Worksheets("Data").Range("BM" & lngDataRow).Value = Me.txtNote.Value
        Worksheets("Data").Range("BN" & lngDataRow).Value = Me.txtListing.Value
        Me.txtCustomer2 = Me.CmbCustomer.Text
        Me.txtFirstName2 = Me.CmbFirstName.Text
        Me.TxtSurname2 = Me.CmbSurname.Text
        Me.txttitle2 = Me.txtDesignation.Text
      
    End Sub