A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old December 22nd, 2006, 05:05 AM posted to microsoft.public.access.forms
[email protected]
external usenet poster
 
Posts: 10
Default if one field is null then ....???

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

  #2  
Old December 22nd, 2006, 05:17 AM posted to microsoft.public.access.forms
Allen Browne
external usenet poster
 
Posts: 11,706
Default if one field is null then ....???

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
ups.com...
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



  #3  
Old December 22nd, 2006, 05:41 AM posted to microsoft.public.access.forms
[email protected]
external usenet poster
 
Posts: 10
Default if one field is null then ....???

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 wrote:
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
ups.com...
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


  #4  
Old December 22nd, 2006, 06:33 AM posted to microsoft.public.access.forms
Allen Browne
external usenet poster
 
Posts: 11,706
Default if one field is null then ....???

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()

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
ups.com...
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 wrote:
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
ups.com...
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



  #5  
Old December 22nd, 2006, 07:56 AM posted to microsoft.public.access.forms
[email protected]
external usenet poster
 
Posts: 10
Default if one field is null then ....???

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,

Allen Browne wrote:
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()

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
ups.com...
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 wrote:
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
ups.com...
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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:24 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.