Updating database using userform dependent combobox selection

[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.:crying:21141


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...


Code:




Private Sub Worksheet_Activate()
Worksheets("Data").Activate
Worksheets("Data").AutoFilterMode = False
End Sub


Sub CmbCustomer_DropButtonClick()


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_DropButtonClick()
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)
For lngDataRow = 3 To LastRow


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
Exit For
End If




End Sub


I have deleted extra sheets to reduce the size of the file... need the form ViewDataForm to work :(

[SamT]

Just by placing "Option Explicit" at the top of both Form Modules, and using Debug >> Compile, I found 4 errors

Some other things I noticed, that won't stop the code from working, but do make things more complicated and harder to understand.
You data sheet is laid out haphazardly, for exa
Gotta go, C U L8r

[thus77]

Hi SamT,

Thank you for replying.. :)

am pretty to new to VBA itself and everything that i built the code around is basically...obviously, codes i copied off the net to make it serve the purpose.. :(

I do appreciate you trying to help..

what i would like the viewdataform to do for me is help update the fields in the corresponding rows it looks up.

for eg: Access Gulf Rental, results three contacts. but when i try to edit the second contact on the form, it still updates the first one..
how could i help sort this out?