If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problem with form opening From List Box
Hi,
I have a form with a list box that lets me assign companies to employees. Some employees get a full assignment (100% of value derived) and some get a partial(55%, etc). When the user double clicks on the assignment in the list box a form pops up showing the company name and the percentage (which defaults to 100%). The user can then edit the percentage. The code which is supposed to manipulate the popped up form is failing throwing an error on each reference to it of "Method of Object 'Forms' failed". I commented out each in tern and it fails on all the references. The code is below as is the SQL it produces. Ideas - Suggestions? Thx Kevin Private Sub lstAssignments_DblClick(Cancel As Integer) Dim varItem As Variant Dim lngCustID As Long Dim lngEmplID As Long Dim strSQL As String Dim strCriteria As String If Me.lstAssignments.ItemsSelected.Count = 0 Then MsgBox ("You need to select an assignment first.") Exit Sub End If With Me!lstAssignments For Each varItem In .ItemsSelected lngCustID = .Column(2, varItem) lngEmplID = .Column(0, varItem) strCriteria = "tblCustomers.CustomerID = " & lngCustID & " And EmployeeID = " & lngEmplID & " AND Not ISNULL(EndDate) " Next varItem End With strSQL = "SELECT tblCustomers.CustomerName, tblAssignments.PercentRevenue " _ & "FROM tblAssignments INNER JOIN tblCustomers " _ & "ON tblAssignments.CustomerID = tblCustomers.CustomerID " _ & "WHERE " & strCriteria & ";" Debug.Print strSQL DoCmd.OpenForm "frmPopPercentCommission" 'Forms!frmPopPercentCommission.Visible = False 'Forms!frmPopPercentCommission.[Record Source] = strSQL 'Forms!frmPopPercentCommission.Requery Forms!frmPopPercentCommission.Visible = True End Sub SELECT tblCustomers.CustomerName, tblAssignments.PercentRevenue FROM tblAssignments INNER JOIN tblCustomers ON tblAssignments.CustomerID = tblCustomers.CustomerID WHERE tblCustomers.CustomerID = 1672 And EmployeeID = 92571 AND Not ISNULL(EndDate) ; |
#2
|
|||
|
|||
Problem with form opening From List Box
I solved the "Method of Object Problem - bad reference to Form et al.
The problem now (I Think) is that the Record source is not being updated with the SQL and is thus returning the wrong customer. I will alter the code below for the form references. Any help with the recordsource appreciated. Thanks Kevin "kc-mass" connearney_AT_comcast_DOT_net wrote in message ... Hi, I have a form with a list box that lets me assign companies to employees. Some employees get a full assignment (100% of value derived) and some get a partial(55%, etc). When the user double clicks on the assignment in the list box a form pops up showing the company name and the percentage (which defaults to 100%). The user can then edit the percentage. The code which is supposed to manipulate the popped up form is failing throwing an error on each reference to it of "Method of Object 'Forms' failed". I commented out each in tern and it fails on all the references. The code is below as is the SQL it produces. Ideas - Suggestions? Thx Kevin Private Sub lstAssignments_DblClick(Cancel As Integer) Dim varItem As Variant Dim lngCustID As Long Dim lngEmplID As Long Dim strSQL As String Dim strCriteria As String If Me.lstAssignments.ItemsSelected.Count = 0 Then MsgBox ("You need to select an assignment first.") Exit Sub End If With Me!lstAssignments For Each varItem In .ItemsSelected lngCustID = .Column(2, varItem) lngEmplID = .Column(0, varItem) strCriteria = "tblCustomers.CustomerID = " & lngCustID & " And EmployeeID = " & lngEmplID & " AND Not ISNULL(EndDate) " Next varItem End With strSQL = "SELECT tblCustomers.CustomerName, tblAssignments.PercentRevenue " _ & "FROM tblAssignments INNER JOIN tblCustomers " _ & "ON tblAssignments.CustomerID = tblCustomers.CustomerID " _ & "WHERE " & strCriteria & ";" Debug.Print strSQL DoCmd.OpenForm "frmPopPercentCommission" Me.RecordSource = strSQL Me.Requery End Sub SELECT tblCustomers.CustomerName, tblAssignments.PercentRevenue FROM tblAssignments INNER JOIN tblCustomers ON tblAssignments.CustomerID = tblCustomers.CustomerID WHERE tblCustomers.CustomerID = 1672 And EmployeeID = 92571 AND Not ISNULL(EndDate) ; |
#3
|
|||
|
|||
Problem with form opening From List Box
Problem solved but thanks.
"kc-mass" connearney_AT_comcast_DOT_net wrote in message ... Hi, I have a form with a list box that lets me assign companies to employees. Some employees get a full assignment (100% of value derived) and some get a partial(55%, etc). When the user double clicks on the assignment in the list box a form pops up showing the company name and the percentage (which defaults to 100%). The user can then edit the percentage. The code which is supposed to manipulate the popped up form is failing throwing an error on each reference to it of "Method of Object 'Forms' failed". I commented out each in tern and it fails on all the references. The code is below as is the SQL it produces. Ideas - Suggestions? Thx Kevin Private Sub lstAssignments_DblClick(Cancel As Integer) Dim varItem As Variant Dim lngCustID As Long Dim lngEmplID As Long Dim strSQL As String Dim strCriteria As String If Me.lstAssignments.ItemsSelected.Count = 0 Then MsgBox ("You need to select an assignment first.") Exit Sub End If With Me!lstAssignments For Each varItem In .ItemsSelected lngCustID = .Column(2, varItem) lngEmplID = .Column(0, varItem) strCriteria = "tblCustomers.CustomerID = " & lngCustID & " And EmployeeID = " & lngEmplID & " AND Not ISNULL(EndDate) " Next varItem End With strSQL = "SELECT tblCustomers.CustomerName, tblAssignments.PercentRevenue " _ & "FROM tblAssignments INNER JOIN tblCustomers " _ & "ON tblAssignments.CustomerID = tblCustomers.CustomerID " _ & "WHERE " & strCriteria & ";" Debug.Print strSQL DoCmd.OpenForm "frmPopPercentCommission" 'Forms!frmPopPercentCommission.Visible = False 'Forms!frmPopPercentCommission.[Record Source] = strSQL 'Forms!frmPopPercentCommission.Requery Forms!frmPopPercentCommission.Visible = True End Sub SELECT tblCustomers.CustomerName, tblAssignments.PercentRevenue FROM tblAssignments INNER JOIN tblCustomers ON tblAssignments.CustomerID = tblCustomers.CustomerID WHERE tblCustomers.CustomerID = 1672 And EmployeeID = 92571 AND Not ISNULL(EndDate) ; |
Thread Tools | |
Display Modes | |
|
|