DLookup that worked in Access 2007 doesn't work in Access 2010

[dharrington]

I'm stumped and am hoping someone can help me. Some time ago I added a private sub to a subform that performs a DLookup in a query that filters on two fields in the subform active record and adds information from the query to a field in the subforms active record and based on what it adds it will update another field before moving on to the next record and then loops until it reaches the end of the subform. I did this because I couldn't figure out how to achieve what I needed Access to do any other way and although it was slow it did the job. The problem is I created this database using Access 2007 and recently upgraded to Access 2010. When I run the private sub it starts just fine but then seems to cause the database to stop running after about 300 to 400 records. I typically run this for between 30,000 and 60,000 records.



Is there an alternative to achieve what I need access to do, possibly one that will work quicker? If so, can you explain what I need to do? Any help is very much appreciated. Here are the details.

Query and Forms:

• Query is called "Dispatch Analysis Auto Rank"
• Form is called "Dispatch Analysis"
• Subform is called "Dispatch Analysis subform"

The query has the following fields:

• ID (key)
• Store #
• Request Type
• Vendor #
• Auto Rank (formula)
• Trip + 3 Hrs
• Standard Hourly Rate Reg

The subform has the following fields:

• ID (key)
• Store #
• Request Type
• Vendor #
• Rank
• Old Rank
• Existing Rank
• Action
• Trip + 3 Hrs
• Standard Hourly Rate Reg

Here is what the query is doing:

• It is filtering on Store # in the subforms active record "criteria is [Forms]![Dispatch Analysis]![Dispatch Analysis subform].[Form]![Store #]"
• It is filtering on Request Type in the subforms active record "criteria is [Forms]![Dispatch Analysis]![Dispatch Analysis subform].[Form]![Request Type]"
• It then assigns a sequential number called Auto Rank based on the ID "formula is DCount("ID","Dispatch Analysis Auto Rank","ID <= " & [ID])"
• Trip + 3 Hrs criteria is >0
• Standard Hourly Rate Reg criteria is >0

By performing a DLookup on this query I'm able to assign a unique number (Auto Rank) to each vendor for each Store #/Request Type and assign an action based on the If statements if there is a trip +3 Hrs and a Standard Hourly Rate Reg. The Auto Rank by Store #/Request Type is the most important part of this.

Here is the subform sub I run by double clicking on the Rank field I want to start adding the Auto Rank from the query to the Rank in the subform and update the Action.

Private Sub Rank_DblClick(Cancel As Integer)
If MsgBoxYesNo(RunAutoRankYesNo) Then
While Me.CurrentRecord < Me.Recordset.RecordCount
If Me.Trip___3_Hrs > 1 And Me.Standard_Hourly_Rate_Reg > 1 Then

Me![Rank] = DLookup("[Auto Rank]", "Dispatch Analysis Auto Rank", "[ID]=Forms![Dispatch Analysis]![Dispatch Analysis subform]![ID]")

If IsNull(Me.Rank) Then
Me.Action = Null
ElseIf Me.Rank > 0 And Me.Existing_Rank = "Yes" And Me.Rank <> Me.Old_Rank Then
Me.Action = "Change"
ElseIf Me.Rank > 0 And Me.Existing_Rank = "Yes" And Me.Rank = Me.Old_Rank Then
Me.Action = Null
ElseIf Me.Rank > 0 And Me.Existing_Rank = "No" Then
Me.Action = "Add"
End If
Else
Me.Rank = Null
Me.Action = Null
End If
DoCmd.GoToRecord Record:=acNext
Wend
MsgBoxOKOnly AutoRankComplete
End If
End Sub

Can anyone help me with this? Is there alternative like a RecordSetClone? If so, how would I write this?

Thank you in advance!

Daniel

[June7]

I don't know how that code worked. Variables should not be within quote marks. Reference to a control on form is a variable. Concatenate variables.

Which form is this code behind?

Me![Rank] = DLookup("[Auto Rank]", "Dispatch Analysis Auto Rank", "[ID]=" & Forms![Dispatch Analysis]![Dispatch Analysis subform]![ID])

[dharrington]

The code is behind the subform called Dispatch Analysis subform. The main form is called Dispatch Analysis.

I changed it to the following as you suggested . It works but it still stops. This time it was after updating 933 records.

Me![Rank] = DLookup("[Auto Rank]", "Dispatch Analysis Auto Rank", "[ID] = " & Forms![Dispatch Analysis]![Dispatch Analysis subform]![ID])

Is there a quicker and more efficient way to get the DCount without using a DLookup for each record to get it? I currently have 27,900 records I need to update.

In case it is helpful here is the SQL code for the query the DLookup opens for each record. It is pulling information from one table.

SELECT [Dispatch Analysis].ID, [Dispatch Analysis].[Store #], [Dispatch Analysis].[Request Type], [Dispatch Analysis].[Vendor #], DCount("ID","Dispatch Analysis Auto Rank","ID <= " & [ID]) AS [Auto Rank], [Dispatch Analysis].[Trip + 3 Hrs], [Dispatch Analysis].[Standard Hourly Rate Reg]
FROM [Dispatch Analysis]
WHERE ((([Dispatch Analysis].[Store #])=[Forms]![Dispatch Analysis]![Dispatch Analysis subform].[Form]![Store #]) AND (([Dispatch Analysis].[Request Type])=[Forms]![Dispatch Analysis]![Dispatch Analysis subform].[Form]![Request Type]) AND (([Dispatch Analysis].[Trip + 3 Hrs])>0) AND (([Dispatch Analysis].[Standard Hourly Rate Reg])>0));

[June7]

The query name is [Dispatch Analysis Auto Rank]? If so, it has DCount that references itself? Looks like circular reference to me. I just tried that and the query errors. I don't know how anything gets updated.

The code is behind subform? [Rank] is a field on that subform? [ID] is a field on that subform? Don't need the form reference prefix, although should still work. Use Me! for both fields.

BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be TripPlus3 and StoreNum.

[dharrington]

Yes, the query is called [Dispatch Analysis Auto Rank]. It is pulling data from a table called [Dispatch Analysis]. I understand what you are saying about the DCount but it does work. However, I'm not sure I follow your other comments about the code behind the subform. Can you provide me more detail about what you are suggesting? I have [Rank] and [ID] listed in more than one place and only the [ID] field has the form reference prefix which is in the DLookup.

I am intentionally counting the query results because it assign a unique auto rank to the rank of a vendor for each [store #]/[request type] which the query filters on based on the active record in the subform. Once it gets to a new [store #]/[request type] it start the process all over again until it reaches the end of the form. This is what the subform looks like when it is done auto filling the ranks (pipe symbol is the delimiter). What do you suggest I do instead of this to achieve the same results?

ID | Store # | Request Type | Vendor # | Rank
1 | Store 1 | Request Type 1 | Vendor 1 | 1
2 | Store 1 | Request Type 1 | Vendor 3 | 2
3 | Store 1 | Request Type 1 | Vendor 2 | 3
4 | Store 1 | Request Type 2 | Vendor 8 | 1
5 | Store 1 | Request Type 2 | Vendor 3 | 2
6 | Store 2 | Request Type 4 | Vendor 6 | 1

My main problem is the screen freezes and in some cases I get a message stating "system resource exceeded" which didn't happen in Access 2007. Is there an alternative to accomplish updating a subform field by counting records that are in a table, in ascending order by ID, that is filtered based on criteria in the subform active record, in this case [Store #] and [Request Type]? I'd like to get the same result with code that will utilize less resources, work faster, and not cause the database screen to freeze if possible.

Thank you!

[June7]

What is purpose of this rank value, what is it used for?

Circular reference is a common error with expressions in textbox. Say a textbox is named Price - a circular reference error will trigger from expression in that textbox if it references Price:

=Nz([Price],"None")

I don't see how an expression in a query can reference that query, which is what I think I see in your query.

Ranking in a query by group is fairly common topic. Start with:

https://www.accessforums.net/queries...ery-41748.html

[dharrington]

I figured it out! In case others need to do something similar you can visit URL http://stackoverflow.com/questions/2...t-subset-of-ta.

I'm able to do this by running a query using the following SQL.

SELECT (SELECT COUNT(T1.ID)
FROM
[Dispatch Analysis Auto Rank] AS T1
WHERE T1.ID <= T2.ID and t1.[Store #] = t2.[Store #]
and t1.[Request Type]= t2.[Request Type]) AS Sequence, * INTO OutputResultsTableName
FROM [Dispatch Analysis Auto Rank] AS T2
ORDER BY T2.ID;

I then wrote code I run using a button on my main form that runs an update query adding the Sequence to the tables [Rank] field for each [ID], then it refreshes the subform, then it runs the loop code to update the Action field. By taking the DLookup out of the picture it runs fast and doesn't freeze up.