MS Access Data Macro to SET a calculated field value on INSERT using DMAX

Question

I have a two table scenario with a typical parent / child relational setup:

tblGroup - idGroup (autonumber, PK); GroupName (Short Text), Rank (Number)
tblRange - idRange (autonumber, PK), idGroup (FK -> tblGroup.idGroup), RangeName (Short Text), Rank (Number)

What I am doing on the parent (tblGroup) table is using a data macro to add the rank using the BeforeChange event:

IF
  IsInsert
        SetField  - Rank
                  - DMAX(Rank, [tblGroup])+1

This works nicely and I can happily use a parametized INSERT query to add rows to the table and not have to worry about duplicate ranks and so forth.

What I would like to be able to do but cannot figure out how, is to have a data macro do the same thing for the child (tblRange) table, with the rank being set to the new highest for the parent group the child record belongs to.

If I use the same DMAX approach as I have above I am supposed to be able to set a criteria as a third option, acting like a where clause, to limit the lookup / calculation. How can I refer to the specific idGroup I am working with in tblRange in the macro? I cannot seem to figure out how to reference the new records value for this in the macro.

Something like DMAX(Rank, [tblRange], ???How_to_refer_to_idGroup_Properly???)+1

Any help greatly appreciated

Cheers

The Frog

Answer 1

I figured out a way to do this. Thankyou caffeinated beverages!

The reason for the strange error messages is due to limitations in the Data Macro processing, specifically in the BeforeChange event. The solution is as follows:

1. Create a query that selects MAX rank (MaxRank) and GROUP BY for the idGroup (ParentID)
2. The resultant query produces two columns of data: [MaxRank] and [ParentID]
3. There will be a row for every idGroup with the maximum Rank for each
4. Create a BeforeChange data macro
5. Set the following:

IF   IsInsert
        LookupRecord
            Lookup Record In - qryGetMaxRank (or whatever you called your query)
            WHERE - [qryGetMaxRank].[ParentID] = [tblRange].[idGroup]
        Set Field
            Name - [tblRange].[Rank]
            Value - [MaxRank] + 1

The BeforeChange event cannot handle parameters for a query, and I am guessing that this applies in some form the to DMAX function here too. The use of a query that does not use any parameters, and then using the LookupRecord WHERE clause to do the filtering provided the single row result needed. The [MaxRank] value from the returned result is then able to be used to set a new value for the field.

Bit of a workaround but it does allow someone to work with the data either through a form or through the datasheet view and not create a problem.

**In answer to if this is a multi-user DB - it is not. It is just me working with it. If / when the solution is scaled up to something requiring multi-user I will likely recreate the BE in SQL Server or MySQL and use stored procedures for all data I/O. Happy to keep Access as the FE and compile into an application (using the runtime for clients), but I am a fair way off from having to do that yet. Very early stages of development at this time.

Cheers to everyone for the pointers. They helped me figure this out. Hopefully this will be of use to someone else in the future.

PS: If you need to use a parametrized query in a data macro it looks like the best bet is with the AfterInsert event or AfterUpdate event as they can support parameters.