Setting .DefaultValue causes Restart

  • Thread starter Thread starter Larry
  • Start date Start date
L

Larry

I have a subform, of locations for the user defined on the main form. Each
user can have multiple locations. The subform is a single record form and
has a field for "Order", to describe which order the locations will print on
any reports.

The problem I am having is when I want to set the default value for the
Order textbox. I want Order to be 1 for a new record, or 1 greater than the
highest Order for the current user.

I put the following code in the OnCurrent even of the subform:

If Me.NewRecord Then
Me.txtOrder.DefaultValue = """" &
Cstr(NZ(DMax("Order","tblPersonnelLocation","PersonnelID = " &
Me.Parent.PersonnelID),0) + 1) & """"
End if

When I have this code in place and open the form, MS Access crashes with a
msg about needing to repair/restart my database. If I put a break on the IF
part of the statement and step through it, IT WORKS! But, somehow, I don't
think I want my users having to do that.

I also tried this without the IF statement, just doing it for every record,
and that didn't work. I also tried putting the value in a string variable
first, then assigning that to the DefaultValue property, and it didn't help.
I have also tried putting this formula on the property line itself, with no
success.

Anyone have an idea what might be causing this problem?

BTW, Access 2002 SP 3 on Windows XP.


TIA
Larry
 
Larry,

I am not sure of the reason for the problem you are having. But nor am
I sure why you are trying to do this on the Current event of the form.
Why not just enter your expression directly into the Default Value
property of the Order control on the form that you use as the subform, i.e.
Nz(DMax("[Order]","tblPersonnelLocation","[PersonnelID]=" &
[PersonnelID]),0)+1
 
I have tried putting it on the property line, the last thing I mentioned in
my original msg, with no luck. While it did not cause a crash, it did not
work properly either.


Steve Schapel said:
Larry,

I am not sure of the reason for the problem you are having. But nor am
I sure why you are trying to do this on the Current event of the form.
Why not just enter your expression directly into the Default Value
property of the Order control on the form that you use as the subform, i.e.
Nz(DMax("[Order]","tblPersonnelLocation","[PersonnelID]=" &
[PersonnelID]),0)+1

--
Steve Schapel, Microsoft Access MVP

I have a subform, of locations for the user defined on the main form. Each
user can have multiple locations. The subform is a single record form and
has a field for "Order", to describe which order the locations will print on
any reports.

The problem I am having is when I want to set the default value for the
Order textbox. I want Order to be 1 for a new record, or 1 greater than the
highest Order for the current user.

I put the following code in the OnCurrent even of the subform:

If Me.NewRecord Then
Me.txtOrder.DefaultValue = """" &
Cstr(NZ(DMax("Order","tblPersonnelLocation","PersonnelID = " &
Me.Parent.PersonnelID),0) + 1) & """"
End if

When I have this code in place and open the form, MS Access crashes with a
msg about needing to repair/restart my database. If I put a break on the IF
part of the statement and step through it, IT WORKS! But, somehow, I don't
think I want my users having to do that.

I also tried this without the IF statement, just doing it for every record,
and that didn't work. I also tried putting the value in a string variable
first, then assigning that to the DefaultValue property, and it didn't help.
I have also tried putting this formula on the property line itself, with no
success.

Anyone have an idea what might be causing this problem?

BTW, Access 2002 SP 3 on Windows XP.


TIA
Larry
Click to expand...
Click to expand...
 
Larry,

This is standard procedure, and so if it "does not work properly", there
must be a reason. What does "does not work properly" mean? And what is
the exact expression you used in the Default Value property setting?
 
In the Default property for this textbox, I put the following:
=Nz(DMax("Order", "tblPersonnelLocation", "PersonnelID = " &
Me.Parent.PersonnelID), 0) + 1

BUT, Access changes it to
Nz(DMax("Order","tblPersonnelLocation","PersonnelID = " &
[Me].[Parent].[PersonnelID]),0)+1

This change does not work (even in immediate mode) while the syntax I
used does work (in immediate mode and in code anyway). The way Access
changes it, gives me a ?#NAme error

I have to get PersonnelID from the parent form, because when a new
location record is being created for the person on this subform, there
is no PersonnelID on the new record, so I can't refer to it.

So I change the referenc to refer to the name of the parent form and
that seems to work. BUT when I delete a location, I now get an error
that says "can't find the field '|' referred to in your expression".
argh!

It goes ahead and deletes the record, but then increments the counter
on the next "blank" record. So frustrating!
 
Larry,

You are using incorrect syntax. Please refer to my earlier post for the
correct syntax of the Default Value property setting. Please try this,
and post back if it doesn't give the desired results.
 
No, my sytax is correct. PersonnelID does not exist on a new record in
the subform, until after the record is created. That's why I have to
refer to the PersonnelID on the parent form.

If I do it the way you suggest, I get an #Error displayed in the Order
textbox.

Steve Schapel said:
Larry,

You are using incorrect syntax. Please refer to my earlier post for the
correct syntax of the Default Value property setting. Please try this,
and post back if it doesn't give the desired results.

--
Steve Schapel, Microsoft Access MVP


Larry said:
In the Default property for this textbox, I put the following:
=Nz(DMax("Order", "tblPersonnelLocation", "PersonnelID = " &
Me.Parent.PersonnelID), 0) + 1

BUT, Access changes it to
Nz(DMax("Order","tblPersonnelLocation","PersonnelID = " &
[Me].[Parent].[PersonnelID]),0)+1

This change does not work (even in immediate mode) while the syntax I
used does work (in immediate mode and in code anyway). The way Access
changes it, gives me a ?#NAme error

I have to get PersonnelID from the parent form, because when a new
location record is being created for the person on this subform, there
is no PersonnelID on the new record, so I can't refer to it.

So I change the referenc to refer to the name of the parent form and
that seems to work. BUT when I delete a location, I now get an error
that says "can't find the field '|' referred to in your expression".
argh!

It goes ahead and deletes the record, but then increments the counter
on the next "blank" record. So frustrating!
Click to expand...
Click to expand...
 
Larry,

Aha! I see what you have done, I think. On your subform, the
PersonnelID control is not named PersonnelID... am I right? I always
name bound controls the same as the field they are bound to, as per
Access default behaviour, in which case my suggested expression will
work. So, fair enough, you can refer to the PersonnelID on the parent
form. In this case, your syntax is not correct. Try it like this...

Nz(DMax("[Order]","tblPersonnelLocation","[PersonnelID]=[Parent]![PersonnelID]"),0)+1
 
That did it Steve! Thanks. I always hated those exclaimation points!

Steve Schapel said:
Larry,

Aha! I see what you have done, I think. On your subform, the
PersonnelID control is not named PersonnelID... am I right? I always
name bound controls the same as the field they are bound to, as per
Access default behaviour, in which case my suggested expression will
work. So, fair enough, you can refer to the PersonnelID on the parent
form. In this case, your syntax is not correct. Try it like this...

Nz(DMax("[Order]","tblPersonnelLocation","[PersonnelID]=[Parent]![PersonnelID]"),0)+1


--
Steve Schapel, Microsoft Access MVP


Larry said:
No, my sytax is correct. PersonnelID does not exist on a new record in
the subform, until after the record is created. That's why I have to
refer to the PersonnelID on the parent form.

If I do it the way you suggest, I get an #Error displayed in the Order
textbox.
Click to expand...
Click to expand...
 
Larry,

It is the ! but more importantly the Me keyword which is recognised by
VBA but not by Jet.
 
Back
Top