Solved

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

Posted on 2006-07-05
14
508 Views
Last Modified: 2006-11-18
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
Comment
Question by:mpnet
  • 7
  • 5
  • 2
14 Comments
 
LVL 35

Expert Comment

by:Raynard7
ID: 17040854
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
 
LVL 35

Expert Comment

by:Raynard7
ID: 17040859
also [Orders.Actions] should be [Orders].[Actions]
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17040879
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 17040886
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
 

Author Comment

by:mpnet
ID: 17040923
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 17040929
ok, try this then

Me.Actions = Me.Actions & CSTR(rs!Quantity) & "x " & CSTR(rs!Productnumber)
0
 

Author Comment

by:mpnet
ID: 17040947
no, still get "Type Mismatch"
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:mpnet
ID: 17040972
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 17040986
ah ha, its this

set rs = Me.orders_subform.Form.RecordsetClone



doh, how can I miss that!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17040989
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
 

Author Comment

by:mpnet
ID: 17040995

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
 
LVL 65

Accepted Solution

by:
rockiroads earned 125 total points
ID: 17040997
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
 

Author Comment

by:mpnet
ID: 17041313

excellent, thanks, it now works!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17041421
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now