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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to debug this code 7 65
Access 2013 combo box not working 3 60
Modifying Conditional Format from VBA code 3 72
how to Classify of email (pdf attachments) 7 50
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

730 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