Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2006-07-05
14
Medium Priority
?
542 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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
 

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 500 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

610 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