Macro If statment criteria referencing table field.

[Chip]

I am trying to use the following criteria in an if statement in a macro: [TempVars]![VarID]=DMin("ID","tblInventoryItemTransactionsSorted","[tblInventoryItemTransactionsSorted].[Name]='" & [tblInventoryItemTransactionsSorted].[Name] & "'") where VarID is a temporary variable that is set to the ID of the current record, and DMin calculates the smallest ID number for any set of records with the same Name in the record set. The value that DMin will return is actually in a field in the table but I am unable to use in the If statement so I use the domain function to calculate it again. The problem is that I keep getting the following error message "Microsoft Access cannot find the name 'tblInventoryItemTransactionsSorted' you entered in the expression.". The DMin function as written above works fine in a query. Any suggestions would be appreciated.

[June7]

In macro or VBA code, the parameter must come from form (or report), not table.

[TempVars]![VarID]=DMin("ID","tblInventoryItemTransactionsSorted","[Name]='" & [Name] & "'")


Name is a reserved word. Should not use reserved words as names.

[Chip]

June7: Thanks for your prompt reply.

So what do I need to do? Create a form (or report) based on the table to reference in the If statement? That sounds very cumbersome. Are there other options?

[June7]

Why do you NOT have a form? How does the macro have a 'current record' for the macro to know a [Name]? You have a table open? Cannot refer directly to table and query objects to pull dynamic parameter for domain aggregate filter argument.

Users should not interact directly with tables and queries, only forms and reports.

I don't use macros, only VBA but the questions would be the same.

[Chip]

The process I am working on is building a table for an inventory valuation report. I gather the raw transactional data into a table I create and sort the transactions into another table so that I can use dsum to calculate running totals. I then need to add some other calculated data into fields in the table that differ based upon criteria. So I set a temporary variable to the value of the first record ID, do some testing, and then run update queries based upon the outcome of the testing. I then increment the temporary variable and repeat the process on the next record, and so on, until EOF. Once the table data is complete, it will be used as a record set for reports and forms. So far it all works fine except that I cannot get test if the current record ID is equal to the MinID for the item number in question. If I could reference the table field directly in the If statement, this would be simple, but alas... If you can suggest a better way of doing this, I would appreciate your suggestion.

Thanks,

[June7]

Textbox on report has RunningSum property. Might explore that option.

Don't really understand why you 'sort the transactions into another table'. This is what queries are for - sorting and filtering records. However, I do have situations where I write data to a 'temp' table to do some extraordinary manipulation of data for desired output. I just don't understand why you need this.

Not sure what you mean by "The value that DMin will return is actually in a field in the table" - you mean the ID field is in table and that's the field you want to pull minimum ID from?

Domain aggregates can perform poorly in queries and textboxes. You can build a query object that groups records by [Name] and returns the minimum ID.
SELECT [Name], Min([ID]) AS MinID FROM tblInventoryItemTransactionsSorted GROUP BY [Name];
Then join that query to the raw dataset to get the same effect as the DLookup in query.
However, that doesn't resolve how to set the TempVars variable.

Might need a VBA custom function.

I would have to analyse your code.

[Chip]

Ok. One question at a time.
I sort transactions into another table so that I can create a running total using dsum. Dsum will only create a running total and reset the total when the item number changes if it is looking at a table that is sorted.

The Value that Din will return is actually a field in the table. The first thing the process does is identify the first record of a subset of records that have the same Name. This is done by comparing the ID of the current record with the minimum ID for any of the subset. In queries, this is done by calculating DMin of the ID and comparing it with ID. When I first tried to do this within a macro If statement, it did not work, so I added a MinID field to my table and a DMin(ID) to one of my update queries and added the minimum ID value to each record in my table. I thought I could use this field in my Macro IF statement. So my table a field with the minimum ID value for every item number.

Let me approach this another way. I have some update queries that do various calculations under various conditions. The conditions have to do with values of running totals and whether the record is the first record in a set of records with the same item number, and whether there are initial values or not. All of the raw data that is used to test these conditions is contained in fields in my table. How can I test these conditions and decide which update query to run in the simplest way possible?

I seems odd that a Macro IF statement is unable to reference data in a table, but since it seems to be true, there must be a way to do this.

[June7]

Neither macro nor VBA can reference a table or query object directly, which is what your filter parameter in the DLookup is attempting. Code behind a form or report can pull parameter from field of current record on the form or report.

A function called from query can pass field value from current record as an argument parameter and return some value based on that input. This can be an intrinsic function such as DLookup or a VBA custom function.

Those are the options.