if one field is null then ....???

[luanhoxung]

hi all !
I try using IsNull(mycontrol) or Nz(mycotrol) for filling it but not
succeeded.

my code:
Private Sub Form_Current()
If IsNull(Me.INV) Then
Me.INV = "some text here"
End If
End Sub

i always get #Error# in my control on form view.
Do i state the code in wrong location ? or incorrect code ?

Thanks any suggestion.
Luan from VietNam

 

[Allen Browne]

Luan, what is your purpose here?

The form's Current event fires every time you move to a record. There seems
little point in changing the value of a text box just because you viewed a
record.

If you are trying to update all records where INV is null to some other
value, use an Update query. Then set the Default Value of the text box so it
gets the value by default for new records too.

If you are just trying to avoid Null values in the field, simply open your
tablle in design view, and set the Required property of the field to Yes.

If INV is unbound, change its Control Source property to:
="some text here"
so it shows that for all records. There is no need to change it every time
you visit a record.

The error could be the result of several things, such as:
- A control that has the same name as a field, but is bound to something
else.
- An invalid expression in the Control Source.
- An invalid Default Value.

 

[luanhoxung]

Thanks ur quick reponse, Allen.
Sorry for my unclear question and my bad English.

Actually, Me.INV is my Invoice Number (primary key of my table) which
is nearly autonumber.
My goal is:
1/ checking Inv No. if null (mean 1st value) then set it to "0001" & 2
characters of current month and 2 character of current year.
2/ if it is not null (mean not 1st value) then goto last record :
val(left(Inv,4))+1 & ....
But it nearly complexly to me, because i dont know much more VB.
I am trying to learn VB step by step.

Any solutions or sample code nearly my goal ?
Thanks ur time.

 

[Allen Browne]

Okay, you want to assign a value to the INV field when you start to create a
new record? (Existing records will already have a value.)

Use the BeforeInsert event of the form to assign the value. This event fires
as soon as you start typing in the new record.

You can get the 2 characters for the month and 2 characters from the year
as:
Format(Date, "mmyy")
To find the highest number already assigned so far in your table, use
DMax().

It is important to know if INV is a Number field or a Text field,
particularly with the leading zeros.

Would it be easier just to use an AutoNumber field as the primary key? You
could still record the date when the record was created, just by adding a
Date/Time field to your table, and setting its Default Value to:
=Date()

 

[luanhoxung]

Thanks again, Allen.
My Invoice number is a text. Because Inv no. (EX: 0010-1206) show the
10th invoice of December, 2006, so i cannot use autonumber here. i
remark my Invoice in new year.
I will think more about ur advise tonight.
Thanks ur time,