Loop Through Subform (A2k)

**HansV** · 2006-09-12 07:40

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.

**D Willett** · 2006-09-12 07:42

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

**D Willett** · 2006-09-12 09:31

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.

**HansV** · 2006-09-12 09:47

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

**D Willett** · 2006-09-12 11:22

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

**HansV** · 2006-09-12 11:39

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>

**D Willett** · 2006-09-12 12:13

Hans I'm getting an error :

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

**HansV** · 2006-09-12 12:17

What is the SQL for qryPart?

**D Willett** · 2006-09-12 12:29

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 ?

**HansV** · 2006-09-12 12:31

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)

**D Willett** · 2006-09-12 12:41

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

**D Willett** · 2006-09-12 14:57

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.

**HansV** · 2006-09-12 15:06

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

**D Willett** · 2006-10-25 09:56

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>

**HansV** · 2006-10-25 10:21

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?

Thread: Loop Through Subform (A2k)

Thread Tools

Re: Loop Through Subform (A2k)