Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop Through Subform (A2k)

    You could use the Concat function from <post:=301,393>post 301,393</post:>. You'd have to specify a condition on EST_NO (referring to the value from the main form) and on NET_CST.

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Loop Through Subform (A2k)

    I have a form (frmPartCheck) with a subform (sbfPart) which returns all parts on an estimate relating to one number.
    The subforms record source is :
    SELECT qryPart.EST_NO, qryPart.SUP_NO, qryPart.ITM_CDE, qryPart.ITM_DES, qryPart.NET_CST, qryPart.RET_CST FROM qryPart;

    Also on the form is a command button :

    <pre>DoCmd.SendObject , , , "Nick@someemail.co.uk;Paul.tunnicliffe@someemail.c o.uk", _
    "Dave@someemail.co.uk", , "Parts Not In Stock(Vehicle In Strip)" & _
    " " & Me.EST_NO, "This vehicle has been stripped today and some parts have not arrived yet." & vbCrLf & _
    "If there is a problem could you let me know at the earliest convenience" & vbCrLf & _
    "Thank You"</pre>


    The subform when shown lists the parts relating to the estimate number and with conditional formatting, turns any field when the NET_CST = 0 to red, therefore informing the user that items in red are not in stock.
    I would like to loop through the sbfPart and include ITM_DES to the mail when NET_CST =0

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop Through Subform (A2k)

    Not sure Hans if thats what I was after.
    I'll have a think about it and study it further.

    Thanks for the link anyway.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop Through Subform (A2k)

    If it turns out not to be what you want, please explain what you need.

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop Through Subform (A2k)

    Heres the form the user sees, red items are the ones which need to be in the email body.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop Through Subform (A2k)

    Try the code below. You must have copied the Concat function to a standard module, and you need a reference (in Tools | References) to the Microsoft DAO 3.6 Object Library:
    <code>
    DoCmd.SendObject , , , "Nick@someemail.co.uk;Paul.tunnicliffe@someemail.c o.uk", _
    "Dave@someemail.co.uk", , "Parts Not In Stock(Vehicle In Strip) " & Me.EST_NO, _
    "This vehicle has been stripped today and the following parts have not arrived yet:" & _
    vbCrLf & Concat("qryPart", "ITEM_CDE", "EST_NO = " & Chr(34) & Me.EST_NO & Chr(34) & _
    " AND NET_CST = 0") & vbCrLf & _
    "If there is a problem could you let me know at the earliest convenience." & vbCrLf & _
    "Thank You"</code>

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop Through Subform (A2k)

    Hans I'm getting an error :

    Too few parameters on the line
    <pre> Set rst = dbs.OpenRecordset(strSQL)</pre>


  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop Through Subform (A2k)

    What is the SQL for qryPart?

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop Through Subform (A2k)

    SELECT PRT.EST_NO, PRT.SUP_NO, PRT.ITM_CDE, PRT.ITM_DES, PRT.NET_CST, PRT.RET_CST
    FROM PRT;

    Hans

    I've used the function from post No 301,393 is this the correct one ?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop Through Subform (A2k)

    Don't see anything special there. I'm afraid it's getting too difficult to troubleshoot without seeing the database. Could you post a stripped down and zipped copy?

    (Yes, the function from <post#=301393>post 301393</post#> is the correct one)

  11. #11
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop Through Subform (A2k)

    Hans

    The DB is far too complicated to zip a small 100k attachment.
    Leave it with me, I'll have play about with the query.

    Thanks

  12. #12
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop Through Subform (A2k)

    With a little bit of tweaking the query and code, some extra help from Hans, here is the solution which looks pretty neat when compiled within an email:

    <pre>DoCmd.SendObject , , , "Nick@someemail.co.uk;Paul.tunnicliffe@someemail.c o.uk", _
    "Dave@someemail.co.uk", , "Parts Not In Stock ( Vehicle In Strip ) " & Me.EST_NO, _
    "This vehicle has been stripped today and the following parts have not arrived yet:" & _
    "************************************************* *********************************" & vbCrLf & _
    vbCrLf & Concat("qryPartNotIn", "Desc", "Est = " & Me.EST_NO & _
    " AND Nett = 0", , vbCrLf) & vbCrLf & vbCrLf & _
    "************************************************* *********************************" & vbCrLf & _
    "If there is a problem could you let me know at the earliest convenience." & vbCrLf & _
    "Thank You"</pre>



    Once again, Hans's help has lead to a fantastic solution.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop Through Subform (A2k)

    I'm glad you got it to work! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  14. #14
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop Through Subform (A2k)

    Hans

    Can I tweak this code as follows, not sure of the syntax:

    <pre>vbCrLf & Concat("qryPartNotIn", "Desc", "Est = " & Me.EST_NO & _
    " AND Nett = 0 and or RET = NETT", , vbCrLf) & vbCrLf & vbCrLf & _</pre>


  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop Through Subform (A2k)

    You can't use and or, instead use either and or or. Keep in mind that and has precedence over or, so A and B or C is interpreted as (A and [img]/forums/images/smilies/cool.gif[/img] or C. If you want A and (B or C) you must use the parentheses exactly like that.

    Does that help?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •