Please help with this loop issue - newby question. 1

[bhoran]

I am quite new to this VBA stuff and I have actuallty posted this question in another area but it seems very complicated the way I 1st posted it.

I have written the following code:
*******
Private Sub Command0_Click()
Dim strSQL As String
Dim strOldSQL As String
Dim txtDeptNo As String
Dim intDepNoCount As Integer

txtDeptNo = DLookup("[PRFT CNTRE]", "Department", "REPORTFUNCT = 'BalanceSheet'&quot

Debug.Print txtDeptNo

DoCmd.SetWarnings False
strSQL = "UPDATE DISTINCTROW cc SET cc.[Cost Centre]='" & txtDeptNo & "'"
'change the sql property of the query "qselMyQuery"
strOldSQL = ChangeSQL("010", strSQL)
'your query will now have a new sql value

DoCmd.OpenQuery "010", acViewNormal, acEdit
DoCmd.OpenQuery "BudControlTestcreate", acViewNormal, acEdit
DoCmd.OpenReport "BudCntrlTTEST", acPreview
DoCmd.SetWarnings True

Debug.Print strSQL

End Sub
********

This is working fine but I have to run this for each [PRFT CNTRE] that fits the criteria. As it is text I cannot simply add 1 to it. I somehow need to move onto the next [PRFT CNTRE] that has a REPORTFUNCT = 'BalanceSheet' until there are no more. (I will have other criteria later).

Cheers.

 

[Onyxpurr]

This is what I usually do (unless someone knows how to do it more easily which I'm sure they do by use in the rs object, but this is my lazy way ...

Dim totalrecs as integer, i as integer, donotinclude as string
donotinclude = "REPORTFUNCT = 'BalanceSheet')

totalrecs = DCount("[PRFT CNTRE]", "Department", donotinclude)

For i = 1 to totalrecs
txtDeptNo = DLookup("[PRFT CNTRE]", "Department", donotinclude)
donotinclude = donotinclude & &quot; AND [PRFT CNTRE] <> '&quot; & txtDeptNo & &quot;'&quot;
Next i

Hope this helps (please don't beat me up for not using rs) lol

 

[Onyxpurr]

Oops take out the first ) after 'BalanceSheet'

 

[bhoran]

Thanks Onyxpurr for your prompt responce, I had found a good example on this site after my post using rs - I do not undestand your code and am interested in it.

For i = 1 to totalrecs 'don't understand what this is doing? please explain.

otherwise I like the idea of this code, simple once you know.

Here is what I produced before seeing your post:

Private Sub Command0_Click()
Dim rsPrftCntreNo As Recordset
Dim strSQL As String
Dim strOldSQL As String
Dim txtDeptNo As String

DoCmd.SetWarnings False

Set rsPrftCntreNo = CurrentDb.OpenRecordset(&quot;select [PRFT CNTRE] FROM DEPARTMENT WHERE [REPORTFUNCT]='BalanceSheet';&quot
rsPrftCntreNo.MoveFirst

Do Until rsPrftCntreNo.EOF
txtDeptNo = rsPrftCntreNo![PRFT CNTRE]
Debug.Print txtDeptNo

strSQL = &quot;UPDATE DISTINCTROW cc SET cc.[Cost Centre]='&quot; & txtDeptNo & &quot;'&quot;
'change the sql property of the query &quot;qselMyQuery&quot;
strOldSQL = ChangeSQL(&quot;010&quot;, strSQL)
'your query will now have a new sql value

DoCmd.OpenQuery &quot;010&quot;, acViewNormal, acEdit
DoCmd.OpenQuery &quot;BudControlTestcreate&quot;, acViewNormal, acEdit
DoCmd.OpenReport &quot;BudCntrlTTEST&quot;, acPrint
rsPrftCntreNo.MoveNext

Debug.Print strSQL
Loop
DoCmd.SetWarnings True
End Sub

Yours seems a little simpler.

 

[Onyxpurr]

For i = 1 to totalrecs

Next i

This is a loop. It's preferable to use this loop when you need to increment an integer number.

For i = 1 'initializes i = 1
to totalrecs 'tells it once i is equal to this number, stop
step 2 'optional, default is set to increment by 1, but you can have it increment by other numbers

Next i 'this is where it actually increments i essentially the same as i = i + 1

 

[bhoran]

Excellent that for that, I like the logic. I just couldn't quite get my head around the the capturing of the total records then stepping through.

I now have a better idea on how to approach the issue so your post has been very helpful for the future. Thanks

 

[bhoran]

Sorry to bother you Onyxpurr

I am using your idea for something else but i am a little confused.

What I am trying to build is a string of fields to replace some SQL.

so I am looking for something like this from my string:
[Aug]+[Sep]+[Oct]

I am using the following:

Dim strYTD As String, strdonotinclude As String
Dim intTotalRecs As Integer

intTotalRecs = DCount(&quot;[Months]&quot;, &quot;FYASFPeriod&quot;, &quot;[ASF Period] < Forms!BudCntrl.cmbSelASFPeriod&quot

For i = 1 To intTotalRecs
strYTD = &quot;[&quot; & DLookup(&quot;[Months]&quot;, &quot;FYASFPeriod&quot;, &quot;'& strdonotinclude & '&quot & &quot;] + &quot;
strdonotinclude = &quot; AND [Months] <> '&quot; & strYTD & &quot;'&quot;
Next i

In debug I get:
intTotalRecs of 3 'correct
strYTD as:
[Aug] +
[Aug] +
[Aug] +

I can't quite get it right.

 

[bhoran]

Sorry code shoulod be this:

For i = 1 To intTotalRecs
strYTD = DLookup(&quot;[Months]&quot;, &quot;FYASFPeriod&quot;, &quot;[Months] <> ' & strdonotinclude & '&quot
strdonotinclude = &quot;'&quot; & strYTD & &quot;'&quot;
Debug.Print strdonotinclude
Debug.Print strYTD
Next i

last post was old code.

 

[Onyxpurr]

Glad I could help.

For i = 1 To intTotalRecs
strYTD = DLookup(&quot;[Months]&quot;, &quot;FYASFPeriod&quot;, strdonotinclude)

if isnull(strdonotinclude) then
strdonotinclude = &quot;[Months] <> '&quot; & strYTD & &quot;'&quot;
else
strdonotinclude = strdonotinclude & &quot; AND [Months] <> '&quot; & strYTD & &quot;'&quot;
end if

Debug.Print strdonotinclude
Debug.Print strYTD
Next i

 

[bhoran]

Almost, the if statement didn't work using
isnull(strdonotinclude) as strdonotinclude is not null on the 1st record so I madified it to if i =1 aqnd that seems to work well.

Full code posted below:

For i = 1 To intTotalRecs
strYTD = DLookup(&quot;[Months]&quot;, &quot;FYASFPeriod&quot;, strdonotinclude)

If i = 1 Then
strYTDCalc = &quot;[&quot; & strYTD & &quot;]&quot;
strdonotinclude = &quot;[Months] <> '&quot; & strYTD & &quot;'&quot;
Else
strYTDCalc = strYTDCalc & &quot; + [&quot; & strYTD & &quot;]&quot;
strdonotinclude = strdonotinclude & &quot; AND [Months] <> '&quot; & strYTD & &quot;'&quot;
End If
Next i

I now get [Aug] + [Sep] + [Oct] etc etc

thanks a heap.

 

[bhoran]

p.s. I also had to modify my Dcount statements for when I select the 1st month.

But ssems to be working just fina and dandy now.
Cheers.