Link to home
Start Free TrialLog in
Avatar of mindserve
mindserve

asked on

Match two combo boxes on two forms

I have a form named workorders and a cboemployee combo box which load the information from employees table.

I also have a button on this form which is called addlabor. When the user clicks this button, a small form loads and again the  cboemployee box with the information from employees table is loaded.

What I want to do is once the user chooses the cboemployee from the workorder, to have the second form load with only that employee listed, or to somehow highlight and lock that employee so that any labor added ( which is added to a listview control) is always by the same employee. How can I do this. So far I have tried everything , but nothing works.
Avatar of Shauli
Shauli

One way to do it, is to set your sql statement that loads the employee data to the secons form, to look for the employee selected in cboemployee on workorders form. Something like:

"SELECT * FROM employees WHERE employee='" & workorders.cboemployee.Text & "';"

If you do that, then you really don't need a combobox on the second form. You can replace it with a label containing the selected employee name, and load your listview accordingly.

S
Avatar of mindserve

ASKER

My code on addlabor looks like this...how can I get this code to show the same information from the frmworkorder form to this addlabor form.?
--------------------------------------------------------------------

Option Explicit

Private rsemployees As ADODB.Recordset
Private mclsLabor As clsWOLabor

Public Property Get Labor() As clsWOLabor
59080   Set Labor = mclsLabor
End Property

Public Property Let Labor(ByVal NewLabor As clsWOLabor)
59090   Set mclsLabor = NewLabor
End Property

Private Sub cmdCancel_Click()
59100   Unload Me
End Sub

Private Sub cmdOK_Click()
On Error Resume Next
59110   mclsLabor.BillableHours = Me.txtHours.Text
59120   mclsLabor.Comment = Me.txtComments.Text
59130   mclsLabor.BillingRate = CCur(Mid(Me.txtRate.Text, 2))
59140   mclsLabor.EmployeeID = Me.cboEmployees.ItemData(Me.cboEmployees.ListIndex)
59150   Unload Me
End Sub

Public Property Get EmployeeRecords() As ADODB.Recordset
59160   Set EmployeeRecords = rsemployees
End Property

Public Property Let EmployeeRecords(ByVal rsEmp As ADODB.Recordset)
59170   Set rsemployees = rsEmp
End Property

Private Sub CBOEmployees_Click()
59180   With rsemployees
59190     .MoveFirst
59200     .Find "EmployeeID = " & cboEmployees.ItemData(cboEmployees.ListIndex)
59210     Me.txtRate.Text = Format(.Fields("BillingRate"), "Currency")
59220   End With
End Sub

Private Sub Form_Load()
        Dim iCnt As Integer
        Dim iLoop As Integer
59230   txtRate.Locked = False
59240   PopulateEmployees
       
59250   If mclsLabor.WorkorderLaborID = 0 Then
59260     Me.txtHours.Text = Format(0, "General Number")
59270     Me.txtRate.Text = Format(0, "Currency")
59280     Me.txtComments.Text = ""
59290   Else
59300     Me.txtHours.Text = Format(mclsLabor.BillableHours, "General Number")
59310     Me.txtRate.Text = Format(mclsLabor.BillingRate, "Currency")
59320     Me.txtComments.Text = mclsLabor.Comment
59330     iCnt = cboEmployees.ListCount - 1
59340     For iLoop = 0 To iCnt
59350       If cboEmployees.ItemData(iLoop) = mclsLabor.EmployeeID Then
59360         cboEmployees.ListIndex = iLoop
59370         Exit For
59380       End If
59390     Next
59400   End If
59410   GetFormPos Me
End Sub

Private Sub Form_Unload(Cancel As Integer)
59420   SetFormPos Me
End Sub

Private Sub PopulateEmployees()
59430 On Error Resume Next
59440   Me.cboEmployees.Clear
59450   With rsemployees
59460     .MoveFirst
59470     Do While Not .EOF
59480       Me.cboEmployees.AddItem (.Fields("LastName") & ", " & .Fields("FirstName"))
59490       Me.cboEmployees.ItemData(cboEmployees.NewIndex) = .Fields("EmployeeID")
59500       .MoveNext
59510     Loop
59520   End With
End Sub

Private Sub txtComments_GotFocus()
59530   Me.txtComments.SelStart = 0
59540   Me.txtComments.SelLength = Len(Me.txtComments.Text)
End Sub

Private Sub txtHours_GotFocus()
59550   Me.txtHours.SelStart = 0
59560   Me.txtHours.SelLength = Len(Me.txtHours.Text)
End Sub

Private Sub txtHours_KeyPress(KeyAscii As Integer)
59570   Select Case KeyAscii
          Case 46, 48 To 57, 8
59580     Case Else
59590       KeyAscii = 0
59600   End Select
End Sub

Private Sub txtRate_GotFocus()
59610   Me.txtRate.SelStart = 0
59620   Me.txtRate.SelLength = Len(Me.txtRate.Text)
End Sub

Private Sub txtRate_KeyPress(KeyAscii As Integer)
59630   Select Case KeyAscii
          Case 46, 48 To 57, 8
59640     Case Else
59650       KeyAscii = 0
59660   End Select
End Sub

Private Sub txtRate_LostFocus()
59670   If Mid(Me.txtRate.Text, 1, 1) <> "$" Then
59680     Me.txtRate.Text = "$" & Me.txtRate.Text
59690   End If
End Sub
Add this line of code in the load event of the NewForm opened .. i.e. after the combo is filled with all the employee names ..

NewForm.cboEmployee.Text = workorders.cboemployee.Text
ASKER CERTIFIED SOLUTION
Avatar of Rejojohny
Rejojohny
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Nope, that doesn't do it as it still allows the user to change the employee name...I need to lock it...and guess what, I figured it out myself!  But Johnny gets th points as he was closest to what I needed
frmLabor.cboEmployees = frmWorkOrder.cboEmployees
frmLabor.cboEmployees.Locked = True