Primary key Failure

N

Nelson

I am having a situation where users are entering data that the primary key
should prevent. However instead of stopping the entry, the primary key and
the one to many relationship are being "removed", allowing bad data into the
tables.

Nelson White
 
J

Jerry Whittle

Have you seen this in action or taking the word or users? Rule #6 is that
users lie.

Is the entry form simple or is there some fancy code for saving the records?
If so, please post the code.

If they are entering directly into the table, especially if using
subdatasheets or lookup fields, all bets are off.
 
J

John Vinson

I am having a situation where users are entering data that the primary key
should prevent. However instead of stopping the entry, the primary key and
the one to many relationship are being "removed", allowing bad data into the
tables.

Nelson White
Click to expand...

Well... fix the problem.

If you could please post some more information about the structure of
your database and how your users are getting the data in, we might be
able to help. Primary keys and relationships don't "remove"
themselves; my guess is that you have a user who knows enough to be
dangerous (i.e. to open the table in design view and unset the PK) but
not enough to understand why it would be foolish to do so.

You may need to implement Access Security, leaving the users with
update privileges but stripping them of all design change privileges.

John W. Vinson[MVP]
 
N

Nelson

1) I have "witnessed" the instance wher the user enters the data and at once:
The Priamry Key is removed, the one to many relationship is changed to a
"one to many", and the database is corrupted / crashes.
The form is entring ddata into 3 tables on the close of the form, following
is the code that I have.

Thank you
Nelson

[Item#].SetFocus
If [Item#].Text = "" Then
intVBResponse = MsgBox("You did not indicate an item number for this
shipment, do you want to cancel this order? ", vbYesNo, "Missing Information")
If intVBResponse = vbYes Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.Close
Else
Exit Sub
End If

Else
DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.RunSQL "INSERT INTO tblConsignees ( [Store#], ShipTo,
CompanyName, AddressOne, AddressTwo, City, St, Zip, StoreCode ) " & _
"SELECT tblReplacementChairOrders.PONumber,
tblReplacementChairOrders.[Customer Name], tblReplacementChairOrders.Company,
tblReplacementChairOrders.AddressOne, tblReplacementChairOrders.AddressTwo,
tblZipCodes.City, tblZipCodes.State, tblReplacementChairOrders.Zip,
tblReplacementChairOrders.StoreCode " & _
"FROM tblReplacementChairOrders INNER JOIN tblZipCodes ON
tblReplacementChairOrders.Zip = tblZipCodes.ZIPCODE " & _
"WHERE
(((tblReplacementChairOrders.PONumber)=[Forms]![frmReplacementChairOrders]![PONumber]));"

DoCmd.RunSQL "INSERT INTO tblPOHeader ( PurchaseOrderNumber,
StoreNumber, Customer, NovimexDate, StoreCode, OrderDate, RequestedShipDate
)" & _
"SELECT tblReplacementChairOrders.PONumber,
tblReplacementChairOrders.PONumber, 'Novimex' AS Company,
tblReplacementChairOrders.Date, tblReplacementChairOrders.StoreCode,
tblReplacementChairOrders.Date, tblReplacementChairOrders.Date " & _
"FROM tblReplacementChairOrders " & _
"WHERE
(((tblReplacementChairOrders.PONumber)=[Forms]![frmReplacementChairOrders]![PONumber]));"

DoCmd.RunSQL "INSERT INTO tblPurchaseOrders ( PurchaseOrderNumber,
ItemNumber, POSerialNumber, CustomerSerialNumberPurchaseOrder,
QuantityOrdered, Whse )" & _
"SELECT tblReplacementChairOrders.PONumber,
tblReplacementChairOrders.[Item#], [PONumber] & (Left([item#],3)) &
(Right([Item#],4)) AS POSerialNumber, 'Novimex' & ([item#]) AS
CustommerSerialNumber, tblReplacementChairOrders.Qty, 'NVMX' AS Whse " & _
"FROM tblReplacementChairOrders " & _
"WHERE
(((tblReplacementChairOrders.PONumber)=[Forms]![frmReplacementChairOrders]![PONumber]));"
DoCmd.SetWarnings True
DoCmd.Close
End If
 
J

Jerry Whittle

I noticed that the code uses DoCmd.DoMenuItem in at least 3 places. I suggest
changing them to the appropriate DoCmd.RunCommand. It's possible that the
DoCmd.DoMenuItem could get moved around plus Access really doesn't support it
anymore.

What are the primary and foriegn keys? Since you are controlling the links
and not letting Access do it such as in a Form/Subform, you have to be very,
very sure that the code is doing what you want when you want.

I noticed that the SQL references a text field on a form. What happens if
this field is empty or incorrect? Also text fields (a.k.a. controls) have a
Text property and Value property. Text property returns the formatted string.
The Text property may be different than the Value property for a text box
control. The Text property is the current contents of the control. The Value
property is the saved value of the text box control. The Text property is
always current while the control has the focus. In a nutshell it's possible
that the text box might return two different strings depending on
circumstances. To ensure that you are selecting what think, you could save
the record in the form first, set focus on the text box, or use the .Value or
..Text properties in the SQL like so:

[Forms]![frmReplacementChairOrders]![PONumber].Value

And still this might not me the answer. I'm not much of a coder, so if
anyone else wants to jump in here, please do even if it's to tell me that I
don't know what I'm talking about.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Nelson said:
1) I have "witnessed" the instance wher the user enters the data and at once:
The Priamry Key is removed, the one to many relationship is changed to a
"one to many", and the database is corrupted / crashes.
The form is entring ddata into 3 tables on the close of the form, following
is the code that I have.

Thank you
Nelson

[Item#].SetFocus
If [Item#].Text = "" Then
intVBResponse = MsgBox("You did not indicate an item number for this
shipment, do you want to cancel this order? ", vbYesNo, "Missing Information")
If intVBResponse = vbYes Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.Close
Else
Exit Sub
End If

Else
DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.RunSQL "INSERT INTO tblConsignees ( [Store#], ShipTo,
CompanyName, AddressOne, AddressTwo, City, St, Zip, StoreCode ) " & _
"SELECT tblReplacementChairOrders.PONumber,
tblReplacementChairOrders.[Customer Name], tblReplacementChairOrders.Company,
tblReplacementChairOrders.AddressOne, tblReplacementChairOrders.AddressTwo,
tblZipCodes.City, tblZipCodes.State, tblReplacementChairOrders.Zip,
tblReplacementChairOrders.StoreCode " & _
"FROM tblReplacementChairOrders INNER JOIN tblZipCodes ON
tblReplacementChairOrders.Zip = tblZipCodes.ZIPCODE " & _
"WHERE
(((tblReplacementChairOrders.PONumber)=[Forms]![frmReplacementChairOrders]![PONumber]));"

DoCmd.RunSQL "INSERT INTO tblPOHeader ( PurchaseOrderNumber,
StoreNumber, Customer, NovimexDate, StoreCode, OrderDate, RequestedShipDate
)" & _
"SELECT tblReplacementChairOrders.PONumber,
tblReplacementChairOrders.PONumber, 'Novimex' AS Company,
tblReplacementChairOrders.Date, tblReplacementChairOrders.StoreCode,
tblReplacementChairOrders.Date, tblReplacementChairOrders.Date " & _
"FROM tblReplacementChairOrders " & _
"WHERE
(((tblReplacementChairOrders.PONumber)=[Forms]![frmReplacementChairOrders]![PONumber]));"

DoCmd.RunSQL "INSERT INTO tblPurchaseOrders ( PurchaseOrderNumber,
ItemNumber, POSerialNumber, CustomerSerialNumberPurchaseOrder,
QuantityOrdered, Whse )" & _
"SELECT tblReplacementChairOrders.PONumber,
tblReplacementChairOrders.[Item#], [PONumber] & (Left([item#],3)) &
(Right([Item#],4)) AS POSerialNumber, 'Novimex' & ([item#]) AS
CustommerSerialNumber, tblReplacementChairOrders.Qty, 'NVMX' AS Whse " & _
"FROM tblReplacementChairOrders " & _
"WHERE
(((tblReplacementChairOrders.PONumber)=[Forms]![frmReplacementChairOrders]![PONumber]));"
DoCmd.SetWarnings True
DoCmd.Close
End If



Nelson said:
I am having a situation where users are entering data that the primary key
should prevent. However instead of stopping the entry, the primary key and
the one to many relationship are being "removed", allowing bad data into the
tables.

Nelson White
Click to expand...
Click to expand...
 
J

Jerry Whittle

Repost of an earlier message as something strange seemed to happen.

I noticed that the code uses DoCmd.DoMenuItem in at least 3 places. I
suggest changing them to the appropriate DoCmd.RunCommand. It's possible that
the DoCmd.DoMenuItem could get moved around plus Access really doesn't
support it anymore.

What are the primary and foriegn keys? Since you are controlling the links
and not letting Access do it such as in a Form/Subform, you have to be very,
very sure that the code is doing what you want when you want.

I noticed that the SQL references a text field on a form. What happens if
this field is empty or incorrect? Also text fields (a.k.a. controls) have a
Text property and Value property. Text property returns the formatted string.
The Text property may be different than the Value property for a text box
control. The Text property is the current contents of the control. The Value
property is the saved value of the text box control. The Text property is
always current while the control has the focus. In a nutshell it's possible
that the text box might return two different strings depending on
circumstances. To ensure that you are selecting what think, you could save
the record in the form first, set focus on the text box, or use the .Value or
..Text properties in the SQL like so:

[Forms]![frmReplacementChairOrders]![PONumber].Value

And still this might not me the answer. I'm not much of a coder, so if
anyone else wants to jump in here, please do even if it's to tell me that I
don't know what I'm talking about.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Nelson said:
1) I have "witnessed" the instance wher the user enters the data and at once:
The Priamry Key is removed, the one to many relationship is changed to a
"one to many", and the database is corrupted / crashes.
The form is entring ddata into 3 tables on the close of the form, following
is the code that I have.

Thank you
Nelson

[Item#].SetFocus
If [Item#].Text = "" Then
intVBResponse = MsgBox("You did not indicate an item number for this
shipment, do you want to cancel this order? ", vbYesNo, "Missing Information")
If intVBResponse = vbYes Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.Close
Else
Exit Sub
End If

Else
DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.RunSQL "INSERT INTO tblConsignees ( [Store#], ShipTo,
CompanyName, AddressOne, AddressTwo, City, St, Zip, StoreCode ) " & _
"SELECT tblReplacementChairOrders.PONumber,
tblReplacementChairOrders.[Customer Name], tblReplacementChairOrders.Company,
tblReplacementChairOrders.AddressOne, tblReplacementChairOrders.AddressTwo,
tblZipCodes.City, tblZipCodes.State, tblReplacementChairOrders.Zip,
tblReplacementChairOrders.StoreCode " & _
"FROM tblReplacementChairOrders INNER JOIN tblZipCodes ON
tblReplacementChairOrders.Zip = tblZipCodes.ZIPCODE " & _
"WHERE
(((tblReplacementChairOrders.PONumber)=[Forms]![frmReplacementChairOrders]![PONumber]));"

DoCmd.RunSQL "INSERT INTO tblPOHeader ( PurchaseOrderNumber,
StoreNumber, Customer, NovimexDate, StoreCode, OrderDate, RequestedShipDate
)" & _
"SELECT tblReplacementChairOrders.PONumber,
tblReplacementChairOrders.PONumber, 'Novimex' AS Company,
tblReplacementChairOrders.Date, tblReplacementChairOrders.StoreCode,
tblReplacementChairOrders.Date, tblReplacementChairOrders.Date " & _
"FROM tblReplacementChairOrders " & _
"WHERE
(((tblReplacementChairOrders.PONumber)=[Forms]![frmReplacementChairOrders]![PONumber]));"

DoCmd.RunSQL "INSERT INTO tblPurchaseOrders ( PurchaseOrderNumber,
ItemNumber, POSerialNumber, CustomerSerialNumberPurchaseOrder,
QuantityOrdered, Whse )" & _
"SELECT tblReplacementChairOrders.PONumber,
tblReplacementChairOrders.[Item#], [PONumber] & (Left([item#],3)) &
(Right([Item#],4)) AS POSerialNumber, 'Novimex' & ([item#]) AS
CustommerSerialNumber, tblReplacementChairOrders.Qty, 'NVMX' AS Whse " & _
"FROM tblReplacementChairOrders " & _
"WHERE
(((tblReplacementChairOrders.PONumber)=[Forms]![frmReplacementChairOrders]![PONumber]));"
DoCmd.SetWarnings True
DoCmd.Close
End If



Nelson said:
I am having a situation where users are entering data that the primary key
should prevent. However instead of stopping the entry, the primary key and
the one to many relationship are being "removed", allowing bad data into the
tables.

Nelson White
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trying to create a report that will list unmatched records by Ticket Primary Key 0
VBA word change keys 0
Do I have to set the primary key to include primary key fields? 3
Primary and Foreign Keys 10
Defining Relationships/Primary Keys between two tables 4
Access Many to Many relationship 21
Adding data to MS Access from csv file 0
One-to-one relationship 2

Top