Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2013
    Location
    Chesapeake, VA
    Posts
    30

    Unanswered: Help with a VBA question PLEASE!

    Why does the code line below in red give me a run time error 424 " object required. But the code in blue works fine?

    Code:
    Private Sub cboMaterial_ID_AfterUpdate()
    Dim Number As Integer
    Dim NumResult As Integer
    Dim result As Integer
    
    
    'result = DLookup("[Quantity]", "Materials", "Material_ID=" & Me!cboMaterial_ID)
    
    
    result = [cboMaterial_ID].Column(2)
    If result = 0 Then
    MsgBox ("No more materials available")
    Else
    Number = InputBox("Enter the Quantity,")
    NumResult = result - Number
    Me![cboMaterial_ID].Column(2) = NumResult
    MsgBox ("Current Value of Quantity" & NumResult)
    
    End If
    End Sub
    Last edited by back2basics; 02-19-13 at 07:37.

  2. #2
    Join Date
    Feb 2013
    Posts
    3
    Try

    Me![cboMaterial_ID].Column(2) = NumResult

  3. #3
    Join Date
    Jan 2013
    Location
    Chesapeake, VA
    Posts
    30
    Thank you Rogue, But now getting a new error "451":

    "Property let procedure not defined and property get procedure did not return a object"

    What is Access talking about with this error? How could I have not set this combo box up right to do what I want to do?

    Help states" You specified an operation or property that is exclusive to collections, but the object isn't a collection"


    I mean, why does the command work in the above code when getting the data from the table?

    Any suggestions anyone, Dale
    Last edited by back2basics; 02-19-13 at 08:20.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The Column(n) property of a Combobox is read-only, see: Column Property*[Access 2007 Developer Reference].

    The only way you can change the contents of a ComboBox or a ListBox control (except by changing its RowSource property or using the Requery method) is by using the AddItem or RemoveItem Methods. See: AddItem Method*[Access 2007 Developer Reference] and RemoveItem Method*[Access 2007 Developer Reference]
    Have a nice day!

  5. #5
    Join Date
    Jan 2013
    Location
    Chesapeake, VA
    Posts
    30
    Thank you, this is very helpful. It is obvious that this command will not work for what I want to do. Not sure if I can explain this.

    I do not want to modify the combo box.

    I want to put the answer "NumResult" back in the field from which the first statement result = [cboMaterial_ID].Column(2) retrieved it. Is this possible?

    Will the UPpdate, Set, Where command work. If so, how can I point to the correct field in the "Where" statement?
    Last edited by back2basics; 02-19-13 at 11:35.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by back2basics View Post
    I want to put the answer "NumResult" back in the field from which the first statement result = [cboMaterial_ID].Column(2) retrieved it. Is this possible?
    I'm not sure to understand. Do you mean that you want to update the table where the data value that appears in the combobox comes from?

    If the answer is yes and from the commented line you originally posted, you should try:
    Code:
    Dim strSQL As String
    strSQL = "UPDATE Materials SET Quantity = " & NumResult & " WHERE Material_ID = " & Me.cboMaterial_ID ' Use Material_ID = '" & Me.cboMaterial_ID & "'" if Material_ID is not numeric.
    CurrentDb.Execute strSQL, dbFailOnError
    '
    ' To update the Combo, use:
    '
    Me.cboMaterial_ID.Requery
    DLookup("[Quantity]", "Materials", "Material_ID=" & Me!cboMaterial_ID)
    Have a nice day!

  7. #7
    Join Date
    Jan 2013
    Location
    Chesapeake, VA
    Posts
    30
    Sinndho,

    "Do you mean that you want to update the table where the data value that appears in the combobox comes from?"


    Yes, thank you are correct! It is exactly what I want to do and your codes works.

    You are awesome

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  9. #9
    Join Date
    Jan 2013
    Location
    Chesapeake, VA
    Posts
    30
    Hi, A problem I am having is the Combo Box list is showing Materials in the quantity field which are equal to zero. This why I test result for zero

    Can you help with applying a filter so that any material with a quantity of zero is not shown. Not sure where is the best place to apply this filter? In the combo box property sheet or in the code?

    My logic tells me that since this module is triggered on afterUpdate it too late to filter the material shown in the Combo Box list.

    I hope this makes sense. FYI my code to date is the following:


    Code:
    Private Sub cboMaterial_ID_AfterUpdate()
    Dim Number As Integer
    Dim NumResult As Integer
    Dim result As Integer
    Dim strSQL As String
    
    ' This Sub routine allows a user to select a material item  from a Combo Box list.  It then checks the quantity value of a material to be sure it is not zero.
    ' The Sub then prompts the user for the desired quantity  and updates the quantity field as  required
    
    result = Me![cboMaterial_ID].Column(2)
        If result = 0 Then
        MsgBox ("No more materials available")
        GoTo endtest
            Else
                Number = InputBox("Enter the Quantity,") 'Handle the error on cancel
                NumResult = result - Number
                strSQL = "UPDATE Materials SET Quantity = " & NumResult & " WHERE Material_ID = " & Me.cboMaterial_ID
                ' Use Material_ID = '" & Me.cboMaterial_ID & "'" if Material_ID is not numeric.
                CurrentDb.Execute strSQL, dbFailOnError
                ' To update the Combo, use:
                Me.cboMaterial_ID.Requery
                MsgBox ("Current Value of Quantity  " & NumResult)
    endtest:
    End If
    End Sub

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by back2basics View Post
    Hi, A problem I am having is the Combo Box list is showing Materials in the quantity field which are equal to zero. This why I test result for zero

    Can you help with applying a filter so that any material with a quantity of zero is not shown. Not sure where is the best place to apply this filter? In the combo box property sheet or in the code?
    If you don't want Materials whith a quantity of zero appear in the combo, you need to change the RowSource property of this control. As far as I know, a ComboBox control does not have a Filter property. This can be done dynamically if needed, for instance after setting a quantity to zero:
    Code:
    Me.cboMaterial_ID.RowSource = "SELECT * FROM Materials WHERE Quantity > 0;"
    Note: This is just an example as I don't know what the combo actually displays nor where it actually fetch its data from.

    Alternatively, you can also create a permanent query with the same condition (Quantity > 0) and use the Requery method of the combo:
    Code:
    Me.cboMaterial_ID.Requery
    Something else that's not directly related to your question rendered me perplex, a little bit. In the following segment of code:
    Code:
        If result = 0 Then
        MsgBox ("No more materials available")
        GoTo endtest
            Else
                Number = InputBox("Enter the Quantity,") 'Handle the error on cancel
                NumResult = result - Number
                strSQL = "UPDATE Materials SET Quantity = " & NumResult & " WHERE Material_ID = " & Me.cboMaterial_ID
                ' Use Material_ID = '" & Me.cboMaterial_ID & "'" if Material_ID is not numeric.
                CurrentDb.Execute strSQL, dbFailOnError
                ' To update the Combo, use:
                Me.cboMaterial_ID.Requery
                MsgBox ("Current Value of Quantity  " & NumResult)
    endtest:
    End If
    Why do you use a Goto instruction? This is useless as everything between Else and End If will never be executed when the conditional expression (If result = 0) is True? The normal construction is:
    Code:
    If (conditional expression) Then
        ' 
        ' Instruction(s) to be executed when
        ' the conditional expression is True.
        '
    Else
        ' 
        ' Instruction(s) to be executed when
        ' the conditional expression is False.
        '
    End If
    Have a nice day!

  11. #11
    Join Date
    Jan 2013
    Location
    Chesapeake, VA
    Posts
    30
    Quote Originally Posted by Sinndho View Post
    If you don't want Materials whith a quantity of zero appear in the combo, you need to change the RowSource property of this control. As far as I know, a ComboBox control does not have a Filter property. This can be done dynamically if needed, for instance after setting a quantity to zero:
    Code:
    Me.cboMaterial_ID.RowSource = "SELECT * FROM Materials WHERE Quantity > 0;"
    This worked very well, Thank you. The actual statement in the row source is: SELECT [materials].[Material_ID], [materials].[Material_name], [materials].[Quantity] FROM materials WHERE [materials].[Quantity] > 0 ORDER BY [Material_name]; note that I received help on this.
    Note: This is just an example as I don't know what the combo actually displays nor where it actually fetch its data from.

    Alternatively, you can also create a permanent query with the same condition (Quantity > 0) and use the Requery method of the combo:
    Code:
    Me.cboMaterial_ID.Requery
    Something else that's not directly related to your question rendered me perplex, a little bit. In the following segment of code:
    Code:
        If result = 0 Then
        MsgBox ("No more materials available")
        GoTo endtest
            Else
                Number = InputBox("Enter the Quantity,") 'Handle the error on cancel
                NumResult = result - Number
                strSQL = "UPDATE Materials SET Quantity = " & NumResult & " WHERE Material_ID = " & Me.cboMaterial_ID
                ' Use Material_ID = '" & Me.cboMaterial_ID & "'" if Material_ID is not numeric.
                CurrentDb.Execute strSQL, dbFailOnError
                ' To update the Combo, use:
                Me.cboMaterial_ID.Requery
                MsgBox ("Current Value of Quantity  " & NumResult)
    endtest:
    End If
    Why do you use a Goto instruction? This is useless as everything between Else and End If will never be executed when the conditional expression (If result = 0) is True?
    I agree, this and entire statement is not needed, especially now that the filter is working. FYI, the reason it was there is because the test was previously failing and I was doing some tests in debug.
    The normal construction is:
    Code:
    If (conditional expression) Then
        ' 
        ' Instruction(s) to be executed when
        ' the conditional expression is True.
        '
    Else
        ' 
        ' Instruction(s) to be executed when
        ' the conditional expression is False.
        '
    End If
    I totally and really appreciate your help. You are very good with this program.

    Dale

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •