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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|