Match two combo boxes on two forms

Posted on 2004-10-02
Last Modified: 2010-05-02
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.
Question by:mindserve
  • 2
  • 2
LVL 19

Expert Comment

ID: 12206882
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.


Author Comment

ID: 12207007
My code on addlabor looks like 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
LVL 26

Expert Comment

ID: 12211377
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
LVL 26

Accepted Solution

Rejojohny earned 250 total points
ID: 12211390
or try this ...

>>I also have a button on this form which is called addlabor
where is the click event of the button "addlabor"???

assuming ur new form opened is called "addlabor" and button in "workorders" form is also called "addlabor" ..

Private Sub addlabor_Click()
    addlabor.cboEmployee.Text = cboemployee.Text
End sub

Author Comment

ID: 12222844
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now