Change in DAO Error problem, strange behavior

[data59]

I'm getting an error due to referential integrity, the error is "you can not change record because a related record is required in table.” If I click back a record and then click forward to the current record and click enter the record takes with out producing an error. I'm confused


Here are my tables

tblMain

IDTool (KEY)
Year
ToolNum
DesignStart
StartDate
CompleteDate
WeeksToBuild
TotalHr


tbl_sub_Main


AutoIDTool (KEY)
IDTool (1 to many)
ToolNum
Year
WeekNum
JobHr
JobType

Option Compare Database
Option Explicit



Private Sub Enter_Click()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim wtb As Integer
Dim r As Integer
Dim sr As Integer
Dim x As Integer
Dim y As Integer
Dim w As Integer
Dim th As Integer

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblsubMain", dbOpenDynaset)

wtb = Format(Me.CompleteDate(), "ww") - Format(Me.StartDate(), "ww")

Me.WeeksToBuild = wtb





x = Format(Me.CompleteDate(), "ww")
y = wtb + x
w = Format(Me.StartDate(), "ww")
th = Me.TotalHr / wtb
y = wtb + w


For x = w To y
  If x < y Then
   rst.AddNew
   rst("WeekNum") = x
   rst("JobHr") = th
   rst.Update
  
End If

Next x
End Sub

 

[MajP]

Unless I am not seeing something, I can not see how this works at all. You make a recordset from tblSubMain. This table is linked by IDtool to tblMain. From your notation I assume that IDTool is a primary key in tblMain and a foriegn key in tblSubMain. So you try to enter a record in tblSubMmain, but you do not add a foriegn key relating it to a record in tblMain. I would assume you would always get a referential integrity error.

 

[data59]

Majp, What should I do? I'm new at this, the way I got it to start working was initializing the 2 tables with a record before I created the one to many relation ship. What I want to do is if a record in the main table is deleted, the corresponding records in the sub table will be deleted. The field IDTool in the sub form is a Number data type.

 

[MajP]

Not sure if the following line was a statement or a question:
"What I want to do is if a record in the main table is deleted, the corresponding records in the sub table will be deleted"

1. If this is a question, when you set up your relationship check "Allow Cascade Deletes"
2. If this is a statement of what you did already, then here are some ideas about your code. From looking at your fields it looks like the main tbl deals with the manufacturing of a Tool. The sub table looks like information about specific jobs in the manufacturing process of a tool. Each Tool has many Jobs associated with it.
If this is the case then I would think that, the following is true. I changed the names to make more sense. Please verify:

tblMain (I would call it "tblTool")
IDTool (Primary Key)

tblSubMain (I would call it "tblJob")
autoIDTool (Primary Key) (I would call it "autoIDJob")
IDTool (foriegn key) (I would call it "intFkToolID")
ToolNum (delete this field it is in tblTool)

tblMain (tblTool) is related to tblSubMain (tblJob) by IDTool to IDTool (intFkToolID) with referential integrity set, and cascade deletes.

In your code you want to enter jobs for the current Tool record. So if you are looking at IDTool "1234" then you need to put "1234" in the foriegn key (IDTool, intFkToolID) in the table, "tblJob".

If this is the case add this to your code.

dim intToolID as int (or lng)
intToolID = me.IDTool
   rst.AddNew
   [/b] rst("intFkToolID") = intToolID[/b]
   rst("WeekNum") = x
   rst("JobHr") = th
   rst.Update