How to set DefaultValue property to custom function? 1

[MikeBronner]

Hi everyone:
I've been trying the seemingly impossible: I want to set my default value of a field to a custom function defined in a module. Is this possible? What do I need to do to make it work?
I have the following in Module 'Functions':

Public Function GetNextID()
    GetNextID = Nz(DMax("[CSI_ID]", "TEST40_COST_STRUCTURE_INDEX"), 0) + 1
End Function

However, when I set the default value property to '=GetNextID()' it responds that it cannot find this function.

Thanks for all you efforts .

 

[dhookom]

This should be possible. I assume your module name is Functions. What happens if you open the debug window (Press Ctrl+G) and then enter:
?GetNextID()
You also need to consider that if your form is sitting open while another person enters a new CSI_ID, then your value will default to the same as the new record.

Otherwise, this looks like it should work.

Duane
MS Access MVP

 

[WildHare]

Mike, are you doing this on a FORM, or in the table design view? As Duane noted, this should be very doable from a form point of view, but might not be in raw table design...it sure would be nice if you could, though...

Jim

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[MikeBronner]

Thanks dhookum, wildhare:

wildhare, you're right: this is in the raw table view, not form view. I wanted to avoid having to use forms, as this is for backend purposes only. There will be no supporting frontend to this database, since it is a conversion project from one system to another. But that's a whole other story.

So I gather that you both agree that it can't be done in the table view, and that I'll be forced to use a form?

Thanks for your responses

Mike

 

[WildHare]

AFAIK, the ONLY thing you can put in the DEFAULT VALUE property for a field at the TABLE level is a discrete value, e.g. 'ZOT' or 'Norweigian Blue' - you can not use a function, a reference to another field in the record, a logical equivalence, an if..then test, a trigger, or anything else that might be remotely more useful than a plain old discrete value.

Sorry. I don't think MS ever intended JET to be a real database engine, hence its' limitations.

Jim

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[MikeBronner]

Thanks for your help Jim . Eventhough it wasn't the answer I was hoping for, I now know that I need to look for a different solution.

Take Care,
Mike

 

[OldTimeProgrammer]

In Access2000 (and later, I guess), you can use built-in functions as default values, including "IIF" and "Date()". It doesn't seem to be well-documented, but go to the "Default Value" property of a field and get the dropdown to see most of what's allowable. ("IIF" isn't in there, but it works; makes me wonder what else is missing.)

Unfortunately, I have not been able to get my own functions to work, so the answer to the original question is still "Can't be done."

 

[dhookom]

There are specific built-in functions that will work. They can't be user-defined functions and they can't reference other fields or tables. For instance, DLookup("EmpID","tblEmployees","DeptID=" & DeptID) will not work in a table design. This will work as the default of a control on a form.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.