CODEWITHSUNDEEP
ms-accessvbams-access-2010
spriore
spriore

Reputation: 613

Microsoft Access VBA Error 461

I cannot figure out why I keep getting error #461, "Method or data member not found".

Private Sub cmdUpdate_Click()
    CurrentDb.Execute "UPDATE Parts " _
        & "SET [In-Week 1] = " & Me.in1 & ", [In-Week 2] = " & Me.in2 & ", [In-Week 3] = " & Me.in3 _
        & ", [In-Week 4] = " & Me.in4 & ", [In-Week 5] = " & Me.in5 & ", [In-Week 6] = " & Me.in6 & ", [In-Week 7] = " & Me.in7 _
        & ", [In-Week 8] = " & Me.in8 & ", [In-Week 9] = " & Me.in9 & ", [In-Week 10] = " & Me.in10 & ", [In-Week 11] = " & Me.in11 _
        & ", [In-Week 12] = " & Me.in12 & ", [In-Week 13] = " & Me.in13 & ", [In-Week 14] = " & Me.in14 & ", [In-Week 15] = " & Me.in15 _
        & ", [In-Week 16] = " & Me.in16 & ", [In-Week 17] = " & Me.in17 & ", [In-Week 18] = " & Me.in18 & ", [In-Week 19] = " & Me.in19 _
        & ", [In-Week 20] = " & Me.in20 & ", [In-Week 21] = " & Me.in21 & ", [In-Week 22] = " & Me.in22 & ", [In-Week 23] = " & Me.in23 _
        & ", [In-Week 24] = " & Me.in24 & ", [In-Week 25] = " & Me.in25 & ", [In-Week 26] = " & Me.in26 & ", [In-Week 27] = " & Me.in27 _
        & ", [In-Week 28] = " & Me.in28 & ", [In-Week 29] = " & Me.in29 & ", [In-Week 30] = " & Me.in30 & ", [In-Week 31] = " & Me.in31 _
        & ", [In-Week 32] = " & Me.in32 & ", [In-Week 33] = " & Me.in33 & ", [In-Week 34] = " & Me.in34 & ", [In-Week 35] = " & Me.in35 _
        & ", [In-Week 36] = " & Me.in36 & ", [In-Week 37] = " & Me.in37 & ", [In-Week 38] = " & Me.in38 & ", [In-Week 39] = " & Me.in39 _
        & ", [In-Week 40] = " & Me.in40 & ", [In-Week 41] = " & Me.in41 & ", [In-Week 42] = " & Me.in42 & ", [In-Week 43] = " & Me.in43 _
        & ", [In-Week 44] = " & Me.in44 & ", [In-Week 45] = " & Me.in45 & ", [In-Week 46] = " & Me.in46 & ", [In-Week 47] = " & Me.in47 _
        & ", [In-Week 48] = " & Me.in48 & ", [In-Week 49] = " & Me.in49 & ", [In-Week 50] = " & Me.in50 & ", [In-Week 51] = " & Me.in51 & ", [In-Week 52] = " & Me.in52 & "  " _
        & "WHERE [Part #] = '" & txtFindPart & "';"

After I click my button it runs the error and highlights Private Sub cmdUpdate_Click() in yellow and .in4 in the normal cursor highlight.

I have made sure all parts are typed correctly and that they exist in the form linked to the VBA script.

Upvotes: 2

Views: 641

Answers (1)

HansUp
HansUp

Reputation: 97101

It seems Access got confused with that code which builds the monstrous UPDATE string. And it complained about Me.in4 when the real problem is that Me.in5 does not exist. When you examine the text box you think is named in5, you will find its name is actually Text10in5.

I tracked down the problem by adding this code to the beginning of cmdUpdate_Click(). The first version threw an error when i = 5, so I put that value in a Case statement to ignore it and see whether any of the remaining values threw a similar error. None of the other values threw an error.

Dim i As Long
For i = 1 To 52
    Select Case i
    Case 5
        ' pass: Text10in5
    Case Else
        Debug.Print Me.Controls("in" & i).Name
    End Select
Next

Although that was a useful debugging technique, the actual reason I showed it to you is because I think you should use a similar strategy to update Parts. Open a DAO.Recordset with your target Parts row. Then loop through those 52 text boxes and store their values to the corresponding Parts fields ...

For i = 1 To 52
    rs.Fields("In-Week " & i).Value = Nz(Me.Controls("in" & i).Value, 0)
Next

That should work assuming you rename Text10in5 to in5.

Upvotes: 1

Related Questions