Form not recognizing record added via SQL statement "Cyberwolf0000 via AccessMonster.com" <u42640[ at ]uwe> 11/18/2008 2:35:37 PM I have a table that has data entered in 2 ways. One way is when it is entered on a subform of another form. The other is a stand-alone form. The underlying table structure is as follows: ReasonCodes - ReasonCodeID(PK) RPPCauseInfo - RPPCauseInfoID(PK) - ReasonCodeID(FK) I have a one-to-many relationship between ReasonCodes(One) and RPPCauseInfo (Many). When data is entered via the subform everything works fine. But, when I have the stand-alone form open, I have Code that adds a record to the ReasonCodes table using a SQL INSERT INTO statement. The record gets added fine, but when I go to move away from the record I get an error "The Microsoft Jet database engine cannot find a record in the table 'ReasonCodes' with key matching field(s) 'ReasonCodeID'" I have tried doing a requery on the form but that did not help. I believe it is because of the One-To-Many relationship. That is why I add a record to the ReasonCodes table to enforce the integrity. Is there a way to requery the ReasonCodes table so that the record is recognized? Can anyone tell me why this happens? -- James B Gaylord For the Wolf comes the strength of the Pack, For the Pack comes the strength of the Wolf, -R. Kipling Office 2003 on Win XP SP2 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1

Re: Form not recognizing record added via SQL statement "tkelley via AccessMonster.com" <u47368[ at ]uwe> 11/18/2008 6:14:11 PM Can you post your SQL statement? Cyberwolf0000 wrote: [Quoted Text] >I have a table that has data entered in 2 ways. One way is when it is >entered on a subform of another form. The other is a stand-alone form. The >underlying table structure is as follows: > >ReasonCodes - ReasonCodeID(PK) >RPPCauseInfo - RPPCauseInfoID(PK) - ReasonCodeID(FK) > >I have a one-to-many relationship between ReasonCodes(One) and RPPCauseInfo >(Many). > >When data is entered via the subform everything works fine. But, when I have >the stand-alone form open, I have Code that adds a record to the ReasonCodes >table using a SQL INSERT INTO statement. The record gets added fine, but >when I go to move away from the record I get an error "The Microsoft Jet >database engine cannot find a record in the table 'ReasonCodes' with key >matching field(s) 'ReasonCodeID'" I have tried doing a requery on the form >but that did not help. I believe it is because of the One-To-Many >relationship. That is why I add a record to the ReasonCodes table to enforce >the integrity. Is there a way to requery the ReasonCodes table so that the >record is recognized? > >Can anyone tell me why this happens? > -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1

Re: Form not recognizing record added via SQL statement "Cyberwolf0000 via AccessMonster.com" <u42640[ at ]uwe> 11/18/2008 6:58:23 PM strSQL = "SELECT ReasonCodeID " & _ "FROM ReasonCodes " & _ "WHERE ReprintID=" & rec2!ReprintID & " AND Reason='Rejected Preprint' " & _ "AND ReasonFreq=1 AND ReasonImps=" & rec!NetImps & " AND " & _ "ReasonComments='This roll was rejected by the Roll to Roll Department';" Set rec3 = db.OpenRecordset(strSQL) Me.ReasonCodeID = rec3!ReasonCodeID tkelley wrote: [Quoted Text] >Can you post your SQL statement? > >>I have a table that has data entered in 2 ways. One way is when it is >>entered on a subform of another form. The other is a stand-alone form. The >[quoted text clipped - 18 lines] >> >>Can anyone tell me why this happens? -- James B Gaylord For the Wolf comes the strength of the Pack, For the Pack comes the strength of the Wolf, -R. Kipling Office 2003 on Win XP SP2 Message posted via http://www.accessmonster.com