Hello Alan,
Thank you for taking time to write in about the status and how you work
around this issue. Your exprience will surely benefit the community!
Best Regards,
Peter Yang
MCSE2000, MCSA, MCDBA
Microsoft Partner Online Support
Get Secure! -
www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| Thread-Topic: Table Relationship Integrity - Null Values
| thread-index: AcaGlymnN55PJksESwui0icJJCZVKg==
| X-WBNR-Posting-Host: 202.7.166.164
| From: =?Utf-8?B?c3RlZWw=?= <
[email protected]>
| References: <
[email protected]>
<
[email protected]>
| Subject: RE: Table Relationship Integrity - Null Values
| Date: Fri, 2 Jun 2006 15:52:02 -0700
| Lines: 133
| Message-ID: <
[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| Newsgroups: microsoft.public.access.adp.sqlserver
| Path: TK2MSFTNGXA01.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.access.adp.sqlserver:24733
| NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
| X-Tomcat-NG: microsoft.public.access.adp.sqlserver
|
| HI Peter,
|
| The scnerio was like this:
| Table A: tblPurchase_order
| name1: POID - uniqiueidentifier - PK
| name2: PODescription - nvarchar(50)
| name3: CostCodeID - uniqueidentifier - FK
|
| In the Form I would have field CostCOdeID set up as a Combo Box
referencing
| the CostCode table and CostCodeID - uniqueidentifier - PK
|
| When I add a new Purchase Order as follows:
| POID - newGuid()
| PO Description : "blah blah"
| - the error occurs if I try to exit the record without selecting a
CostCodeID.
|
| Since posting this issue I was able to resolve by placing some code in
the
| Current event for the Form. The Form's Control Source is set to
| tblPurchaseOrders. I need to keep the GUID's for replication.
|
| ------------------------------------------
| Private Sub Form_Current()
| If IsNull(Me.CostCodeID) Then
| Me.CostCodeID = Null
| End If
| End Sub
| ------------------------------------------
|
| Thanks for looking at this issue for me.
| --
| Regards,
|
| Alan
|
|
| ""privatenews"" wrote:
|
| > Hello Alan,
| >
| >
| > I was not able to reproduce the issue on Access 2003 SP2/WinXP
| >
| > 1. Create tablea/tableb
| >
| >
| > create table tablea (col1 uniqueidentifier primary key, colb
| > uniqueidentifier null, a int, b varchar(20))
| >
| > create table tableb (colb uniqueidentifier primary key default newid())
| >
| > alter table tablea add constraint fk1 foreign key (colb) references
tableb
| > (colb)
| >
| > select * from tablea
| >
| > insert tablea values (newid(), null, 2,'ddd')
| >
| > insert tablea values (newid(), newid(), 1, 'ddd') --I received error
about
| > foreign key
| >
| > sp_help tablea
| >
| > select * from tablea
| >
| >
| > 2. created a ADP from the existing table, I was able to input new item
by
| > removing the guid created auotmatically in colb when editing tablea.
| >
| > Best Regards,
| >
| > Peter Yang
| > MCSE2000, MCSA, MCDBA
| > Microsoft Partner Online Support
| >
| > Get Secure! -
www.microsoft.com/security
| >
| > =====================================================
| > When responding to posts, please "Reply to Group" via
| > your newsreader so that others may learn and benefit
| > from your issue.
| > =====================================================
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| > --------------------
| > | Thread-Topic: Table Relationship Integrity - Null Values
| > | thread-index: AcaF7SXQqRB3+gh7T5G9+Pes3CihSg==
| > | X-WBNR-Posting-Host: 210.50.131.97
| > | From: =?Utf-8?B?c3RlZWw=?= <
[email protected]>
| > | Subject: Table Relationship Integrity - Null Values
| > | Date: Thu, 1 Jun 2006 19:35:01 -0700
| > | Lines: 17
| > | Message-ID: <
[email protected]>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 8bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| > | Newsgroups: microsoft.public.access.adp.sqlserver
| > | Path: TK2MSFTNGXA01.phx.gbl
| > | Xref: TK2MSFTNGXA01.phx.gbl
microsoft.public.access.adp.sqlserver:24723
| > | NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
| > | X-Tomcat-NG: microsoft.public.access.adp.sqlserver
| > |
| > | We have been building databases for replication and therefore using
| > primary
| > | keys of type uniqueidentifier. The GUI is developed in Microsoft
Access
| > 2003,
| > | which doesn’t seem to allow us to insert NULL values into the
foreign
| > keys
| > | (of type uniqueidentifier) that reference these primary keys. This
means
| > that
| > | our referential integrity is compromised as we are forced to enter a
| > valid
| > | value into the foreign key, or else remove the relationship from the
SQL
| > | table.
| > |
| > | How can we assign NULL values to uniqueidentifier fields in Access
2003
| > | projects? We saw one help that suggested an explicit assignment in
the
| > | BEFOREUPDATE event, but this doesn’t appear to be working for
us.
| > |
| > | Thanks.
| > | --
| > | Regards,
| > |
| > | Alan
| > |
| >
| >
|
