Continuous Form Loop VBA Problem (1 Viewer)

mreference

Registered User.
Local time
Today, 20:39
Joined
Oct 4, 2010
Messages
137
I have a parent form with a command button cmdBookInAllStock_Click() that once clicked it copies the data from the continuous subform field [Order_Qty] and copy that value to another subform field [This_Deliv_Qty].

The code I have to do that is:

Me.frmPurchase_Order_Detail.Form.This_Deliv_Qty.Value = Me.frmPurchase_Order_Detail.Form.Order_Qty.Value

It works for the first record in the recordset but not the rest.

Could someone provide me some assistance.

It's easier for me to to do this and alter the small number of items not delivered, rather than input everything that has been delivered.

thank you
 

RuralGuy

AWF VIP
Local time
Today, 14:39
Joined
Jul 2, 2005
Messages
13,826
Can you write an Update query that does this for you?
 

mreference

Registered User.
Local time
Today, 20:39
Joined
Oct 4, 2010
Messages
137
I need to do this on a regular basis each day as orders come in, would an update query solve this and can it be run from a command button?

Any pointers would be appreciated.
 

RuralGuy

AWF VIP
Local time
Today, 14:39
Joined
Jul 2, 2005
Messages
13,826
The answer to both questions is YES. I'll see if I can work up something for you.
 

RuralGuy

AWF VIP
Local time
Today, 14:39
Joined
Jul 2, 2005
Messages
13,826
I can't believe how poor my coding skills have become but this UNTESTED code should get you on your way:
Code:
Private Sub cmdBookInAllStock_Click()
    On Error GoTo cmdBookInAllStock_Click_Err
    Dim rs As DAO.Recordset
    Dim CurControl As Control
    CurControl = Screen.ActiveControl
    
    Set rs = Me.frmOrderDetailSubSubForm.Form.Recordset

    rs.MoveFirst
    Do While Not rs.EOF
        Me.frmOrderDetailSubSubForm.Form!This_Deliv_Qty = Me.frmOrderDetailSubSubForm.Form!Order_Qty
        rs.MoveNext
    Loop
    rs.MoveFirst
    Set rs = Nothing
    CurControl.SetFocus

cmdBookInAllStock_Click_Exit:
    Exit Sub

cmdBookInAllStock_Click_Err:
    MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
    Resume cmdBookInAllStock_Click_Exit

End Sub
 

mreference

Registered User.
Local time
Today, 20:39
Joined
Oct 4, 2010
Messages
137
Thank you for supplying the code, I have given it a try this morning and added a message box line as a precaution.

Code:
Private Sub cmdBookInAllStock_Click()

Dim LResponse As Integer

LResponse = MsgBox("Are you sure you want to do this?", vbYesNo, "Continue")

If LResponse = vbYes Then
   
    On Error GoTo cmdBookInAllStock_Click_Err
    Dim rs As DAO.Recordset
    Dim CurControl As Control
    CurControl = Screen.ActiveControl
    
    Set rs = Me.frmPurchase_Order_Detail.Form.Recordset

    rs.MoveFirst
    Do While Not rs.EOF
        Me.frmPurchase_Order_Detail.Form!This_Deliv_Qty = Me.frmPurchase_Order_Detail.Form!Order_Qty
        rs.MoveNext
    Loop
    rs.MoveFirst
    Set rs = Nothing
    CurControl.SetFocus

cmdBookInAllStock_Click_Exit:
    Exit Sub

cmdBookInAllStock_Click_Err:
    MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
    Resume cmdBookInAllStock_Click_Exit

Else
    Me.OrderNotes.SetFocus
End If
End Sub

I get this error message even if I remove the Message Box code:

Error: 91
Object variable or With block variable not set

I have a google search but didn't really grasp what it was telling me.

Is this something that you can easily spot and fix?
 

RuralGuy

AWF VIP
Local time
Today, 14:39
Joined
Jul 2, 2005
Messages
13,826
Try this:
Code:
Private Sub cmdBookInAllStock_Click()

Dim LResponse As Integer

LResponse = MsgBox("Are you sure you want to do this?", vbYesNo, "Continue")

If LResponse = vbYes Then
   
    On Error GoTo cmdBookInAllStock_Click_Err
    Dim rs As DAO.Recordset
    Dim CurControl As Control
    CurControl = Screen.ActiveControl
    
    Set rs = Me.frmPurchase_Order_Detail.Form.Recordset

    rs.MoveFirst
    Do While Not rs.EOF
        Me.frmPurchase_Order_Detail.Form!This_Deliv_Qty = Me.frmPurchase_Order_Detail.Form!Order_Qty
        rs.MoveNext
    Loop
    rs.MoveFirst
    Set rs = Nothing
    CurControl.SetFocus
[B][COLOR="Red"]Else
    Me.OrderNotes.SetFocus
End If[/COLOR][/B]

cmdBookInAllStock_Click_Exit:
    Exit Sub

cmdBookInAllStock_Click_Err:
    MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
    Resume cmdBookInAllStock_Click_Exit

End Sub
 

mreference

Registered User.
Local time
Today, 20:39
Joined
Oct 4, 2010
Messages
137
Thank you, I removed all the code relating to the message box I added in and the error 91 still happened.

I'm presuming it must be something to do with the code you wrote for me, but I'm not as uptodate as you are so do not know what part of the code to tweak.

I think that part of the Else If works ok, as when I clicked No, it did what it was supposed to do and setfocus in the correct field.

I bet we are so close to resolving this.
 

RuralGuy

AWF VIP
Local time
Today, 14:39
Joined
Jul 2, 2005
Messages
13,826
Did you just try the code that I posted last?
 

mreference

Registered User.
Local time
Today, 20:39
Joined
Oct 4, 2010
Messages
137
I did try, see attached code and error message. Right click on image and open in new tab if not displayed

Image 1
Image 2



Thank you
 

RuralGuy

AWF VIP
Local time
Today, 14:39
Joined
Jul 2, 2005
Messages
13,826
Does it compile? Have you single stepped the code yet?
 

mreference

Registered User.
Local time
Today, 20:39
Joined
Oct 4, 2010
Messages
137
I clicked on Compile and nothing flashed up in the Editor, I then clicked on Step Into and again nothing happens, cursor stays where it is. Not sure what I am supposed to be seeing either.
 

RuralGuy

AWF VIP
Local time
Today, 14:39
Joined
Jul 2, 2005
Messages
13,826
Put your cursor just outside the left margin while looking at the code at the "If LResponse = vbYes Then" line and left click. A dot appears that is a break point. Then run the form again and it should stop at the dot. F8 key will single step the code.
 

mreference

Registered User.
Local time
Today, 20:39
Joined
Oct 4, 2010
Messages
137
I tried that, I got a red dot appear and then I run the form again.

It stopped at the Red Dot first, that line was in yellow.

I pressed F8 and the following lines went yellow (in order)

On Error GoTo cmdBookInAllStock_Click_Err

CurControl = Screen.ActiveControl

MsgBox "Error: " & Err.Number & vbCrLf & Err.Description

Then the Error 91 message pops up

And the final yellow lines were (in order)

Resume cmdBookInAllStock_Click_Exit

Exit Sub
 

RuralGuy

AWF VIP
Local time
Today, 14:39
Joined
Jul 2, 2005
Messages
13,826
Comment out the "CurControl = Screen.ActiveControl" line and then also the "CurControl.SetFocus" line toward the end. Then click the break point again and it will go away. Then run the form again.
 

Users who are viewing this thread

Top Bottom