Solved

Match two combo boxes on two forms

Posted on 2004-10-02
5
152 Views
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.
0
Comment
Question by:mindserve
  • 2
  • 2
5 Comments
 
LVL 19

Expert Comment

by:Shauli
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.

S
0
 
LVL 1

Author Comment

by:mindserve
ID: 12207007
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
0
 
LVL 26

Expert Comment

by:Rejojohny
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
0
 
LVL 26

Accepted Solution

by:
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.show
    addlabor.cboEmployee.Text = cboemployee.Text
End sub
0
 
LVL 1

Author Comment

by:mindserve
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
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
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…

760 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

20 Experts available now in Live!

Get 1:1 Help Now