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
|
|||
|
|||
null but not null
I have a formula that populates my primary key as other fields on my form are
filled in, but when I go to save the new record I am informed that the index or primary key cannot contain a null value. How do I get my form to recognize that the field is populated by the formula? Help PLEASE!! |
#2
|
|||
|
|||
null but not null
If the formula depends on other fields in the form you could set the field to
the reults of the calculation by coding the Afterupdate of the last field needed for the calculation. You probably need to use the forms object names (Me!) in the formula and not the tables fields as the fields have not been updated until the PK Null issue is resolved. Jennifer wrote: I have a formula that populates my primary key as other fields on my form are filled in, but when I go to save the new record I am informed that the index or primary key cannot contain a null value. How do I get my form to recognize that the field is populated by the formula? Help PLEASE!! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200608/1 |
#3
|
|||
|
|||
null but not null
Thank you for the quick reply. I'm kind of learning as I go. I have a
formula that pulls the values into the primary key automatically. =[Drawing #] & "-" & [Sheet #] & [Revision] The only primary key related information in the VB code is below... Private Sub PrimaryKey_Exit(Cancel As Integer) End Sub What do I need to do with this? "jahoobob via AccessMonster.com" wrote: If the formula depends on other fields in the form you could set the field to the reults of the calculation by coding the Afterupdate of the last field needed for the calculation. You probably need to use the forms object names (Me!) in the formula and not the tables fields as the fields have not been updated until the PK Null issue is resolved. Jennifer wrote: I have a formula that populates my primary key as other fields on my form are filled in, but when I go to save the new record I am informed that the index or primary key cannot contain a null value. How do I get my form to recognize that the field is populated by the formula? Help PLEASE!! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200608/1 |
#4
|
|||
|
|||
null but not null
In the AfterUpdate property of the text box code in which you enter the last
bit of data, say Revision, place this code: Private Sub Revision_AfterUpdate() Me!ID = Me![Drawing] & "-" & Me![Sheet #] & Me![Revision #] End Sub Set the Tab Stop property of the ID text box to No and the ID needs to be Text. Hope this helps, Bob Jennifer wrote: Thank you for the quick reply. I'm kind of learning as I go. I have a formula that pulls the values into the primary key automatically. =[Drawing #] & "-" & [Sheet #] & [Revision] The only primary key related information in the VB code is below... Private Sub PrimaryKey_Exit(Cancel As Integer) End Sub What do I need to do with this? If the formula depends on other fields in the form you could set the field to the reults of the calculation by coding the Afterupdate of the last field [quoted text clipped - 6 lines] or primary key cannot contain a null value. How do I get my form to recognize that the field is populated by the formula? Help PLEASE!! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200608/1 |
#5
|
|||
|
|||
null but not null
I use the AutoNumber DataType for a primary key in my tables, and it makes
life very easy for me. I don't know if there are objections to this. And you could still have your combination field made up in a query if you wanted a semparate or familiar reference. It's my understanding that Primary Keys (or any index for that matter) are faster if they are Long Integers instead of Text fields. Anyone want to chime in? "Klatuu" wrote: Try putting the formula in the Before Insert event of text box for your primary key. Me.PrimaryKey =[Drawing #] & "-" & [Sheet #] & [Revision] "Jennifer" wrote: Thank you for the quick reply. I'm kind of learning as I go. I have a formula that pulls the values into the primary key automatically. =[Drawing #] & "-" & [Sheet #] & [Revision] The only primary key related information in the VB code is below... Private Sub PrimaryKey_Exit(Cancel As Integer) End Sub What do I need to do with this? "jahoobob via AccessMonster.com" wrote: If the formula depends on other fields in the form you could set the field to the reults of the calculation by coding the Afterupdate of the last field needed for the calculation. You probably need to use the forms object names (Me!) in the formula and not the tables fields as the fields have not been updated until the PK Null issue is resolved. Jennifer wrote: I have a formula that populates my primary key as other fields on my form are filled in, but when I go to save the new record I am informed that the index or primary key cannot contain a null value. How do I get my form to recognize that the field is populated by the formula? Help PLEASE!! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200608/1 |
#6
|
|||
|
|||
null but not null
Try putting the formula in the Before Insert event of text box for your
primary key. Me.PrimaryKey =[Drawing #] & "-" & [Sheet #] & [Revision] "Jennifer" wrote: Thank you for the quick reply. I'm kind of learning as I go. I have a formula that pulls the values into the primary key automatically. =[Drawing #] & "-" & [Sheet #] & [Revision] The only primary key related information in the VB code is below... Private Sub PrimaryKey_Exit(Cancel As Integer) End Sub What do I need to do with this? "jahoobob via AccessMonster.com" wrote: If the formula depends on other fields in the form you could set the field to the reults of the calculation by coding the Afterupdate of the last field needed for the calculation. You probably need to use the forms object names (Me!) in the formula and not the tables fields as the fields have not been updated until the PK Null issue is resolved. Jennifer wrote: I have a formula that populates my primary key as other fields on my form are filled in, but when I go to save the new record I am informed that the index or primary key cannot contain a null value. How do I get my form to recognize that the field is populated by the formula? Help PLEASE!! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200608/1 |
#7
|
|||
|
|||
null but not null
I agree with you on the use of autonumbers. I have seen others argue that
they prefer to use meaningful Unique data. IMHO, there are a couple of problems there. At some point, you stand a good chance of getting duplicate data. Lets say you are using phone number as a primary key. Seems unique enough, but then (although chances are rare, why take them), Sam moves out of town and cancels his phone service. Then Sally moves into town, starts her phone service and gets Sam's old number and becomes a client/member, whatever of your organization. Opps! Or we are using a Supervisors Last Name to link from the Employee table to a Supervisor table. Fred has 37 people that report to him. Fred gets fired and Wally takes his place. Now we have to change a lot of records. If, instead, we are properly normalized and the [SuperVisor] field is an FK to the supervisor table and we are using autonumbers, All that is necessary is to go to the supervisor table, change the [Name] field and all related queries, forms, etc. now show Wally instead of Fred. This is good (and we didn't really like Fred all that much anyway) "Access101" wrote: I use the AutoNumber DataType for a primary key in my tables, and it makes life very easy for me. I don't know if there are objections to this. And you could still have your combination field made up in a query if you wanted a semparate or familiar reference. It's my understanding that Primary Keys (or any index for that matter) are faster if they are Long Integers instead of Text fields. Anyone want to chime in? "Klatuu" wrote: Try putting the formula in the Before Insert event of text box for your primary key. Me.PrimaryKey =[Drawing #] & "-" & [Sheet #] & [Revision] "Jennifer" wrote: Thank you for the quick reply. I'm kind of learning as I go. I have a formula that pulls the values into the primary key automatically. =[Drawing #] & "-" & [Sheet #] & [Revision] The only primary key related information in the VB code is below... Private Sub PrimaryKey_Exit(Cancel As Integer) End Sub What do I need to do with this? "jahoobob via AccessMonster.com" wrote: If the formula depends on other fields in the form you could set the field to the reults of the calculation by coding the Afterupdate of the last field needed for the calculation. You probably need to use the forms object names (Me!) in the formula and not the tables fields as the fields have not been updated until the PK Null issue is resolved. Jennifer wrote: I have a formula that populates my primary key as other fields on my form are filled in, but when I go to save the new record I am informed that the index or primary key cannot contain a null value. How do I get my form to recognize that the field is populated by the formula? Help PLEASE!! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200608/1 |
#8
|
|||
|
|||
null but not null
I've tried several of the suggestions in the thread, but none seem to correct
the issue of the db reading the PK as NULL. It is generating the primary key exactly as I want it. The tab stop property of the primary key text box is set to no and I have it locked down so that it can't be inadvertently edited, but can be copied to place elsewhere. I really just need for the field to be recognized as populated rather than null. I thought I had accidentally stumbled into correcting this problem 2 days ago, but as soon as I transferred this db out to the share as released and ready for use - it came up broken again. Apparently whatever I thought I did to fix it (which I've already forgotten as I've tried so many things) wasn't a very good fix anyway. Jennifer "jahoobob via AccessMonster.com" wrote: In the AfterUpdate property of the text box code in which you enter the last bit of data, say Revision, place this code: Private Sub Revision_AfterUpdate() Me!ID = Me![Drawing] & "-" & Me![Sheet #] & Me![Revision #] End Sub Set the Tab Stop property of the ID text box to No and the ID needs to be Text. Hope this helps, Bob Jennifer wrote: Thank you for the quick reply. I'm kind of learning as I go. I have a formula that pulls the values into the primary key automatically. =[Drawing #] & "-" & [Sheet #] & [Revision] The only primary key related information in the VB code is below... Private Sub PrimaryKey_Exit(Cancel As Integer) End Sub What do I need to do with this? If the formula depends on other fields in the form you could set the field to the reults of the calculation by coding the Afterupdate of the last field [quoted text clipped - 6 lines] or primary key cannot contain a null value. How do I get my form to recognize that the field is populated by the formula? Help PLEASE!! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200608/1 |
Thread Tools | |
Display Modes | |
|
|