Default Value

  • Thread starter Thread starter Sue Compelling
  • Start date Start date
S

Sue Compelling

Hi All -

My attempt at creating a default value in a form control is coming back as
"invalid use of Null" and I can't figure out what I'm doing wrong.

Private Sub LastName_AfterUpdate()
If IsNull(Me.LastName) Then
Me.Salutation.DefaultValue = ([Title] + " ") & ([FirstName] & " ") +
[LastName]
Else
Me.Salutation.DefaultValue = [OrgTitle]
End If
End Sub

TIA
 
Here are the ways to do default values for text, numbers and dates-->

Private Sub controlname_AfterUpdate()
'text field
Me.TheTextControl.DefaultValue = """" & Me. TheTextControl & """"

'date field
Me.TheDateControl.DefaultValue = "#" & Me.TheDateControl & "#"

'number field
Me.TheNumberField.DefaultValue = Me.TheNumberField
End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
Hi Jeanette -

Are you saying I can't have IIF statements in a default value query? - ie I
can't have options?

--
Sue Compelling


Jeanette Cunningham said:
Here are the ways to do default values for text, numbers and dates-->

Private Sub controlname_AfterUpdate()
'text field
Me.TheTextControl.DefaultValue = """" & Me. TheTextControl & """"

'date field
Me.TheDateControl.DefaultValue = "#" & Me.TheDateControl & "#"

'number field
Me.TheNumberField.DefaultValue = Me.TheNumberField
End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Sue Compelling said:
Hi All -

My attempt at creating a default value in a form control is coming back as
"invalid use of Null" and I can't figure out what I'm doing wrong.

Private Sub LastName_AfterUpdate()
If IsNull(Me.LastName) Then
Me.Salutation.DefaultValue = ([Title] + " ") & ([FirstName] & " ") +
[LastName]
Else
Me.Salutation.DefaultValue = [OrgTitle]
End If
End Sub

TIA
Click to expand...


.
Click to expand...
 
Sue said:
Are you saying I can't have IIF statements in a default value query? - ie I
can't have options?
Click to expand...


The DefaultValue property is a **string** that will be
interpreted as an expression at the time the first character
is entered in a ***new*** record. That means that you can
use an expression because the DefautValue property is always
an expression. A critical point here is that you use one
expression in VBA to set the property string to another
expression that will be evaluated when the property is
assigned to the control's Value.

That really sounds confusing, but if you'll take some time
and think about what Jeanette posted, you'll begin to see
the light.

The error message you received means that you tried to set
the DefaultValue property to Null, but you must set it to
either a string or something that Access can convert to a
string.

There may, or may not, be a problem with your first one (I
think there may be a problem with your parenthesis), but, if
OrgTitle is a text string or Null, then there will
definitely be a problem. Your line of code:
Me.Salutation.DefaultValue = [OrgTitle]
may result in the DefaultValue property containing something
like:
This is a title
which is clearly NOT an expression that can be evaluated.
To make that work, you would need to use the kind of thing
Jeanette said:
Me.Salutation.DefaultValue = """" & [OrgTitle] & """"
The result of that will set the DefaultValue to:
"This is a title"
which is a text string that is easily understood by Access.
 
Hi Marshall

I think I've got my terminology wrong as it's possibly not a "defaul value"
I'm after.

What I would like to do is have the Addressee field auto-populate after
certain fields have been entered (or tabbed through) - ie [lastname] and
[orgtitle]

This will save the user having to re-enter data already gathered - though
they will still need to be able to EDIT the field too.

The following expression in my query gives me the right results - I just
don't know how to make it drop into the Addressee field

AddDef: IIf([lastname] Is Not Null,(([Title]+" ") & ([firstname] & "
")+[lastname]),[OrgTitle])

ps - when I followed your prev advise to make the string - it sort of worked
- but the default result of "Ms Sue Kapeli" appeared on the NEXT record not
the record I was entering the data into.

Sorry - I don't know enough to untangle this -

--
Sue Compelling


Marshall Barton said:
Sue said:
Are you saying I can't have IIF statements in a default value query? - ie I
can't have options?
Click to expand...


The DefaultValue property is a **string** that will be
interpreted as an expression at the time the first character
is entered in a ***new*** record. That means that you can
use an expression because the DefautValue property is always
an expression. A critical point here is that you use one
expression in VBA to set the property string to another
expression that will be evaluated when the property is
assigned to the control's Value.

That really sounds confusing, but if you'll take some time
and think about what Jeanette posted, you'll begin to see
the light.

The error message you received means that you tried to set
the DefaultValue property to Null, but you must set it to
either a string or something that Access can convert to a
string.

There may, or may not, be a problem with your first one (I
think there may be a problem with your parenthesis), but, if
OrgTitle is a text string or Null, then there will
definitely be a problem. Your line of code:
Me.Salutation.DefaultValue = [OrgTitle]
may result in the DefaultValue property containing something
like:
This is a title
which is clearly NOT an expression that can be evaluated.
To make that work, you would need to use the kind of thing
Jeanette said:
Me.Salutation.DefaultValue = """" & [OrgTitle] & """"
The result of that will set the DefaultValue to:
"This is a title"
which is a text string that is easily understood by Access.
Click to expand...
 
Sue said:
I think I've got my terminology wrong as it's possibly not a "defaul value"
I'm after.

What I would like to do is have the Addressee field auto-populate after
certain fields have been entered (or tabbed through) - ie [lastname] and
[orgtitle]

This will save the user having to re-enter data already gathered - though
they will still need to be able to EDIT the field too.

The following expression in my query gives me the right results - I just
don't know how to make it drop into the Addressee field

AddDef: IIf([lastname] Is Not Null,(([Title]+" ") & ([firstname] & "
")+[lastname]),[OrgTitle])

ps - when I followed your prev advise to make the string - it sort of worked
- but the default result of "Ms Sue Kapeli" appeared on the NEXT record not
the record I was entering the data into.
Click to expand...


The next record is where the DefaultValue is supposed to
appear. That's because the DefaultValue is applied at the
time the first character is entered in a new record. SInce
you are setting the property after the the first character
was entered in the current new record, it will just sit
there until you start to enter something in the next new
record. I guess that cryptic explanation is supposed to
lead you to the conclusion that the DefaultValue property is
not appropriate for what you want to do.

If you want to set a value in the current record, then just
set the text box's Value. You didn't post the code that
almost worked, but I think all you need to do is remove the
..DefaultValue from it. OTOH, since users are allowed to
edit the value you set, you need to make sure you are in a
new record as well as theuser has not already set rhe
value:

Private Sub LastName_AfterUpdate()
If IsNull(Me.Salutation) And Me.NewRecord Then
Me.Salutation = (Me.Title + " ") & . . .
Else
Me.Salutation = Me.OrgTitle
End If
End Sub

I still have my doubts about the part of your expression
after (Me.Title + " ")
 
Thanks so much Marshall - with your help I've got the result I wanted ... in
the end ...

Private Sub LastName_AfterUpdate()

Me.Addressee = (([Title] + " ") & ([FirstName] & " ") + [LastName])
Me.Salutation = [FirstName]

End Sub

Private Sub OrgTitle_AfterUpdate()
Me.Addressee = [OrgTitle]
Me.Salutation = "Friends"

End Sub

--
Sue Compelling


Marshall Barton said:
Sue said:
I think I've got my terminology wrong as it's possibly not a "defaul value"
I'm after.

What I would like to do is have the Addressee field auto-populate after
certain fields have been entered (or tabbed through) - ie [lastname] and
[orgtitle]

This will save the user having to re-enter data already gathered - though
they will still need to be able to EDIT the field too.

The following expression in my query gives me the right results - I just
don't know how to make it drop into the Addressee field

AddDef: IIf([lastname] Is Not Null,(([Title]+" ") & ([firstname] & "
")+[lastname]),[OrgTitle])

ps - when I followed your prev advise to make the string - it sort of worked
- but the default result of "Ms Sue Kapeli" appeared on the NEXT record not
the record I was entering the data into.
Click to expand...


The next record is where the DefaultValue is supposed to
appear. That's because the DefaultValue is applied at the
time the first character is entered in a new record. SInce
you are setting the property after the the first character
was entered in the current new record, it will just sit
there until you start to enter something in the next new
record. I guess that cryptic explanation is supposed to
lead you to the conclusion that the DefaultValue property is
not appropriate for what you want to do.

If you want to set a value in the current record, then just
set the text box's Value. You didn't post the code that
almost worked, but I think all you need to do is remove the
..DefaultValue from it. OTOH, since users are allowed to
edit the value you set, you need to make sure you are in a
new record as well as theuser has not already set rhe
value:

Private Sub LastName_AfterUpdate()
If IsNull(Me.Salutation) And Me.NewRecord Then
Me.Salutation = (Me.Title + " ") & . . .
Else
Me.Salutation = Me.OrgTitle
End If
End Sub

I still have my doubts about the part of your expression
after (Me.Title + " ")
Click to expand...
 
Back
Top