Help with Code

[tucker61]

I have the below code in my form, which basically, looks to see if a supplier is on a targeted list, creates an new table and then populates my form.

If the supplier performance improves, then i remove the supplier from the list by adding a date removed into a Date_Removed field.

Problem is that the code below does not refer to that field, so i want to change the code to check that the date_removed field is null.

My Date_Removed field is in the tblqcsupplierwatch table. I have tried using various "AND" commands, but to no avail.

I Figure it needs to go in the IIF(NZ(Dcount, but cannot work out where to put the additional code.

IIf(Nz(DCount('*','tblqcsupplierwatch','Sup_Code=''' & [Forms]![frmSupplierWatch]![CboxSupplier] & ''''),0)=0,0,-1)

DoCmd.RunSQL "SELECT DISTINCT tblProductSupplier.Sup_Code, tblCatalog.Department, tblCatalog.Category, IIf(Nz(DCount('*','tblqcsupplierwatch','Sup_Code=''' & [Forms]![frmSupplierWatch]![CboxSupplier] & ''''),0)=0,0,-1) AS InGroup INTO TEMP_SupplierWatch " & vbCrLf & _
"FROM tblProductSupplier LEFT JOIN tblCatalog ON tblProductSupplier.Cat_No = tblCatalog.Cat_No " & vbCrLf & _
"WHERE (((tblProductSupplier.Sup_Code)=[Forms]![frmSupplierWatch]![CboxSupplier]));"

Any help is appreciated.

 

[Ranman256]

you really shouldnt keep creating tables.
All your tables should already be made and you just empty and append to them.

In queries, you put null in the criteria:
[field] is null

 

[tucker61]

Thanks, I agree with what you are saying, the code has always been like that, don't know why it was developed that way, it it was not me who did the initial development..

I think at this moment in time it would be to complicated for me to change that part of the code

 

[tucker61]

Intotal i have 4 tables that are created when needed, similar to the above..

 

[tucker61]

Anyone else able to help with the above code ?

 

[static]

ingroup isn't joined on any other field, so you are performing the same lookup for every record for no reason.

InGroup = IIf(Nz(DCount("*","tblqcsupplierwatch","Sup_Code='" & [Forms]![frmSupplierWatch]![CboxSupplier] & "' and isdate(Date_Removed)"),0)=0,0,-1)

s=""
s=s & "SELECT DISTINCT "
s=s & " a.Sup_Code, "
s=s & " b.Department, "
s=s & " b.Category, "
s=s & InGroup & " AS InGroup "
s=s & "INTO TEMP_SupplierWatch "
s=s & "FROM "
s=s & " tblProductSupplier a "
s=s & " LEFT JOIN tblCatalog b ON a.Cat_No = b.Cat_No "
s=s & "WHERE "
s=s & " a.Sup_Code = [Forms]![frmSupplierWatch]![CboxSupplier] "

 

[tucker61]

Thanks, but that gave me a couple of error messages around the ingroup code.

I have attached a copy of my database to see if that makes it easier.

 

[tucker61]

with db attached.

 

[static]

It thinks InGroup is a field.
Just add 'Dim Ingroup' or use a different name.

Edit.
Sorry brain fade.