Dcount based on where in search condition 2

[cdck]

I am working on a loop until statement to copy records into a table based on records already in that table. The loop will continue until it reaches a point where no more records can be found that still need to be copied.

The section of code I am working with so far is as follows:

[tt]
AloopStop = "N"

Do Until AloopStop = "Y"

If DCOUNT(fcomponent,inboms, fparent IN (SELECT fcomponent FROM tblBomTemp WHERE levelNum = " & levelNum & ")) = 0 then AloopStop = "Y"

levelNum = levelNum + 1

[/tt]Obviously, there will be a SQL insert statement here, which is not yet written[tt]

Loop
[/tt]
As you can see, I am trying to run a check at the beginning of each loop that determines if there are 0 records that still need to be inserted into the table, and if that count is 0, then it causes the loop to end.

I am sure that you can see my problem - the Dcount function does not like the "In (SELECT" search condition. As I click off of this line, the debugger highlights the word IN and states "Compile Error: Expected: list seperator or )". I can't set the search condition up as a separate query, because the levelNum in that query needs to change each time the loop iterates.

I am an absolute newbie in VBA coding - and am stumbling partly because I am used to VBScript coding on web pages, and things that I expect to be able to do don't work.

Can anyone clear my cobwebs for me?

Cheryl dc Kern

 

[PHV]

DCount("fcomponent", "inboms", "fparent IN (SELECT fcomponent FROM tblBomTemp WHERE levelNum = " & levelNum & ")")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

In your Dcount you are missing a double quote. The criteria or thrird paramter takes a string...

Instead of:

fparent IN (SELECT fcomponent FROM tblBomTemp WHERE levelNum = " & levelNum & "

Use:

"fparent IN (SELECT fcomponent FROM tblBomTemp WHERE levelNum = """ & levelNum & """"

I assumed that levelNum is text and therefore you needed quotes and that you were trying to put them in.

 

[lameid]

PHV and I cross posted... I missed the right parenthesis.

I think PHV has what you want.

 

[cdck]

Thank you both, it's amazing what a little punctuation can do!

Cheryl dc Kern