Critera Limitations

[Wicklund]

I have run into a wall, as far as the number of 'options' I can have in a critera option.

I am in a loop, pulling information out of a table, and I want to insure that I do not pull information for the same record twice, so I am adding the Key Field to the Criteria after each pass (see code below).

Dim stLinkCriteria As String

tempIG = DLookup("[IG Number]", "[Project Status - Tbl]", stLinkCriteria)

tempNew = DLookup("[New Parts]", "[Project Status - Tbl]", stLinkCriteria)

stLinkCriteria = stLinkCriteria & " and [IG Number] <> '" & tempIG & "'"

The problem is that there are so many records, that I am hitting the max allowable options for the search. I am getting the following error:

"Query is too complex"

Any ideas??

 

[fmm]

Add a Yes/No field to your table and set it to -1 when you've "grabbed" a record. Modify the query to excude those records. When you're finished, set all records back to 0.

 

[Wicklund]

fmm,

Thank you for your help. Now I want to pull two pieces of information from the 'open' record set [tempNew] & [tempIG] I am currently using the DLookup Function, but am running into a sorting problem. Can I pull the information directly from the 'active' record in the open recordset?

Set rst = CurrentDb.OpenRecordset("Project Status - Tbl", dbOpenDynaset)

For z = 1 To NumOfRecords
rst.FindFirst stLinkCriteria
tempNew = DLookup("[New Parts]", "[Project Status (Sorted) - Qry]", stLinkCriteria)
tempIG = DLookup("[IG Number]", "[Project Status (Sorted) - Qry]", stLinkCriteria)
With rst
.Edit
![Checker] = -1
.Update
End With

Thank you again,

 

[fmm]

My head is foggy from a cold, but try this:

dim tempNew as whatever 'new code
dim tempIG whatever 'new code


Set rst = CurrentDb.OpenRecordset("Project Status - Tbl", dbOpenDynaset)

For z = 1 To NumOfRecords
rst.FindFirst stLinkCriteria
'tempNew = DLookup("[New Parts]", "[Project Status (Sorted) - Qry]", stLinkCriteria) 'disabled code
'tempIG = DLookup("[IG Number]", "[Project Status (Sorted) - Qry]", stLinkCriteria) 'disabled code
With rst
tempNew = ![New Parts] 'new code
tempIG = ![IG Number] 'new code
.Edit
![Checker] = -1
.Update
End With


That ought to do it.

 

[Wicklund]

fmm,

Thank you, but I tried that one. When I ran the code I got:

"Compile Error: Invalid or unqualified reference"

Any other thoughts???

 

[fmm]

Not right off of the top of my head. Try putting a breakpoint in at or before the WITH statement, and watch the variables/fields as the code runs.

Good Luck!

 

[Wicklund]

fmm,

My bad. I had the: tempNew = ![New Parts] & tempIG = ![IG Number] on the wrong side of the With rst statement. When I moved them, the code ran fine.

Thank you for your help!!