Deleting a related records 1

[Herriot]

Hi folks can anyone help please...

My database is a simple stock control one, you'ld think. The stock in question is work garments. I have two tables; tblsizes, tblstock. The tblsizes table has the following fields Size_id (autonum key field); Stock_ID (number); Size (text, cos it could include large, medium etc as well as numerical sizes); Qty (number). The tblStock has the fields Stock_ID (autonum key field) Stock_description. Both tables are linked by the STock_ID field in both tables.

I have two forms. One for data entry and one to view the data and show grand totals. The other is to allow data entry. The data entry form (frmStock) has a subform (frmSizes Subform). The user can enter the stock and the sizes and quantity of each size. The fields in the subform are displayed in datasheet view. It will be easier for the user to delete rows of data as and when required by simply hilighting the row in question and pressing the delete key. I have no way, yet, of deleting the stock should the user need to because of related records.

I hope there is sufficient "meat" in the above for you to be able to answer theses problems...

1- How can I delete a Stock Item (from the table tblStock) along with related records held in the tblSizes?

2 - How can I prevent the user putting duplicate values in the Size field?

With regard to 1, I have tried a delete query without success. Though that may be because I created it incorrectly.

With regard to 2, I have managed to prevent the user from inputting a duplicate Stock item. So, for instance, they couldn't enter Gloves twice. But the same routine doesn't seem to work with the Size field in the "frmSizes subform"

TIA

Regards
Herriot

 

[tunsarod]

What you need to do is to set up the Referential Integrity for these two forms.

Make sure both forms are closed.

Click the Tools menu and select Relationships.

Find the two tables in the list and Add them to the Relationships form.

Locate the StockID field in the tblStock and drag it over and drop it onto the StockID field in tblSizes.

Click (tick) Set referential Integrity and select (tick) Cascade Delete Related Records.

Now open a blank Query for the tblStock table and insert the following into the SQL code window of the query:

DELETE tblStock.StockID
FROM tblStock
WHERE (((tblStock.StockID)=[Forms]![frmStock]![StockID]));

Now when you delete one record in tblStock all related records in tblSizes will go too.

As for the duplicate prevention, I think that you need to set BOTH the Size field and the StockID field tblSize to Unique.

That way the wto will act together to allow:

Gloves Red Size 2
Gloves Red Size 3
Gloves Blue Size 2
Gloves Blue Size 3

but not:

Gloves Red Size 2
Gloves Red Size 2
Gloves Blue Size 3
Gloves Blue Size 3

Hope this helps.
Rod

 

[Herriot]

Thanks Tunsarod.

With regard to Question 1 above. I completely forgot about the referential integrity checkmarks in the relationships. I remembered later about it. However is there anyway to change the msgbox that warns the user to something more simple the user could understand?

With regard to duplication. Unfortunately your solution didn't work :-(. It looks as though it would and in fact I was sort of half way there as I had one of the fields set to be unique.

Though it sounds a good idea I think some code, that would be checked on leaving the Size field, would be required where the end result is that a msgbox pops up to tell the user that Size entries for each garment must be unique and sets the focus back in the size box.

Thanks again. Any further help would be appreciated.

Regards

Herriot

 

[tunsarod]

I have just created two tables tblStock and tblSizes a form frmStock and a delete query which I called "Delete tblStock and related tblSizes". I set referential integrity between the tables and checked the box to Cascade Delete Related Records.

The tables: tblStock has fields: StockID - autonumber
StockName - text
ItemType - text

StockID is indexed (no duplicates).

tblSizes has fields: SizeID - autonumber
StockID - number
Size - number

StockID and Size are indexed (no duplicates) and are JOINT Primary Key. Note I did not use SizeID as part of the Primary Key.

The form is based on the tblStock with one record per page. It also contains a subform based on tblSizes with StockID on the main form and StockID on the subform creating the link between them.

I found that if I enter a record in the main form and then tab to the subform to enter several records based on the same StockID I am able to create records freely but if I try to create two recordss with the same StockID and Size then Access stops me from creating a duplicate and puts up a warning message. I think thats what you are looking for isn't it?


Here is the SQL for the Query:

DELETE tblStock.StockID
FROM tblStock
WHERE (((tblStock.StockID)=[Enter a current Stock ID number]));

When the query is run a parameter request box appears with the phrase "Enter a current Stock ID number." I entered 2 and pressed return and the Stock record with ID of 2 and all related Size records vanished without any messages.

Regarding the Duplicates Error message: I have created a useful Error messaage to replace the Access message which I agree is hardly user friendly.

Place it in the OnError Event procedure for the tblSizes Subform:-

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3022 Then
Response = acDataErrContinue

MsgBox "The Size value you just entered would have created a duplicate record." _
& Chr(13) & Chr(13) _
& "Please re-enter a unique StockID/Size combination.", vbExclamation, "Error on Duplicate Entry"
End If
Me.Form.Undo
Me.Size = Null
Me.Size.SetFocus

End Sub


It's based on a well hidden example in the Help system found by: Opening a form in design view, placing the cursor in the On_Error event property box and pressing F1 then selecting Event Procedures from the top line and opening the example - which I have reproduced here in case you have problems locating this Help topic.

ERROR EVENT - EVENT PROCEDURES

The following example shows how you can replace a default error message with a custom error message. When Microsoft Access returns an error message indicating it has found a duplicate key (error code 3022), this event procedure displays a message that gives more application-specific information to users.
To try the example, add the following event procedure to a form that is based on a table with a unique employee ID number as the key for each record.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Const conDuplicateKey = 3022
Dim strMsg As String

If DataErr = conDuplicateKey Then
Response = acDataErrContinue
strMsg = "Each employee record must have a unique " _
& "employee ID number. Please recheck your data."
MsgBox strMsg
End If
End Sub

I hope this helps.

If you have any difficulty with all this post your email and I'll send you my example tables, forms and Query to examine.

Regards
Rod

 

[Herriot]

tunsarod.

Thanks for taking the time to do that. I appreciate it. I can't however replicate what you are doing so can you send me the the tables etc that you mentioned?

My email is peter.leswell@blueyonder.co.uk

Many thanks and my apologies for seeming thick - I'm not really, honest!

 

[tunsarod]

OK Check your email - example.mdb sent.
Rod

 

[Herriot]

Rod

What can I say? Thank you very much.

As you can guess I had a look at your DB and your posting did make a lot of sense. I was kind of going in the same direction but I had my primary keys wrongly set up as you had pointed out. And as for the code, well I was nowhere near . Before I received your email I thought I had a reasonable knowledge (not a professional one by any means) of access with a smattering of code. But now I think that I have a smattering of Access and even less with code!

Apart from the primary keys being set incorrectly I also had a relationship between the two tables, linked by the Stock_ID, I note that you don't have any relationships set up and it still works. I always thought, wrongly it appears, that relationships need to be set.

Thanks again for all your time and the trouble you went to. I will learn from this experience.

Regards
Herriot