Using a query for a standard value

[Twanne]

Hi,

I'd like to use a standard value in a field. This value should be the lowest value of a table minus 1. The query I wrote for this works and looks like this:

SELECT Min(dkey) AS dkeylock
FROM VetAbsorptie;
 

Now when I ask for dkeylock with the build function (I select it from the query), access gives me a #NAME? error. Anybody who knows how to solve this??

Greetz

Twanne

I made a whole in my brain to fit a mountain

[Twanne]

I could realy use some help here, the query returns exactly one record so it should work, no? Or is it because I select the field from the query itself?

Greetz

Twanne

Trees are like wood...

[barry07]

You can't use a SELECT query to insert a value into a field. In your case you should use the Dmin function as follows:

dkeylock=DMin("dkey","VetAbsorptie")

[Twanne]

Ok, thanks for all the response :p

Found a solution but it is a bit rude:

When you open the form, you can ad a procedure on load. In this procedure I say what the default value for the field is. I had hoped it wouldn't be necessary to write it in vba because I know it is possible from within acces itself.

Didn't find it but if anyone knows the answer I'd like to know it because it closes down my application. Users might mess with code, but they will have a harder time to figure out where the default value is inputed in access itself (no axtra traces). Plus I noticed when you call the Min() function it sometimes failes and makes my db crash :s

Greetz
Twanne

[watch the language - you can get your account banned doing this]

Ps. I wrote this while the solution was handed :D

[Twanne]

Sorry to break down your solution (it probably works but I don't know exactly how), but it doesn't work here.

Btw. I'm talking about when you right click on a field, then select properties (I think, my access is in dutch), there is a field to specify your default value. DMin("dkey","whatever my table name is") doesn't work there.

Greetz

Twanne

Hit the floor at 300 Mph and bounce back