Solved Command Button to Update Recordset in Subform (1 Viewer)

[Tammysdatabase]

I have developed a PO system in my database that works beautifully, however, i'm stuck on the proper code for a command button. I want the command button to "Receive All".

I have a Mainform: Receiving
that has a
Subform: PORecSubfrm

I have placed a command button on the mainform that I would like to have the "Qty Received" field be updated to match the "Quantity Ordered" field, thus will trigger the PO to be marked as "Received" (this part is working in the "after update" for the "Quantity Received" field).

My subform is in datasheet view and filters based on the purchase order number chosen on the mainform.

What I'm grappling with is do I use the control names or the actual field names in my command button code.....I've tried it both ways and can't get it to work and I've tried different ways of writing the code (see below).

My control names on the subform are: QtyReceived & QtyOrdered (fields names for these are mentioned above)

I've tried a couple different ways and I'm not having any success.

Private Sub cmdReceiveAll_Click()

Dim rs As DAO.Recordset

Set rs = Forms!Receiving!PORecSubfrm.Form.RecordsetClone

With rs

    If Not .BOF Then
        .MoveFirst
            !QtyReceived = QtyOrdered
        .Update
    End If
    
End With

Set rs = Nothing

End Sub

I have also tried......

Private Sub cmdReceiveAll_Click()

Dim rs As DAO.Recordset
Set rs = Me.PORecSubfrm.Form.RecordsetClone

With rs

If Not .BOF And Not .EOF Then
.MoveLast
.MoveFirst

If .Updatable Then
.Edit
!QtyReceived = QtyOrdered
.Update
End If

End If
.Close
End With

Set rs = Nothing

End Sub

Where am I going wrong?? The code in both of these scenarios gets hung up at the !QtyReceived = QtyOrdered line.

 

[theDBguy]

Hi. When you say the code gets hung up, what does the error message say?

 

[Tammysdatabase]

I'm getting a run-time error 3265, Item not found in this collection
When I choose debug that's the line of code that's highlighted.

I currently have the first code I posted in there, but if I try the other bit of code I get essentially the same thing happen. It just seems to be that one line of code.

Thanks for the reply BTW.

 

[Minty]

Why not just run a single update query on pressing the button.

Update YourTable SET QtyReceived = QtyOrdered WHERE PONumber = YourPONumber

Simples, no recordset needed.

 

[theDBguy]

I'm getting a run-time error 3265, Item not found in this collection
When I choose debug that's the line of code that's highlighted.

I currently have the first code I posted in there, but if I try the other bit of code I get essentially the same thing happen. It just seems to be that one line of code.

Thanks for the reply BTW.

Hi. Thanks for the clarification. So, where is this code located (main form?) and where is the control QtyOrdered located (subform?)?

 

[Tammysdatabase]

The command button is located on the mainform. The control QtyOrdered (and QtyReceived) is on the subform.

 

[theDBguy]

The command button is located on the mainform. The control QtyOrdered (and QtyReceived) is on the subform.

Ah, that's what I thought. In that case, you may have to try something like this.

!QtyReceived=Me.SubformControlName.Form!QtyOrdered

However, I do agree with @Minty that using an UPDATE query might be easier/simpler. Cheers!

 

[Minty]

I think the issue is that your field names (in the recordset) are not the same as your control names?

The rs will have [Qty Received] as the field name if your description is accurate.
Ditto the Ordered field.

 

[Tammysdatabase]

Why not just run a single update query on pressing the button.

Update YourTable SET QtyReceived = QtyOrdered WHERE PONumber = YourPONumber

Simples, no recordset needed.

Thanks for the suggestions!! I'll give it a try. Simple is usually better....LOL

I'll let you know what happens.

And Thanks to theDBguy for his help too!!

 

[theDBguy]

Thanks for the suggestions!! I'll give it a try. Simple is usually better....LOL

I'll let you know what happens.

And Thanks to theDBguy for his help too!!

Hi. We're happy to assist. Good luck with your project.

 

[Tammysdatabase]

Thank you to both of you!! Minty's suggestion worked great! It was so simple! I'm learning as I go and have just learned that it's easy to overthink sometimes....good lesson!!