Solved

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

Posted on 2006-07-05
14
495 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

708 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

21 Experts available now in Live!

Get 1:1 Help Now