Format Form like Report

[plog]

When dealing with the criteria portion of a domain function (Dcount, Dlookup, etc.), you have to be cognizant of which parts are strings and which parts are variables. The criteria is essentially one big string which you can put data fields into.

Your criteria never started the string back up once it used the [TOrderNum] field. You need more quote mark after it:

"[TOrderNum] <" & [TOrderNum] & " And [PartsFID]=" & [PartsFID]

If you have any other questions could you post a new structure screenshot?

 

[mango97]

I was able to get the DCount functions to work properly Ill post the code here just in case you want to see it...

Before: DCount("[PartsFID] + [TOrderNum]","TaskOrder","[PartsFID]=" & [PartsFID] & "And [TOrderNum] <" & [TOrderNum])

CompBefore: DCount("[PartsFID] + [TOrderNum] + [CompDate]","TaskOrder","[PartsFID]=" & [PartsFID] & "AND [TOrderNum] <" & [TOrderNum] & "AND [CompDate] IS NOT NULL")

When I make the forms/reports I will add conditional formatting to determine in the two values are equal. I wasn't aware of how simple/robust the domain functions are, so I'm also replacing most of my macros with them.

I used DLookup to replace the macro for the previous/next fields:

Previous: DLookUp("[Shop]","TaskOrder","[PartsFID]=" & [PartsFID] & "AND [TOrderNum] =" & [TOrderNum]-1)

Next: DLookUp("[Shop]","TaskOrder","[PartsFID]=" & [PartsFID] & "AND [TOrderNum] =" & [TOrderNum]+1)

These work as expected, however since the ID from ShopProcesses is stored in the Shop field I am shown the numerical value of the Shop instead of the actual shop name. How can I have it show the shop name instead of the shop ID? (In the [Shop] field in the TaskOrder table I used the lookup wizard to show the value. I suspect this isn't an optimal approach!)

Also, Here is an updated structure screenshot. In the TaskOrder table the Previous/Next and Task# fields can all be calculated, therefore once I've replaced them with calculated fields in the query I will delete them.

Larger Image



Thanks a bunch!
mango97

 

[plog]

You can nest that in a Dlookup or you can build a query on top of it and use it to link to the ShopProcesses table.

For simplicity's sake, you have this code which returns a Shop ID value:

DLookUp("[Shop]","TaskOrder","[PartsFID]=" & [PartsFID] & "AND [TOrderNum] =" & [TOrderNum]+1)

Let's call all that code X. So to get the ShopProcessesValue we do another Dlookup based on X:

DLookup("[ShopProcesses]", "ShopProcesses", "[ShopID]=" & X)

Now replace X with that code.

Honestly though, it might be best just to get the SHopID value, then build another query on top of that and link ShopProcesses to that query which gets Next, Previous.

 

[mango97]

You can nest that in a Dlookup or you can build a query on top of it and use it to link to the ShopProcesses table.

For simplicity's sake, you have this code which returns a Shop ID value:

DLookUp("[Shop]","TaskOrder","[PartsFID]=" & [PartsFID] & "AND [TOrderNum] =" & [TOrderNum]+1)

Let's call all that code X. So to get the ShopProcessesValue we do another Dlookup based on X:

DLookup("[ShopProcesses]", "ShopProcesses", "[ShopID]=" & X)

Now replace X with that code.

Honestly though, it might be best just to get the SHopID value, then build another query on top of that and link ShopProcesses to that query which gets Next, Previous.

I don't quite understand what you mean by "build another query on top of that and link ShopProcesses to that query", However I did try the code (and got it to work!). The only problem with the code is that for the first and last tasks in a part there is no "Previous" or "Next" field and results in an error. Is there a way to fix this?

 

[plog]

You build a query that pulls in all data from TaskOrder, along with your 2 calculated fields (Next, Previous). Save that query as 'subTasks'. Then you build a new query brining in subTasks and ShopProcesses twice. Link one instance of ShopProcesses to Next and the other to Previous. Bring down all the fields from subTasks and then bring in ShopProcesses field from both ShopProcesses instances. Your query now has all the data you need included the NExt and Previous ShopProcesses fields.

To fix your nested Dlookups (where you get the ShopID and then use it to lookup the name), you have to wrap all of that inside an Iif statement and see if the first Dlookup returns any values, if not, make it be blank, otherwise use the finished code you have with nested Dlookups. Using the query method I detailed in the first paragraph will eliminate this error without much coding.