Assign a value to a field from the Format event

I am listing a file whose records contain the fields "Item Number" and
"Description". When the program encounters a record that has a blank Item
Number or a blank Description, I want to replace the offending field with the
work "Blank". Here is the code that I have in my Detail section Format event:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Nz(Me![Item Number], "") = "" Or isBlank(Me![Item Number]) Then
Me![Item Number] = "Blank"
End If
If Nz(Me![Description], "") = "" Or isBlank(Me![Description]) Then
Me![Description] = "Blank"
End If
Cancel = False
End Sub

When I encounter a blank field, I get an error on the assignment of the
contstant to the offending field ([Item Number] or [Description]. I also get
the message "You can't assign a value to this object". What am I doing wrong?

Thanks

Rather than assign the field as the control source for the control, use an
expression like this:

=IIf(Len(Nz([Item Number],"")) = 0, "Blank", [Item Number])
--
Dave Hargis, Microsoft Access MVP


"Goldar" wrote:

I am listing a file whose records contain the fields "Item Number" and
"Description". When the program encounters a record that has a blank Item
Number or a blank Description, I want to replace the offending field with the
work "Blank". Here is the code that I have in my Detail section Format event:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Nz(Me![Item Number], "") = "" Or isBlank(Me![Item Number]) Then
Me![Item Number] = "Blank"
End If
If Nz(Me![Description], "") = "" Or isBlank(Me![Description]) Then
Me![Description] = "Blank"
End If
Cancel = False
End Sub

When I encounter a blank field, I get an error on the assignment of the
contstant to the offending field ([Item Number] or [Description]. I also get
the message "You can't assign a value to this object". What am I doing wrong?

Thanks

Goldar wrote:


I am listing a file whose records contain the fields "Item Number" and
"Description". When the program encounters a record that has a blank Item
Number or a blank Description, I want to replace the offending field with the
work "Blank". Here is the code that I have in my Detail section Format event:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Nz(Me![Item Number], "") = "" Or isBlank(Me![Item Number]) Then
Me![Item Number] = "Blank"
End If
If Nz(Me![Description], "") = "" Or isBlank(Me![Description]) Then
Me![Description] = "Blank"
End If
Cancel = False
End Sub

When I encounter a blank field, I get an error on the assignment of the
contstant to the offending field ([Item Number] or [Description]. I also get
the message "You can't assign a value to this object". What am I doing wrong?


You can not assign a value to a bound text box.

Instead of using code, you can use an expression in the text
box:
=IIf(Nz(Trim([Item Number]), "") = "", "Blank", [Item
Number])

--
Marsh
MVP [MS Access]

Thanks to you both for the suggestion. I didn't know that you can't assign a
value to
a bound text box. Both your suggestions worked fine.
"Marshall Barton" wrote:

Goldar wrote:


I am listing a file whose records contain the fields "Item Number" and
"Description". When the program encounters a record that has a blank Item
Number or a blank Description, I want to replace the offending field with the
work "Blank". Here is the code that I have in my Detail section Format event:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Nz(Me![Item Number], "") = "" Or isBlank(Me![Item Number]) Then
Me![Item Number] = "Blank"
End If
If Nz(Me![Description], "") = "" Or isBlank(Me![Description]) Then
Me![Description] = "Blank"
End If
Cancel = False
End Sub

When I encounter a blank field, I get an error on the assignment of the
contstant to the offending field ([Item Number] or [Description]. I also get
the message "You can't assign a value to this object". What am I doing wrong?


You can not assign a value to a bound text box.

Instead of using code, you can use an expression in the text
box:
=IIf(Nz(Trim([Item Number]), "") = "", "Blank", [Item
Number])

--
Marsh
MVP [MS Access]