access sub-form with While...Wend to update main form

Hi,
this is my problem: I have a form for orders, when I click on a "partial delivery" button it updates the stock level of a few selected fields of the sub form. I then add an info to the "actions" field of the main form, saying that the partial delivery button was clicked. This works, but now I want to add more info to the "actions" field, telling me exactly which products were processed. I've tried this:

-------------------------------------------------------------
Dim rs As Recordset
Set rs = Me.orders_subform.Form.RecordsetClone
rs.MoveFirst
While Not rs.EOF
  If rs.Fields("Deliver_This") Then
    [Orders.Actions] = ([Orders.Actions] & Str(rs.Fields("Quantity")) & "x " & rs.Fields("Produktnumber") & vbNewLine)
  End If
rs.MoveNext
Wend
rs.Close
-------------------------------------------------------------

but I keep getting a "Type Mismatch" error. [Orders.Actions] is a memo field, [Quantity] a number and [Productnumber] text. What am I doing wrong?
mpnetAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rockiroadsConnect With a Mentor Commented:
If it complains about DAO, go to Tools/References and check the latest version of the DAO Object Library

then run your code, with rs defined as DAO.Recordset
0
 
Raynard7Commented:
Can you try?

  If rs.Fields("Deliver_This") Then
    rs.Edit
    [Orders.Actions] = ([Orders.Actions] & Str(rs.Fields("Quantity")) & "x " & rs.Fields("Produktnumber") & vbNewLine)
    rs.Update
  End If
0
 
Raynard7Commented:
also [Orders.Actions] should be [Orders].[Actions]
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
rockiroadsCommented:
Note, if u ran this again, wont u keep increasing Orders.Actions with possibly the same info?
Or is that the intention

Is orders.actions a field on the subform? Which means its also a field in that recordset?
Or is it on the mainform?

Try this

If on the subform, try this
me.orders_subform.form..Actions = me.orders_subform.form.Actions & rs!Quantity & "x " & rs!Produktnumber & vbcrlf

Or, if u use via recordset, as Raynard7 has shown but do this
rs!Actions = rs!Actions & rs!Quantity & "x " & rs!Produktnumber & vbcrlf

If Actions is on the mainform

Me.Actions = Me.Actions & rs!Quantity & "x " & rs!Produktnumber & vbcrlf




0
 
rockiroadsCommented:
Ooops got a typo in ProductNumber in last post



Now do u always want the last item to be a new line?
U can try this instead

if len(trim$(Me.Actions))>0 then Me.Actions = Me.Actions & vbcrlf
Me.Actions = Me.Actions & rs!Quantity & "x " & rs!Productnumber



0
 
mpnetAuthor Commented:
thanks both for your quick reply.

Raynard7: the "rs.Edit" is giving me a "Invalid use of property" error. I tried "rs.EditMode", it brings the same error.

rockiroads: Actions is a field on the main form, and it stores every action performed on the form (like adding new products, delivering the order, getting the payment etc.). But when I run
Me.Actions = Me.Actions & rs!Quantity & "x " & rs!Produktnumber & vbcrlf
I still get "Type Mismatch"
0
 
rockiroadsCommented:
ok, try this then

Me.Actions = Me.Actions & CSTR(rs!Quantity) & "x " & CSTR(rs!Productnumber)
0
 
mpnetAuthor Commented:
no, still get "Type Mismatch"
0
 
mpnetAuthor Commented:
I just tried something, I took out the whole While....Wend statement, and i still get Type Mismatch. The code now is:

        Dim rs As Recordset
        Me.orders_subform.Form.RecordsetClone
        rs.MoveFirst
        rs.Close


Any ideas what I'm doing wrong here?
0
 
rockiroadsCommented:
ah ha, its this

set rs = Me.orders_subform.Form.RecordsetClone



doh, how can I miss that!
0
 
rockiroadsCommented:
depending on your references, u may need to define rs as dao.recordset
e.g.
dim rs as DAO.Recordset


If u want to modify values from your recordset, then u have to use rs.Edit. But since u are not, I dont think u need to use it
0
 
mpnetAuthor Commented:

sorry, that was my typing mistake, I already had set rs=   !
I added a MsgBox before every statement, the "rs.MoveFirst" is giving the Type Mismatch error.
0
 
mpnetAuthor Commented:

excellent, thanks, it now works!
0
 
rockiroadsCommented:
No probs
Most likely the recordset u was picking up was probably ADO
Bounded forms use dao so I guess u have to explicitly specify DAO.Recordset

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.