AddNew with Two Separate Tables

[BradB]

I keep getting a "Item not Found in Collection" error when I try to AddNew to the database.

It hangs up on:
&quot;If txtGrade.Text <> &quot;&quot; Then
.Fields(&quot;tblStudentLog.Grade&quot = txtGrade.Text
End If&quot;

The only thing I can figure out is Grade is the first item that is NOT in BOTH databases. StudentNo, Date, StaffNo, SchoolNo ALL exist in BOTH databases. Once I try and add Grade, it returns the &quot;Item not Found in Collection&quot; error. I have StudentLogNo as the Primary Key in the tblStudentLog table and I have StudentLogNo as a foreign key in the tblLog table. They have a one to many relationship.

Private Sub cmdSave_Click()
Dim rsSave As DAO.Recordset
Dim Response
Dim I As Integer

Set rsSave = dbWrap.OpenRecordset(&quot;SELECT tblStudentLog.*, tblLog.* &quot; & _
&quot;FROM tblStudentLog INNER JOIN tblLog ON tblStudentLog.StudentLogNo=tblLog.StudentLogNo&quot

With rsSave
.AddNew
If txtStudentNo.Text <> &quot;&quot; Then
.Fields(&quot;tblStudentLog.StudentNo&quot = txtStudentNo.Text
End If

If txtGrade.Text <> &quot;&quot; Then
.Fields(&quot;tblStudentLog.Grade&quot = txtGrade.Text
End If

If dtpDateLog.Value <> &quot;&quot; Then
.Fields(&quot;tblLog.Date&quot = dtpDateLog.Value
End If
If txtStaffNo.Text <> &quot;&quot; Then
.Fields(&quot;tblStudentLog.StaffNo&quot = Suser
End If
If cboSchool.Text <> &quot;&quot; Then
.Fields(&quot;tblStudentLog.SchoolNo&quot = cboSchool.ItemData(cboSchool.ListIndex)
End If' If txtComment.Text <> &quot;&quot; Then
.Fields(&quot;tblStudentLog.Comment&quot = txtComment.Text
End If
If txtGAFScore.Text <> &quot;&quot; Then
.Fields(&quot;tblStudentLog.GAF&quot = txtGAFScore.Text
End If
If txtAmountOfTime.Text <> &quot;&quot; Then
.Fields(&quot;tblStudentLog.AmtOfTime&quot = txtAmountOfTime.Text
End If
.Update
End With

MsgBox &quot;Save successful!&quot;, vbInformation

Unload frmSearch
Unload Me
End Sub

 

[rdroske]

Add a watch on rsSave in the debugger. Then view the Fields collection items.

 

[BradB]

I have a confession...I've never used the Watch function. I added rsSave as the expression. How do I view the fields collection items?

 

[rdroske]

Sounds like your Watch Window is not visible. If not under View select Watch Window.

Then if you set a watch on rssave you'll see it there with a + next to it. Expand it, scroll donw and you will see Fields with a +, expand it and you need to look at the Items and specificallt the Name field of each Item.

I think you'll see that that field just has no table name qualifier.