Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 543
  • Last Modified:

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?
0
mpnet
Asked:
mpnet
  • 7
  • 5
  • 2
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
rockiroadsCommented:
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
 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now